This topic describes the relationship between basic database functions in C/AL and SQL statements.
C/AL and SQL Statements
GET, FIND, and NEXT
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.
FINDSET
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 |
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
|
CALCFIELD and CALCSUMS
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.
INSERT, MODIFY, DELETE, and LOCKTABLE
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.