Monthly Archives: January 2014

As I already explained in my last blog post the way performance tuning is done in NAV2013R2 has changed from previous versions.

Rather than issueing a SQL Statement for each flowfield in each row on a listpage NAV now uses OUTER APPLY.

In a large upgrade project I am doing with a 100+GB database the Posted Invoices did not perform both from purchase and sales.

It is more work to analyse a query. Each flowfield adds an OUTER APPLY to the query and sometimes this can lead to queries being a page long. It helps to paste the query in SQL server management studio and search for OUTER APPLY and add a line break for each.

In this scenario the Amount and Amount Incl. VAT flowfields were added to the list hence two OUTER APPLY statements were added.

In this case the SIFT on the primairy key was disabled which was best practive in older versions of NAV.

However by activating the MaintainSIFTIndex property the performance of the page was back to being ultra fast like it should be in NAV2013R2.

 

 

It’s been a while since I had time to blog, and still I do not really have the luxury of time but there are at least two things important enough to share.

At the moment I am involved in a large upgrade project from NAV 5.0 to 2013R2 with a Multi-country NAV database of 100+ GB.

This entry will be about the issues NAV2013R2 has with using the SQL Integer option for NAV Code fields and the next entry will be about SIFT on primairy key fields.

Both will lead to performance issues if treated with the knowledge of previous versions.

SQL Server performance tuning for Dynamics NAV changes with each release of the product. This is why it is so hard to make easy rules to remember for developers. Most of the times the changes are good and the product performs faster if used correctly, as is the case with this release.

In Dynamics NAV it is possible to change the SQL DataType for a Code field from the default value VarChar to Integer.

This was done a lot by customers moving from the Classic Database to SQL Server for sorting purposes and it was never an issue.

Until this release.

For those of you who attended one of my sessions at Directions last year you might remember that I told that rather than reading flowfields row-by-row in a listpage NAV is now performing an OUTER APPLY returning a complete dataset using SQL Server power.

The issue in this scenario is when you change for example the properties of the No. field in the Customer table to Integer and you do not change it on all referenced tables SQL cannot handle the conversion.

This is a SQL Server issue more than a NAV issue and it will not be solved by Microsoft.

Here is a link to the issue on Mibuso.

http://mibuso.com/forum/viewtopic.php?f=32&t=58700&start=0&hilit=NAV2013+and+performance

The solutions are to either change all the fields to Integer which is close to impossible since you end up in tables like Default Dimensions or to just reverse the change back to the default value.

I did the latter and implemented a SQL script to add filler characters to solve the sorting issues.

Contact me if you want more information about the solution.