On Microsoft SQL Server, you can use index hinting to force the
server to use a particular index when executing queries for
FINDFIRST, FINDLAST, FINDSET, FIND('-'), FIND('+'),
FIND('=')
, and GET
statements.
Index hinting can prevent situations where SQL Server's Query Optimizer chooses an index access method that requires many page reads and generates long-running queries with response times that vary from seconds to several minutes. Directing SQL Server to use a specific index can give instant 'correct' query executions with response times of milliseconds. Index hints should only be used to target specific problems.
There are two ways to use index hinting in your application:
-
You can leave index hinting turned off and enable it in specific places.
-
You can turn on index hinting and disable it in specific places.
In Microsoft Dynamics NAV, index hinting is turned off by default.
The Benefits of Using Index Hinting
Index hinting has been shown to optimize performance in the following scenarios:
-
Index hints prevent SQL Server from using an old query plan, such as a clustered index scan.
-
Index hints prevent SQL Server from scanning smaller tables and escalating locks to table locks.
Prerequisite
To benefit from index hinting, you must use
SETCURRENTKEY
in your application code.
SETCURRENTKEY
must correspond to the filter that you
want to place on the table.
Copy Code | |
---|---|
GLEntry.SETCURRENTKEY(GLEntry.AccountNo); // Should be added to the code if it is not already present. GLEntry.SETRANGE(GLEntry.AccountNo,'1000','9999'); GLEntry.FINDSET(); |
Example:
In the following C/AL code, index hinting is turned on but
SETCURRENTKEY
is not used.
Copy Code | |
---|---|
GLEntry.SETRANGE("G/L Account No.",'2910'); GLEntry.FINDSET; |
This generates the following SQL query.
Copy Code | |
---|---|
SELECT TOP 500 * FROM "W1403"."dbo"."CRONUS International Ltd_$G_L Entry" WITH (READUNCOMMITTED, INDEX("CRONUS International Ltd_$G_L Entry$0")) WHERE (("G_L Account No_"=@P1)) ORDER BY "Entry No_" ','2910' |
Without using SETCURRENTKEY
, Microsoft Dynamics NAV
will hint the SQL index that corresponds to the primary key in the
G/L Account table. This is not the best key to use for this
query. Conversely, in the following C/AL code, hinting is turned on
and SETCURRENTKEY
is used.
Copy Code | |
---|---|
GLEntry.SETCURRENTKEY("G/L Account No."); GLEntry.SETRANGE("G/L Account No.",'2910'); GLEntry.FINDSET; |
This generates the following SQL query.
Copy Code | |
---|---|
SELECT TOP 500 * FROM "W1403"."dbo"."CRONUS International Ltd_$G_L Entry" WITH (READUNCOMMITTED, INDEX("$1")) WHERE (("G_L Account No_"=@P1)) ORDER BY "G_L Account No_","Posting Date","Entry No_" ','2910' |
Because the C/AL code specifies which key to use, Microsoft Dynamics NAV hints the corresponding index from the code, ensuring that the correct index is always used.
If index hinting is turned off, SQL Server will define the filter automatically.
Disabling Index Hinting in Specific Places
If you decide to turn on index hinting, you can still disable it for a specific company, table, or index. The following examples illustrate how to disable index hinting by executing a statement in query analyzer.
Example 1
Copy Code | |
---|---|
INSERT INTO [$ndo$dbconfig] VALUES ('IndexHint=No') |
Index hinting is turned off across the entire application.
Example 2
Copy Code | |
---|---|
INSERT INTO [$ndo$dbconfig] VALUES ('IndexHint=No;Company="CRONUS International Ltd."') |
Index hinting is turned off only for the CRONUS International Ltd. company.
Example 3
Copy Code | |
---|---|
INSERT INTO [$ndo$dbconfig] VALUES ('IndexHint=No;Company="CRONUS International Ltd.";Table="Sales Header') |
Index hinting is turned off only for the CRONUS International Ltd_$Sales Header table.
Example 4
Copy Code | |
---|---|
INSERT INTO [$ndo$dbconfig] VALUES ('IndexHint=No;Company="CRONUS International Ltd.";Table="Sales Header";Index="1"') |
Index hinting is turned off only for the $1 index in the CRONUS International Ltd_$Sales Header table.
Enabling Index Hinting in Specific Places
You can disable index hinting for the entire system and then enable it where appropriate for your application. The index hint syntax is:
Copy Code | |
---|---|
IndexHint=<Yes,No>;Company=<company name>;Table=<table name>;Key=<keyfield1,keyfield2,...>; Search Method=<search method list>;Index=<index id> |
Each parameter keyword can be localized in the Driver
configuration parameters section of the .stx
file.
The guidelines for interpreting the index hint are:
-
If a given keyword value cannot be matched, the entry is ignored.
-
The values for the company, table, key fields, and search method must be surrounded by double-quotes to delimit names that contain spaces, commas, and so on.
-
The table name must correspond to the name supplied in Object Designer (not the caption name).
-
Key must contain all the key fields that match the required key in the Keys window in Table Designer.
-
The
Search
method contains a list of search methods used inFIND
statements.Function Symbol RECORD.GET()
!
RECORD.FIND()
=
RECORD.FIND('-')
-
RECORD.FIND('+')
+
RECORD.FIND('>')/RECORD.NEXT()
>
RECORD.FIND('<')/RECORD.PREV()
<
RECORD.FINDFIRST()
[
RECORD.FINDLAST()
]
RECORD.FINDSET()
$
-
The index ID corresponds to a SQL Server index for the table: 0 represents the primary key; all other IDs follow the number included in the index name for all the secondary keys. Use the SQL Server command
sp_helpindex
to get information about the index ID associated with indexes on a given table. The following example looks for index information about the Item Ledger Entry table.Copy Code sp_helpindex 'CRONUS International Ltd_$Item Ledger Entry'
When a query is executed, it checks whether the query is for the company, table, current key, and search method listed in one of the IndexHint entries. If it is, it will hint the index for the supplied index ID in that entry. The following rules apply:
-
If the company is not supplied, the entry matches all the companies.
-
If the search method is not supplied, the entry matches all the search methods.
-
If the index ID is not supplied, the index hinted is the one that corresponds to the supplied key. This is probably the desired behavior in most cases.
-
If the company, table, or fields are renamed or the table's keys are redesigned, the IndexHint entries must be modified manually.
The following examples illustrate how to add an index hint to the table by executing a statement in Query Analyzer.
Example 1
Copy Code | |
---|---|
INSERT INTO [$ndo$dbconfig] VALUES ('IndexHint=Yes;Company="CRONUS International Ltd.";Table="Item Ledger Entry";Key="Item No.","Variant Code";Search Method="- +";Index=3') |
This will hint the use of the $3
index of the
CRONUS International Ltd_$Item Ledger Entry table for
FIND('-')
and FIND('+')
statements when
the Item No.,Variant Code key is set as the current key for
the Item Ledger Entry table in the CRONUS International Ltd.
company.
Example 2
Copy Code | |
---|---|
INSERT INTO [$ndo$dbconfig] VALUES ('IndexHint=No;Company="CRONUS International Ltd.";Table="Item Ledger Entry";Key="Item No.","Variant Code";Search Method="- +";Index=3') |
The index hint entry is disabled.
Example 3
Copy Code | |
---|---|
INSERT INTO [$ndo$dbconfig] VALUES ('IndexHint=Yes;Company="CRONUS International Ltd.";Table="Item Ledger Entry";Key="Item No.","Variant Code";Search Method="- +";Index=') |
This will hint the use of the Item No.Variant Code index of the
CRONUS International Ltd_$Item Ledger Entry table for
FIND('-')
and FIND('+')
statements when
the Item No.,Variant Code key is set as the current key for the
Item Ledger Entry table in the CRONUS International Ltd.
company.
This is the most commonly used index-hinting feature.
Example 4
Copy Code | |
---|---|
INSERT INTO [$ndo$dbconfig] VALUES ('IndexHint=Yes;Company=;Table="Item Ledger Entry";Key="Item No.","Variant Code";Search Method="-+";Index=3') |
This will hint the use of the $3
index of the
CRONUS International Ltd_$Item Ledger Entry table for
FIND('-')
and FIND('+')
statements when
the Item No.,Variant Code key is set as the current key for the
Item Ledger Entry table for all the companies (including a
non-company table with this name) in the database.
Example 5
Copy Code | |
---|---|
INSERT INTO [$ndo$dbconfig] VALUES ('IndexHint=Yes;Company="CRONUS International Ltd.";Table="Item Ledger Entry";Key="Item No.","Variant Code";Search Method=;Index=3') |
This will hint the use of the $3
index of the
CRONUS International Ltd_$Item Ledger Entry table for every
search method when the Item No.,Variant Code key is set as
the current key for the Item Ledger Entry table in the CRONUS
International Ltd. company.