vanvugt: What happened to SQLIndex?

[Blog bot]

Источник: https://dynamicsuser.net/nav/b/vanvu...ed-to-sqlindex
==============

Chew, it has been quite some time we met here. Feels like learning to blog again. If you have been waiting for me all that time I make a deep bow and apologize for that. It has been a very busy 8 month period starting last December bringing our NAV 2009 R2 classic environment eventually to NAV 2016 last July. And after that ... a well deserved vacation in our little house down south in France. I guess it never felt more like that: time to take a leave and enjoy live in a total different way. And we sure did.

And now back on the job again. But with one major change: we're on 2016 now. Finally. FINALLY! Over three years ago I joined Van Dijk Educatie to get their Dynamics NAV installation to the latest version, NAV 2013 R2 at that time, but due to higher operational priorities, and to my dismay, it was postponed a couple of times. But we made it. Just before our high season started end of July and to the satisfaction of all our colleagues. Go Live on Monday July 4th went smoothly. We had been on it for all those 8 months, getting from NAV 2009 R2 classic to NAV 2009 R2 RTC in February and then straight to NAV 2016 in July. I must say it was quite an effort from dev and test with great involvement from all. And yes, we a nice number of lessons learned to share with you. Hope I will find time enough to rally get that done, here, so stay tuned.

Well, let's take the bull by the horns, as we say over here; let's start immediately. With an issue that had been bugging our operation ever since I went out to enjoy may vacation. Pure coincidence of course. [8-|]

I was informed that our colleagues were frequently experiencing deadlocks on the Warehouse Request table (5765), which sounding unfamiliar to me. Looking in the history of our 2009 installation TAB5765 was hardly ever an issue, but I noticed we had some customization on this object from way back in 2012, fixing ... deadlock issues!

Apparently TAB5765 standard primary key (PK) was sub optimal:

Type,Location Code,Source Type,Source Subtype,Source No.

The selectivity of the first PK field Type, with only 2 values, was clearly to low, where as the selectivity of the last PK field, Source No., was very high. For this reason, by means of the SQLIndex property of the PK, the SQL index was changed to:

Source No.,Location Code,Source Type,Source Subtype,Type

It indeed did solve the performance issue at that time.

But why were we experiencing seemingly the same again?

The PK wasn't changed by MS and we had kept the SQLIndex property customization on the PK. But clearly, monitoring the deadlocks it showed that it was using the PK.

However, looking at the SQL implementation of the PK, I could not believe my eyes:



Do you see what I mean? It's the standard PK structure:

Type,Location Code,Source Type,Source Subtype,Source No.

Not the one defined by the SQLIndex property:

Source No.,Location Code,Source Type,Source Subtype,Type

This is somewhat scary. Is SQLIndex not working anymore?

Technically it could have been a candidate for the Clean Team as there is no need for this property anymore since we're on SQL only since NAV 2013. But ... migrating old code that uses SQLIndex to 2013 or beyond should however implement the key as defined by SQLIndex.

SQLIndex on PK

Some more detailed investigation learned me the following:

1. SQLIndex is not working on PK, even though C/SIDE allows to populated SQLIndex and even prompts you to use all fields that are in the PK definition, when define SQLIndex with lesser or other fields
2. SQLIndex does still work for SK

I have tested this for NAV 2013 R2 and NAV 2016, but probably also applies to NAV 2013 and 2016.

Why C/SIDE has changed this way I have no clue, but it scares me somewhat.

Mr. MS could you tell us why? If not please revert to the old behavior.




Источник: https://dynamicsuser.net/nav/b/vanvu...ed-to-sqlindex