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
or using the is equally efficient. You should not read
record sets by using "
WHILE FIND('- /+') DO" or any
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
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
In Microsoft Dynamics NAV 2009, the default value is 50. In
Microsoft Dynamics NAV 5.0, when the
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
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
CALCFIELD and CALCSUMS
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.
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
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.