Nav developer: Beware the SQL Index property on NAV 5.0 SP1

[Blog bot]

The discussion of the changes starting with Microsoft Dynamics NAV version 5 regarding the use of Dynamic cursors has already been addressed in the article titled "Cursor Types" on the SE Blog - http://blogs.msdn.com/microsoft_dynamics_n...ed_engineering/


However, we are seeing more and more cases where the use of the SQL Index property in NAV 5.0 SP1 is causing performance issues, so the purpose of this blog is to explain in more detail from an application perspective how this property can affect performance.


From version 5.0 we are using Dynamic cursors, which means that SQL server will tend to choose an index that fits the ORDER BY clause more than the WHERE clause. This is not a problem as long as there is no conflict between the ORDER BY and the WHERE clause. This is where the SQL Index property comes in.

SQL server should be able to choose the optimal index IF we create an index which matches the ORDER BY or the filter/range perfectly. NAV constructs the ORDER BY based on the NAV Key specification not the SQL Index specification. If a SQL Index value is specified on the NAV key and the fields do not exactly match the fields and the order in which they were specified in the Key, then there could be a conflict between the ORDER BY and the WHERE clauses.


How does the issue present itself in the application? These are just a couple of examples we have seen of the impact the SQL Index specification can have on the application…


Poor performance on large tables like Ledger Entry tables, if a user selects Sort on the form and changes from the primary key to a secondary key which has a SQL Index specification.


Using the RunFormView and RunFormLink property of a form…If the RunFormView is set to a non-primary key, SQL might not choose the first key matching the RunFormView if that key has a SQL Index specification. Depending on the keys defined on the table, there may not be another adequate key available, so a suboptimal index may be used. The ORDER BY may no longer match the WHERE clause (RunFormLink), resulting in very poor performance loading the form.


Performance problems when SETCURRENTKEY/SETRANGE is used in code and the key specified has a SQL Index specification that does not match the key or the filters entered (WHERE clause).


************


We are seeing issues mainly with customers who have upgraded to 5.0 SP1 from 5.0, because there were values specified for SQL Index in many tables in the base product in 5.0. Some other customers on pre-5.0 versions might also have issues if someone has done performance tuning on their database. In earlier version of NAV, SQL Index was used to improve performance in certain scenarios.




The recommendation is that the use of the SQL Index specification should be limited to very specific situations where thorough testing shows performance improvement. In the vast majority of scenarios, SQL will make the correct choice of Index, so the SQL Index specification is not needed and can actually cause poor performance.




[font=" 'Calibri','sans-serif'; FONT-SIZE: 11pt; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-ascii-theme-font: minor-latin; mso-hansi-theme-font: minor-latin"]For more information on identifying these problematic queries, please see the SE Blog post mentioned at the top of this article.[/font]


Laura K. Lake


Microsoft Dynamics NA



Microsoft Customer Service and Support (CSS) North America


These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.



Читать дальше