The time that it takes to complete a loop through a subset of records depends on the size of the subset. If a subset cannot be located and read efficiently, then performance deteriorates.

To maximize performance, you must define the keys in the table that support the code that you run. You must then specify these keys correctly in your code.

For example, to retrieve the entries for a specific customer, you apply a filter to the Customer No. field in the Cust. Ledger Entry table. To run the code efficiently on Microsoft SQL Server, you must define a key in the table that has Customer No. as the first field.

The table could have the following keys:

• Entry No.
  
• Customer No.,Posting Date
  

The following is an example of code that finds a subset of records.

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

SQL Server automatically chooses which index to use in order to retrieve data in the most efficient way. SQL Server calculates the cost of retrieving data using different indexes and then chooses the path that has the smallest cost. For Microsoft Dynamics NAV, that calculation is based only on the statistical distribution of values in a column.

For example, if a table contains 1000 rows and a column in the table contains either the value 0 or the value 1, then that column is said to have a low selectivity. If instead a column contained the values ranging from 1 to 500 then the column is said to have a high selectivity. In the following code example, SQL Server chooses an index that contains the HighSelectivityColumn and then sorts the rows by the LowSelectivityColumn.

	Copy Code
SETCURRENTKEY("LowSelectivityColumn"); SETFILTER("LowSelectivityColumn",'1'); SETFILTER("HighSelectivityColumn",'777'); FIND('-')

Concepts