Beware the SQL Index property on NAV 5.0 SP1

Beware the SQL Index property on NAV 5.0 SP1 | Microsoft Share Point

Beware the SQL Index property on NAV 5.0 SP1 | Microsoft Share Point — Fri, 10 Apr 2009 17:51:16 GMT

PingBack from http://microsoft-sharepoint.simplynetdev.com/beware-the-sql-index-property-on-nav-50-sp1/

re: Beware the SQL Index property on NAV 5.0 SP1

stryk — Sat, 11 Apr 2009 20:35:23 GMT

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

re: Beware the SQL Index property on NAV 5.0 SP1

lalake — Mon, 13 Apr 2009 14:50:31 GMT

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