Keys, Queries, and 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
REPEAT
UNTIL NEXT = 0;

You should define keys and queries in the same way when you are using Classic Database Server. However, Classic Database Server can run the same query almost as efficiently if Customer No. is not the first field in the key. For example, if you have defined a key that contains Country/Region Code as the first field and Customer No. as the second field and if there are only a few different country/region codes used in the entries, the query will take about the same time to run.

The table could have these keys.

Entry No.
Country/Region Code, Customer No.,Posting Date

The query could look like the following.

SETCURRENTKEY("Country/Region Code","Customer No.");
SETRANGE("Customer No.",'1000');
IF FIND('-') THEN
REPEAT
UNTIL NEXT = 0;

SQL Server will not be able to answer this query efficiently and will read through the entire table. This is because SQL Server makes stricter demands than Classic Database Server on the way that keys are defined in tables and on the way they are used in queries. You should define your keys and queries with SQL Server in mind, as this will ensure that your application can run just as efficiently on both server options.

See Also

Community Additions

ADD
Show: