Mark Brummel - Author of Microsoft Dynamics NAV 2009 Application Design

Mark Brummel - Author of Microsoft Dynamics NAV 2009 Application Design

Recent Posts

Tags

News

  • Published : Microsoft Dynamics NAV 2009 Application Design

Community

Email Notifications

Other Blogs

Archives

NAV 2013 | No more Indexhinting

Well, that sounds cool right? Like, never index hinting in NAV anymore after all the discussions in the past.

http://mibuso.com/forum/viewtopic.php?f=34&t=13154&hilit=hinting

http://www.mibuso.com/dlinfo.asp?FileID=896

http://dynamicsuser.net/blogs/mark_brummel/archive/2007/08/21/is-navision-to-stupid-or-is-sql-to-smart.aspx

With NAV 2013 it is no longer possible to setup index hints using the $ndo$dbconfig table.

Does it mean it's no longer nescesairy.

Unfortunately not.

I found out about this during an upgrade of one of my customers from NAV2009 RTC to NAV2013.

We experienced extremely slow performance in an area where NAV2009 was much faster than NAV2013. And here I promised better performance... DAMN. Stick out tongue

Off course with our perfect documentation (not) it took some time to figure out it might be that index hinting was used in NAV2009 to force SQL using an index it would not use normally.

The process is selecting shipments to be planned in a trip for a taxicompany. We use a common method to select a shipment using the user id, just like you would when applying customer and vendor ledger entries. When selected it's an extremely selective field (only one or two record of a million have this value) but SQL does not pick it up because... because... shoot me, I don't know why not. DAMN 2. Stick out tongue

So with index hinting no longer possible (confirmed by Microsoft, done on purpose, by redesign) I needed to be creative.

The solution is to save a pointer to a shipment in a second table called "Selected Shipment". All shipment that need to be planned in a trip are temporarily stored there and picked up and deleted afterwards. Depending on the architecture of your transaction this could even be done in a temporary table and/or a single instance codeunit.

So recap: no more indexhinting, be carefull with upgrades and creative with solutions.

And PS: The general performance of NAV 2013 is AWESOME!!!

Posted: 2013-4-5 20:05 by Marq | with 2 comment(s)
Filed under: , ,
Bookmark and Share

Comments

Mark Brummel - Microsoft Dynamics NAV Blog said:

Well, that sounds cool right? Like, never index hinting in NAV anymore after all the discussions in the

# April 5, 2013 9:01 PM

Mark Brummel - Microsoft Dynamics NAV Blog said:

Well, that sounds cool right? Like, never index hinting in NAV anymore after all the discussions in the

# April 5, 2013 9:15 PM
Leave a Comment

(required) 

(required) 

(optional)

(required)