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'

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'

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 in FIND 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'

	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.

The Benefits of Using Index Hinting

Prerequisite

Disabling Index Hinting in Specific Places

Enabling Index Hinting in Specific Places

See Also

Concepts