Author: Alain Krikilion (alias kriki)
I saw a
lot of times that a lot of people use the different ways to get the data from
the database in a not really performant way. Or they write statements that are
not needed and this way they create some confusion in the code.
This
stimulated me to write this blog to explain with examples how (not) to use the
different statements and when (not) to use them.
Most
points are with SQL in mind. But some of them are also valid for native DB.
CLEAR,
INIT and RESET: Lets start with the difference between these 3:
TheTable.RESET: this is used to remove all FILTERS (also in different
FILTERGROUP's) from a record-variable. It also resets the SETCURRENTKEY to the
primary key.
IMPORTANT: DO NOT USE this statement for clearing the fields in a
record. It will NOT clear them.
VERY WRONG: If the record has some data in it before the
RESET-statement, it will keep these values.
TheTable.RESET;
TheTable."Primary Key" := Some Value;
TheTable."Field N" := Some Value;
TheTable.INSERT(FALSE);
CORRECT:
CLEAR(TheTable); // also clears the primary-keyfields
TheTable."Primary Key" := Some Value;
TheTable."Field N" := Some Value;
TheTable.INSERT(FALSE);
TheTable.INIT: this statement clears all fields in the table EXCEPT THE
PRIMARY-KEY FIELDS! It does NOT touch the FILTERS or CURRENTKEY! So if you want
to insert a new record, keeping the primary key-fields already in the record-variable,
this is the command to use.
CLEAR(TheTable): this statement does a TheTable.RESET AND a
TheTable.INIT AND clears the primary keyfields. I almost always use this
statement to INSERT a new record. It is the only command that also resets the
CHANGECOMPANY-statement.
GET:
This command is the best to use if you want to search the record using its
primary key. You can also filter on the primary key and use FINDFIRST. The
SELECT-statement sent to SQL is the same. But the GET requires less coding and
is easier to read. No RESET-SETCURRENTKEY-SETRANGE are needed. The GET does NOT
EVEN consider them.
WRONG (because it creates confusion):
TheTable.RESET;
TheTable.SETCURRENTKEY(...)
TheTable.SETRANGE/SETFILTER
TheTable.GET(...);
CORRECT:
TheTable.GET(...);
In
case you want to do a GET and even if you don't have a record, you want to
continue with your code, you can do this:
Version 1
CLEAR(TheTable);
IF TheTable.GET(....) THEN ;
Version 2
IF NOT TheTable.GET(....) THEN
CLEAR(TheTable);
Both versions will have a clean record in case they don't find a record.
No dirty data will be in it after this code. Personally I prefer the first
version: I prefer FIRST to clean the record and THEN to get the data (if
available).
The
other commands need filtering, so before I start explaining them, I give a good
programming practice that is valid for all of them. To make the code easy to
read/interpret/review/fix/... , it is best to put the commands influencing the
filtering together starting with a RESET. This makes sure you never have to
start searching if some other part of the object has put some filters on it and
if these filters should be there or not in your part of the code. I always use
them together. This makes sure to other programmers I didn't forget to put the
SETCURRENTKEY.
TheTable.RESET;
TheTable.SETCURRENTKEY(...); // even if you want to use the primary key
TheTable.SETRANGE(...) or TheTable.SETFILTER(....)
Some
remarks
FIND,FINDSET opens a cursor on SQL. Opening a cursor is quite heavy, so
if you can avoid it, it is better.
The
following commands are examples to get info when you DON'T want to lock
records. Later I give more info if you want to lock records.
FINDSET gets the first N records in 1 go. N is defined in
File=>Database=>Alter=>Tab advanced=>Caching=>Record Set.
FIND('-'): This statement should not be used anymore (there are a few
exceptions I will explain later), because there are better statements like
FINDFIRST and FINDSET.
FIND('+'): This command should not be used anymore (there are a few
exceptions I will explain later), because there are better statements like
FINDLAST.
FIND('='): This statement is a little strange. It almost works like the
GET using the primary key values to find a record. You use it by filling up the
fields of the primary key and only then doing a FIND('='). It ALSO considers
the filters on the record! It can be useful if you already have the record you
need in the variable, and you want to refresh it.
FIND('>') / FIND('<'): This bases itself on the current values in
the primary key of the record to get the next or previous record, it ALSO
considering the filters. Better use NEXT or NEXT(-1).
FIND('=<>'): This is the worst of all. It first tries FIND('=')
and if it doesn't find a record, it does a FIND('<') and if it doesn't find
anything, it does a FIND('>'). TO BE AVOIDED!
FINDFIRST: This is THE statement to use if you only need the first
record. But NEVER use it if you need to loop records.
FINDLAST: This is THE statement to use if you only need the last record.
But NEVER use it if you need to loop records.
FINDSET: This is THE statement to use if you want to get a set of
records. It will get the first N records in one go. If the actual number of
records is smaller, it will have all the records on your client at once. N is
defined in File=>Database=>Alter=>Tab Advanced=>Caching: Record set
ISEMPTY: This is THE statement to use if you want to check if there is
at least one record in the (filtered) table.
COUNT:
This statement is used if you want a precise count of the number of records in
the (filtered) table. It is heavier for the server to execute than the
COUNTAPPROX.
COUNTAPPROX: This statement is used if you want an approximate count of
the number of records in the (filtered) table. It can be used for a
progressbar. It is lighter for the server to execute than the COUNT.
EXAMPLES for reading data WITHOUT locking the
records
You
need to check if there are ANY records in your (filtered) table, but you don't
need any of them to work on. There are a lot of possibilities that all work,
but performance can be a big problem
(VERY) WRONG: because it counts ALL records
IF
TheTable.COUNT > 0 THEN ... // or COUNTAPPROX
WRONG:
It returns a SET of records to the client
IF
TheTable.FINDSET THEN ...
(LESS) WRONG: Until some versions ago (I don't remember which), you
would use this because there was no alternative. This opens a cursor on SQL.
IF
TheTable.FIND('-') THEN ... // or FIND('+')
(STILL) WRONG: It doesn't open a cursor in SQL, but it still returns 1
record (if there is at least 1 available)
IF
TheTable.FINDFIRST THEN ... // or FINDLAST
CORRECT: it NEVER returns a record. It just returns a boolean to say
there are/are not records.
IF
NOT TheTable.ISEMPTY THEN ...
You
want ONY the first/last record (if it exists), but NEVER more.
VERY WRONG: It returns a set of records to the client
If
TheTable.FINDSET THEN ... // to get the last record , you would need
to
put ASCENDING(FALSE) before calling FINDSET (later more on this
because it is even MORE WRONG than VERY WRONG)
WRONG: This opens a cursor on SQL.
If
TheTable.FIND('-') THEN ... // or FIND('+')
CORRECT: it doesn't open a cursor and it returns only 1 record
If
TheTable.FINDFIRST THEN ... // or FINDLAST
You
want ALL records in ASCENDING order
VERY WRONG: The FINDFIRST does not open a
cursor and returns ONLY the first record. The first time NEXT is called, NAV
sees it hasn't a cursor and creates it in that moment.
IF
TheTable.FINDFIRST THEN
REPEAT
...
UNTIL TheTable.NEXT = 0;
WRONG: This will give you 1 record at a time.
IF
TheTable.FIND('-') THEN
REPEAT
...
UNTIL TheTable.NEXT = 0;
CORRECT: This will give you the first N records in one go, and after
that 1 by 1.
IF
TheTable.FINDSET THEN
REPEAT
...
UNTIL TheTable.NEXT = 0;
You
want ALL records in DESCENDING order
MORE WRONG than VERY WRONG: It will generate a run-time error (try it out
to know the error-message)
TheTable.ASCENDING(FALSE);
IF
TheTable.FINDSET THEN
REPEAT
...
UNTIL TheTable.NEXT = 0;
CORRECT: version 1. Use this version if possible. It uses a FINDSET to
get the records in a faster way and save them in a temptable. Then it loops the
temptable in DESCENDING order. This can be useful if the number of records is
<= N (the number of records returned by FINDSET in one go). It is also best
NOT to use this if there are a lot of records because all those records will be
first saved in a temptable and thus on the client.
IF
TheTable.FINDSET THEN
REPEAT
// save the records in a temptable
tmpTheTable := TheTable;
tmpTheTable.INSERT(FALSE);
UNTIL TheTable.NEXT = 0;
//
now loop the temptable
tmpTheTable.RESET;
tmpTheTable.ASCENDING(FALSE);
IF
tmpTheTable.FIND('-') THEN
REPEAT
...
UNTIL tmpTheTable.NEXT = 0;
CORRECT: version 2
TheTable.ASCENDING(FALSE);
IF
TheTable.FIND('-') THEN
REPEAT
...
UNTIL TheTable.NEXT = 0;
EXAMPLES for modifying records.
First
and for all some general advice for writing to the DB
Start a transaction as late as possible (meaning : first read all the
data without locking anything and after that, start writing).
Lock as little as possible (if you change 1 record in a 10MB records
table, why lock all those records if you can only lock 1?).
Make your transactions as fast as possible (putting SLEEP-statements in
a transaction is VERY (!) BAD).
Make your transactions as small as possible
(ok, not too small : if you need to change 1000 records in a table, don't put a
COMMIT after each write).
Make your transaction so, that each transaction contains all writes it
should do in 1 block or not at all.
IMPORTANT REMARK:
LOCKTABLE (or FINDSET(TRUE,...)) works differently from LOCKTABLE in
native. In native it blocks the table completely. In SQL, it means that from
that point on, all records read in that table will be exclusively locked, BUT
it will not lock records in other tables. It is also possible some other
records are locked because of the locking mechanism in SQL on which we have no
control at all (at least not in C/AL).
You
want to modify 1 record.
WRONG : This will generate 2 (!) SELECT statements in SQL. The first
SELECT is a NO-LOCK and will be executed for the GET-statement. The second
SELECT will be EXCLUSIVE-LOCK and will be executed for the MODIFY. After this,
an UPDATE-statement will be executed. The second SELECT is needed to
exclusively lock the record, to check if no one else changed the record between
the GET statement and the MODIFY statement.
//
You don't have a LOCKTABLE on it (a LOCKTABLE on another table will
NOT
lock current table
TheTable.GET(...); // or also FINDFIRST,FINDLAST
TheTable."Some Field" := 'Some Value';
TheTable.MODIFY(FALSE);
CORRECT: The GET will generate a SELECT with EXCLUSIVE-LOCK. The MODIFY
will generate an UPDATE-statement
TheTable.LOCKTABLE;
TheTable.GET(...); // or also FINDFIRST,FINDLAST
TheTable."Some Field" := 'Some Value';
TheTable.MODIFY(FALSE);
You
read 1 record and optionally you change it.
CORRECT in case the chance is quite low that you need to change the
record so you avoid locking when it is not needed.
//
You don't have a LOCKTABLE on it (a LOCKTABLE on another table will
NOT
lock current table
TheTable.GET(...); // or also FINDFIRST,FINDLAST
IF
to be changed THEN BEGIN
TheTable."Some Field" := 'Some Value';
TheTable.MODIFY(FALSE);
END;
CORRECT in case most of the time you need to change the record to avoid
a second SELECT in SQL
TheTable.LOCKTABLE;
TheTable.GET(...); // or also
FINDFIRST,FINDLAST
IF
to be changed THEN BEGIN
TheTable."Some Field" := 'Some Value';
TheTable.MODIFY(FALSE);
END;
You
read a set of records and you need to change ALL records
VERY WRONG: first you need to use FINDSET (see before for more info on
this). You are using the same record-variable to change the field. It can
happen that you skip records, that you process records more than once, that you
get into an endless loop! And also you didn't lock the table...
IF
TheTable.FINDFIRST THEN // or also FIND('-')
REPEAT
TheTable."Some Field" := 'Some Value';
TheTable.MODIFY(FALSE);
UNTIL TheTable.NEXT = 0;
WRONG: you didn't lock the table, so the MODIFY will create an extra
SELECT
IF
TheTable.FINDSET THEN
REPEAT
TheTable2 := TheTable;
TheTable2."Some Field" := 'Some Value';
TheTable2.MODIFY(FALSE);
UNTIL TheTable.NEXT = 0;
CORRECT: You lock the table WITH the first TRUE in the FINDSET. The
second TRUE is only needed in certain circumstances but I advice to use it
always.
IF
TheTable.FINDSET(TRUE,TRUE) THEN
REPEAT
TheTable2 := TheTable;
TheTable2."Some Field" := 'Some Value';
TheTable2.MODIFY(FALSE);
UNTIL TheTable.NEXT = 0;
You
read a set of records and you need to change SOME records
The
VERY WRONG and WRONG from previous point are also valid here.
WRONG: This example was correct for previous point, but not here because
this blocks ALL records you read. LOCKING records is memory intensive and also
costs performance because if the extra work on the server and because other
users might be blocked by this session even if they want to change other
records. It is also a reason for the dreaded DEADLOCKS.
IF
TheTable.FINDSET(TRUE,TRUE) THEN
REPEAT
TheTable2 := TheTable;
TheTable2."Some Field" := 'Some Value';
TheTable2.MODIFY(FALSE);
UNTIL TheTable.NEXT = 0;
CORRECT: You first read the records and the ones you need to change, you
put in a temptable WITHOUT LOCKING ANYTHING.
//
You do NOT have a LOCKTABLE on table "TheTable"!
IF
TheTable.FINDSET THEN
REPEAT
// save the records in a temptable
IF (Record has to be changed) THEN BEGIN
tmpTheTable := TheTable;
tmpTheTable.INSERT(FALSE);
END;
UNTIL TheTable.NEXT = 0;
//
now do a LOCKTABLE. This means that all records of that table that are
read now, will be EXCLUSIVELY locked
CLEAR(TheTable);
TheTable.LOCKTABLE;
There are 2 ways to loop the records:
Method 1:
//
now loop the temptable. This code uses the version of each record in
the
temptable to change the record in the table. If one of the records
has
been changed in the mean time, it will generate an error. (remark:
the
MODIFY will also generate a SELECT EXCLUSIVE-LOCK before the UPDATE-
statement. This is necessary because NAV has to check if the version of
the
record is still the same.)
tmpTheTable.RESET;
IF
tmpTheTable.FINDSET THEN
REPEAT
TheTable := tmpTheTable;
TheTable."Some Field" := 'Some Value';
TheTable.MODIFY(FALSE);
UNTIL tmpTheTable.NEXT = 0;
Method 2:
//
now loop the temptable. This code retrieves the record again (with
EXCLUSIVE-LOCK!) and then changes it. If the record was changed between
the
first read and now, it will NOT generate an error on the MODIFY
because you will have received the latest version.
tmpTheTable.RESET;
IF
tmpTheTable.FINDSET THEN
REPEAT
TheTable := tmpTheTable;
TheTable.FIND('='); // or TheTable.GET(primary key). Both commands
are
possible but you need to be sure you don't have filters on TheTable
(the CLEAR(TheTable) before takes care of that.
TheTable."Some Field" := 'Some Value';
TheTable.MODIFY(FALSE);
UNTIL tmpTheTable.NEXT = 0;
Using
filtergroups: Filtergroups are useful for putting multiple filters on the same
field or to hide filters from users.
Some advice: Only start using filtergroups from 10 and up. Filtergroups
0 to 6 are reserved (see the C/SIDE reference guide for more info), but I would
keep some extra filtergroups free for the future (until some versions ago,
filtergroup 6 was NOT reserved).
Important: multiple filters on the same field behave as AND between
them.
Example 1: you want to hide a filter from the user to avoid he can
change it
TheTable.RESET;
TheTable.SETCURRENTKEY(....);
TheTable.FILTERGROUP(10); // changes to a filtergroup that the user will
NOT
see
TheTable.SETRANGE(....);
TheTable.FILTERGROUP(0); // change back to the default filtergroup. The
one
in which the user may do want he wants
FORM.RUNMODAL(0,TheTable); // the user will not be able to change the
filters
Example 2: You need to put multiple filters on a field. E.g. You want
all records in which a certain field starts with an A and ends with a B and
somewhere in the middle is a C.
Of course you might create this SETFILTER. But how would you SETFILTER
more complex queries?
TheTable.RESET;
TheTable.SETCURRENTKEY(...);
TheTable.SETFILTER("The Field",'A*C*B');
IF TheTable.FINDSET THEN
The same example with FILTERGROUP
TheTable.RESET;
TheTable.SETCURRENTKEY(...);
TheTable.FILTERGROUP(10);
TheTable.SETFILTER("The Field",'A*');
TheTable.FILTERGROUP(11);
TheTable.SETFILTER("The Field",'*C*');
TheTable.FILTERGROUP(12);
TheTable.SETFILTER("The Field",'*B');
TheTable.FILTERGROUP(0);
IF TheTable.FINDSET THEN
Reading/inserting/modifying/deleting records in another company than the
current one.
IMPORTANT: Remember that ONLY that record will be in the other company.
Any other record-variable accessed by variables in the code in that record or
in the Tablerelation of a field will be on the CURRENT company.
(VERY) WRONG: you should NEVER use triggers when
inserting/modifying/deleting a record that is in another company. All the code
behind the triggers/Tablerelations will work on the CURRENT company.
CLEAR(TheTable);
TheTable.CHANGECOMPANY('Some Other Company');
TheTable.VALIDATE("Field 1",'Some Value');
TheTable.VALIDATE("Field 2",'Some Value');
TheTable.VALIDATE("Field 3",'Some Value');
TheTable.INSERT(TRUE);
CORRECT: to insert a record into another company (MODIFY and DELETE work
in the same way).
CLEAR(TheTable);
TheTable.CHANGECOMPANY('Some Other Company');
TheTable."Field 1" := 'Some Value';
TheTable."Field 2" := 'Some Value';
TheTable."Field 3" := 'Some Value';
TheTable.INSERT(FALSE);
CORRECT: Reading 1 record in another company
CLEAR(TheTable);
TheTable.CHANGECOMPANY('Some Other Company');
TheTable.GET(....);
CORRECT: Reading/deleting/modifying 1 or
multiple record(s) in another company
CLEAR(TheTable);
TheTable.CHANGECOMPANY('Some Other Company');
TheTable.SETRANGE("Field 1",'Some Value');
TheTable.FINDSET;
REPEAT
UNTIL TheTable.NEXT = 0;
//Or
TheTable.DELETEALL(FALSE); // No triggers!!!!!
//Or
TheTable.MODIFYALL(FALSE); // No triggers!!!!!
You want the total inventory of a certain
item in a certain location. There are 2 methods to do the same thing. Both have
pro- and contra.
CORRECT: (PRO : you don't need to know the index ; CONTRA : normal
fields you have to assign a value (or do a GET) and flowfilters you need to
filter)
CLEAR(recItem);
recItem."No." := '1000'; // or also recItem.GET('1000');
recItem.SETRANGE("Location Filter",'BLU');
recItem.CALCFIELDS(Inventory);
MESSAGE('%1',recItem.Inventory);
CORRECT (PRO : always filtering and not assigning values ; CONTRA : you
need to know the index on which the SIFT-field has been defined):
recItemLedgerEntry.RESET;
recItemLedgerEntry.SETCURRENTKEY("Item no.","Location Code");
recItemLedgerEntry.SETRANGE("Item No."','1000');
recItemLedgerEntry.SETRANGE("Location Code",'BLU');
recItemLedgerEntry.CALCSUMS(Inventory);
MESSAGE('%1',recItemLedgerEntry.Quantity);
If you
need 2 or more recordvariables of the same record, you can also use an array in
stead of 2 variables. The 2 variables work independently of each other like
using 2 variables. Some examples.
recRecordVar1.RESET; => recRecordVar[1].RESET;
CLEAR(recRecordVar1); => CLEAR(recRecordVar[1]);
recRecordVar1.SETRANGE(... => recRecordVar[1].SETRANGE(...
You
can also use a variable to indicate the element. An example:
intSomeInteger := 1;
recRecordVar1.RESET; => recRecordVar[intSomeInteger].RESET;
The
array technique can also be used on temporary record-variables. They work also
independently of each other like on a real table. BUT THERE IS ONLY 1
TEMPTABLE! This means that if you create a record with element [1], it will be
available in element [2]! An example (This is the very first code on the
temptable, so there are no records in it!).
CLEAR(recRecord[1]);
recRecord[1]."No." := '1');
recRecord[1].INSERT(FALSE);
recRecord[2].RESET;
recRecord[2].FINDFIRST;
MESSAGE('%1',recRecord[2]."No."); // this will show the value
of the record I
created
in element [1]!
Make a
total amount per "Gen. Bus. Posting Group" and "Gen. Prod.
Posting Group" of table 15:"G/L Entry".
In
SQL it would be easy:
SELECT "Gen_ Bus_ Posting Group","Gen_ Prod_ Posting
Group",sum(Amount)
FROM dbo."CRONUS International Ltd_$G_L Entry"
GROUP BY "Gen_ Bus_ Posting Group","Gen_ Prod_ Posting
Group"
ORDER BY "Gen_ Bus_ Posting Group","Gen_ Prod_ Posting
Group"
But
in C/AL we can't use SQL-statements (or we must use ADO), so there is another
way. I advice to always use this way to do some summing in C/AL.
recGLEntry.RESET;
recGLEntry.SETCURRENKEY(...); // try to get a key that can be used to
find the filtered data in a fast way
recGLEntry.SETRANGE(.....); // put your filters
IF
recGLEntry.FINDSET THEN
REPEAT
// "tmpGLEntry" is the temptable we will use to store the
grouping
totals
tmpGLEntry.RESET;
// try to get a good key for the filters
IF NOT tmpGLEntry.SETCURRENTKEY("Gen. Bus. Posting Group")
THEN
IF NOT tmpGLEntry.SETCURRENTKEY("Gen. Prod. Posting Group")
THEN ;
// I filter on the records for which I want to group the records
tmpGLEntry.setrange("Gen. Bus. Posting
Group",recGLEntry."Gen. Bus.
Posting Group");
tmpGLEntry.setrange("Gen. Prod. Posting
Group",recGLEntry."Gen. Prod.
Posting Group");
IF NOT tmpGLEntry.FINDFIRST THEN BEGIN
// I didn't find a record, so I have to create a new one.
// Remember that to insert a record, you have to respect a unique
primary key.
// This is done, because EACH "recGLEntry" I read is unique,
so I
can
just insert
// "recGLEntry" in my temptable.
tmpGLEntry := recGLEntry;
tmpGLEntry.insert(FALSE);
END
ELSE BEGIN
// I found the record with combination I wanted, so I add the
field(s) I am SUMming
tmpGLEntry.Amount += recGLEntry.Amount;
tmpGLEntry.MODIFY(FALSE);
END;
UNTIL recGLEntry.NEXT = 0;
//
In the temptable I now have records that contain the SUM for the
combination I wanted.
//
Of course if you want to sort your data in a certain way, you will
need a key for it and with SQL you can put property MaintainSQLIndex to
FALSE.
//
Another way is to create a new table with all the fields and keys you
need and use that as
//
temptable. You DON'T need to license a new table that you use ONLY
as
temptable!
//
Now you can just read the temptable and do what you want to do with
your totals
tmpGLEntry.RESET;
FORM.RUNMODAL(0,tmpGLEntry);
No comments:
Post a Comment