Beware the SQL Index property on NAV 5.0 SP1

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 - https://blogs.msdn.com/microsoft_dynamics_nav_sustained_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.

For more information on identifying these problematic queries, please see the SE Blog post mentioned at the top of this article.

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.

Comments

• Anonymous
  April 10, 2009
  PingBack from http://microsoft-sharepoint.simplynetdev.com/beware-the-sql-index-property-on-nav-50-sp1/

• Anonymous
  April 11, 2009
  Hi Laura, well, I know the SE BLOG about that issue ... My opinion is: with NAV 5.0 it was tried to force some "SQL Theory" which does not really suit the "NAV Reality" ... ;c) I also commented that to the SE BLOG, but I would appreciate to get your opinion: I use the "SQL Index" property on large tables (many records) which have many indexes or are heavily used. Here I simply COPY the "Key" content to the "SQL Index" - this re-creates the Index non-unique and without the usually added PK fields, hence, the index gets somewhat smaller (sometime remarkably!), improving its performance but without risking any conflict with the sorting. The "missing" PK fields are not an issue, because the Clustered Index is usually based on the PK, and as with NAV there's nearly always a "Bookmark Lookup" (or "Keylookup") there is no problem. So far I have quite good experiences with that! What do you think about that? Best regards, Jörg jstryk@stryk.info

• Anonymous
  April 12, 2009
  Hi Jorg, Well, I really should have emphasized the fact that the problem arises when the fields in the SQL Index vary too much from the Key specification. In the cases that I have worked with, the most common problems are that the fields in the SQL Index are in a different order OR not all Key fields are included, sometimes resuting in a confilict between the Order By and the Where clauses. In the scenario you are describing, I don't expect you would see this behavior. Laura

• Anonymous
  September 07, 2010
  I know this is a quite old topic but we ran into the problems with the broken downwards compatibility in the application due to this decision. If you want to get a straight sort order, why did you choose such a way to find the right "key"? Furthermore, "veteran" application developers rely on the meaning and using of setcurrentkey. The changed behaviour can lead to a wrong sort order anyway and to heavy perf problems in the native client. In general, you mix up the meaning of key and sort order. Why don't you simply attach the P-Key at the end of the order by clause? It's up to the developer to create a suitable SQL-Index that match the sort order which forces the SQL optimizer to select a similar index when using Dyn-Cursors. When this does not match, it's still up to the developer to create the right sort orders, but with this change the developer is forced to change all setcurrentkeys to a full qualified directive, otherwise one will receive an unexpected behaviour - like we did. Sad.