Defining Keys to Improve Performance

When you write a query that searches through a subset of the records in a table, you need to be careful when you define the keys both in the table and in the query so that Microsoft Dynamics NAV can quickly identify this subset. For example, the entries for a specific customer will usually be a small subset of a table containing entries for all the customers.

Defining Keys to Improve Performance

The time that it takes to complete a query depends on the size of the subset. If a subset cannot be located and read efficiently, performance will deteriorate.
To maximize performance, you must define the keys in the table so that they facilitate the queries that you will have to run. These keys must then be specified correctly in the queries.

For example,

You would like to retrieve the entries for a specific customer. To do this, you apply a filter to the Customer No. field in the Cust. Ledger Entry table. In order to run the query efficiently on SQL Server, you need to define a key in the table that has Customer No. as the first field. You must also specify this key in the query.

The table could have these keys.
Entry No.,Customer No.,Posting Date

The query could look like the following.
SETCURRENTKEY("Customer No.");
SETRANGE("Customer No.",'1000');
IF FIND('-') THEN
REPEATUNTIL NEXT = 0;