Each GET (or FIND('=')) operation requires a separate SQL statement unless the client has already retrieved the desired record during a recent operation. If the client reads the same record several times, then SQL Server is only called the first time that the client needs to read the record.

Each FIND('-/+') operation requires a separate SQL statement unless the client has executed the same query (filters) in a recent operation.

Each NEXT (or FIND('>/<')) operation requires at least one SQL statement.

However, when NEXT is used with FIND('-/+') to read a set, as shown in the following example, one SQL statement can cover the needs of all NEXT function calls in the loop.

	Copy Code
IF FIND('-') THEN REPEAT // Insert statements to repeat. UNTIL NEXT = 0;

Reading the set backwards with FIND('+')/NEXT(-1) or using the ASCENDING Function (Record) is equally efficient. You should not read record sets by using "WHILE FIND('- /+') DO" or any similar constructions.

The FINDSET operation optimizes reading records from SQL Server by establishing a stream of records between Microsoft Dynamics NAV and SQL Server. While the stream is open, no other activity occurs between Microsoft Dynamics NAV and SQL Server. Before the records are read, Microsoft Dynamics NAV has no information about how many records are available to read. However, Microsoft Dynamics NAV must allocate enough memory to accommodate all records that it will read for the FINDSET operation. The stream does not allow it to read records in groups. Microsoft Dynamics NAV allocates memory for a preset number of records and then begins reading the records. You can change the value of this preset number by changing the Record Set value in the New Database or Alter Database window. For more information, see the topic "Entering Information in the New Database - Advanced Tab" in the Microsoft Dynamics NAV Application Help.

Note
In Microsoft Dynamics NAV 2009, the default value is 50. In Microsoft Dynamics NAV 5.0, when the FINDSET operation was introduced, the default value was 500.

If the number of records that is read falls within this range, then all records are read with optimized performance. If there are more records to read than the preset number, then Microsoft Dynamics NAV must establish new commands to SQL Server to continue reading records. Microsoft Dynamics NAV reads all records successfully, but the additional commands are expensive for SQL Server to execute. The FIND('-') operation is more efficient than the FINDSET operation when there are more records to read than the preset number.

You must decide when to use the FINDSET operation and when to use the FIND('-') operation based on the maximum number of records to read and the value of the Record Set preset number.

Note
If Microsoft Dynamics NAV detects a pattern in which FINDSET would be a better choice than FIND, then it converts the FIND operation to a FINDSET operation.

Each CALCFIELD or CALCSUMS operation that calculates a sum requires a separate SQL statement unless the client has calculated the same sum or another sum that uses the same SumIndex or filters in a recent operation.

Each CALCFIELD or CALCSUMS request should be confined to use only one SIFT index. The SIFT index can only be used if:

• All requested sum-fields are contained in the same SIFT index.

• The specified filter matches a SIFT index containing all the sum-fields.

If neither of these requirements is fulfilled, then the sum will be calculated directly from the base table.

Each INSERT, MODIFY, or DELETE operation requires a separate SQL statement. If the table that you modify contains SumIndexes, then the operations will be considerably slower. As a test, select a table that contains SumIndexes and execute one hundred of these INSERT, MODIFY, or DELETE operations to measure how long it takes to maintain the table and all its SumIndexes.

The LOCKTABLE operation does not require any separate SQL statements. It only causes any subsequent reading from the table to lock the table or parts of it.