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
IMHO indexhinting will not do the trick for one simple reason: SetCurrentKey is only significant for sorting and not for selection. The index to use therefor does not depend on the active key but on the fields on which is filtered.
The indexhint option actually gets "in the way" when there are 2 routes to the same screen, i.e. Customer Ledger Entry screen can be reached through the Navigate functionality (where the filter will be on "Document No.") and from the Customer form (where the filter will be on "Customer No."). The resulting screen has the SetCurrentKey set to "Customer No.", "Posting Date" so for the "Document No." route with index hinting to the Customer No. index will still result in a freeze.....
And still, users are allowed and will use the function to change the Sorting Order in this screen (effectively changing SetCurrentKey) with similar performance impact.