C/AL Database Functions and Performance on SQL Server
This topic describes the relationship between basic database functions in C/AL and SQL statements.
C/AL and SQL Statements
GET, FIND, and NEXT
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.
FIND('-/+') operation requires a separate SQL statement unless the client has executed the same query (filters) in a recent operation.
NEXT (or FIND('>/<')) operation requires at least one SQL statement.
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.
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.
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.
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.
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
CALCFIELD and CALCSUMS
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.
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.
INSERT, MODIFY, DELETE, and LOCKTABLE
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
DELETE operations to measure how long it takes to maintain the table and all its SumIndexes.
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.