This is going to be an article about "Index Hinting".
I'm ashamed that I did not know about this feature until for a few weeks ago. Why? Because it has been in the bible for a long time (fyi, "the bible" is "The Application Designer's Guid" aka the w1w1adg.pdf on the \doc folder on your CD). How long? I've seen it in the ADG of 3.10 … so it's been there for a long long time. Just see Chapter 30.6. Hynek Muhlbacher brought this to our attention when Mark (Brummel) and I were working on a tuning.
Anyway … I just wanted to let you know that it exists … that it works … and that you can have very positive AND very negative results with it. I'll try to explain.
There are two ways for using Index Hinting with Dynamics NAV:
@name = N'CustLedgerEntry_Guide1',
@stmt = N'SELECT * FROM "[NavisionDB]"."dbo"."[CompanyName]$Cust_ Ledger Entry" WHERE (("Customer No_"=@P1)) AND "Customer No_"=@P2 AND "Posting Date"=@P3 AND "Currency Code"=@P4 AND "Entry No_">@P7 ORDER BY "Customer No_","Posting Date","Currency Code","Entry No_" OPTION (FAST 10)',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@P1 varchar(20),@P2 varchar(20),@P3 datetime,@P4 varchar(10),@P5 varchar(20)',
@hints = N'OPTION (RECOMPILE)'
Good luck - I'm not using this!
CREATE TABLE [$ndo$dbconfig] (config VARCHAR(512) NOT NULL)
GRANT SELECT ON [$ndo$dbconfig] TO public
INSERT INTO [$ndo$dbconfig] VALUES
('IndexHint=Yes;Company="CRONUS International Ltd.";Table="Item
Ledger Entry";Key="Item No.","Variant Code";Search Method="-
name>;Key=<keyfield1,keyfield2,...>; Search Method=<search method
For the key fields: you have to sum up all key fields that you want the use. I.e. the NAV key you want to use for sorting your data (SETCURRENTKEY).
For further information I would like to forward you to the ADG.
Now you might wonder why and when you need this, and that is very hard to say. When you're doing a performance analysis, you check the SQL statements in the profiler and it's execution plans. Now, sometimes, you notice that SQL Server just doesn't take the correct index, and no matter what index you create, and how many statistics updates you do, SQL just won't take that index. Plus, the results in the profiler are absolutely ridiculous. Well, then you can try index hinting. And when you see it gets the right index, and that the results are better, you succeeded.
There is a warning I have to add. By using many index hints, you're going to "simulate" native behaviour. Why? You're going to use the same key as you specify in your "SETCURRENTKEY" statement. Every time you're in that company, for that table, and using that sorting, SQL will take that key, no matter what your filters are… .
So beware of this.
Application's Designer Guide / Hynek Muhlbacher / Mark Brummel
Hey Eric, nicely written, I am sure its not just you two that didn't know about Index Hinting, so i think this blog will help a lot of people.
Thanks for sharing this.
By the way, if you have problems with SQL using the wrong key in a specific case (e.g., say a form with lots of records), have you tried creating a NEW key specifically for that situation and sorting the form on that, then apply the Hint just to that new key, so that the hinting will not affect other areas of code?