Hidden SQL queries in AL you (probably) want to avoid

Development of Business Central extensions is very much about the database communication, and as AL developers, we always control the data we receive from the database, don't we?

Well... In fact, Business Central hides much of what happens under the hood when it comes to the database queries to keep the language structures simple and optimize the database interaction on the platform level. And sometimes AL code can force BC runtime to send a database query which is not explicitly generated by the code being executed. Usually, we don't even notice when these things happen, but it's good to be aware when and why an additional SQL query may be required. Usually this happens in a loop iterating on some table records, when the Next function is called, but the next record is not prepared. In this case Next has to do the job of a Find and read records from the database. Below are a few examples of such cases.

1. FindFirst with Next

A very simple and obviously incorrect structure for the first example.

procedure FindFirstNext()
var
    GLEntry: Record "G/L Entry";
begin
    GLEntry.FindFirst();
    repeat
    until GLEntry.Next() = 0;
end;

This is a mistake even the CodeCop analyzer can immediately spot (and the rule AA0223 is one of those that must be always on). So what happens when we run this code and why calling Next after FindFirst is not such a great idea?

In old Navision versions, the Find function was actually creating a database cursor, which Next iterated on. Business Central server uses an internal buffer to store selected records instead of a cursor. So when we run a pair Find/Next, the following happens.

• Find* function: Runs a SELECT query, loads selected records into the record buffer and positions the active record pointer on the first selected entry.

  
  

• Next: If the next record is available in the record buffer and the state of the query command has not changed, moves the pointer and return the next record.

But it is not guaranteed that any of the Find* methods will have all the selected records in the buffer. The recordset may be simply too big and exceed the buffer size. And therefore there is a possibility that at a certain point in time Next will not find any record in the buffer to move to, so a new portion of data has to be be retrieved from the database. And this is where magic happens - AL developers don't have to worry about additional data loads - server runtime takes care of it as necessary. If we capture the SQL trace in this moment, we see an unexpected query that appears after Record.Next is invoked.

So, in case of the example above, FindFirst selects only the top 1 record:

SELECT TOP (1)
    "17"."timestamp","17"."Entry No_","17"."G_L Account No_", ...
FROM "CRONUS".dbo."CRONUS International Ltd_$G_L Entry$" "17"  WITH(READUNCOMMITTED) ORDER BY "Entry No_" ASC

When it comes to Next, there is no record in the buffer to return, so it has to go back to the database to read the next portion of data. The actual parameterized query prepared by the statement looks like this:

exec sp_prepexec @p1 output,N'@0 int,@1 int',
N'SELECT  TOP (@0)
    "17"."timestamp","17"."Entry No_","17"."G_L Account No_",...
FROM "CRONUS".dbo."CRONUS International Ltd_$G_L Entry$" "17"
WITH(READUNCOMMITTED)
WHERE ("17"."Entry No_">@1) ORDER BY "Entry No_" ASC OPTION(FAST 50)',
@0=50,@1=1

Next selects the top 50 rows with Entry No. greater than 1. Entry No. 1 has already been read, so it makes sense that we move on to the next one. And note that it selects exactly 50 records, and not all of the remaining table.

Only after all these 50 records have been read from the buffer and the Next function is called again, will it issue a query without the TOP 50 clause.

exec sp_prepexec @p1 output,N'@0 int',
N'SELECT 
    "17"."timestamp","17"."Entry No_","17"."G_L Account No_",...
FROM "CRONUS".dbo."CRONUS International Ltd_$G_L Entry$" "17"  WITH(READUNCOMMITTED)  
WHERE ("17"."Entry No_">@0) 
ORDER BY "Entry No_" ASC OPTION(FAST 50)',
@0=51

Now it is filtering all entries with Entry No. > 51.

So, instead of just one query selecting all g/l entries, we got three, each one retrieving only a part of the dataset. There may be more, in fact, if the number of rows returned by the last query cannot fit into the buffer. But at least the first two queries are redundant - and this is what the CodeCop analyzer wans us about.

2. Find('-')

Similarly, Find('-') selects only top 50 records, and once the repeat..until loop iterates on all 50 and the record buffer is depleted, Next will send another SQL query.

procedure FindMinus()
var
    GLEntry: Record "G/L Entry";
begin
    GLEntry.Find('-');
    repeat
    until GLEntry.Next() = 0;
end;

This code sample generates two queries in the CRONUS company. The first one is SELECT TOP 50, and the second will be selecting the rest of the dataset, just like in the first example:

SELECT ... WHERE "Entry No." > 50

Therefore, if we expect to receive a large number of records, FindSet is preferable to Find('-') because it could spare us one extra DB query.

3. Changing table filters inside a loop

Any actions affecting filters on a record variable invalidate the record buffer associated with this variable. Functions like SetFilter, SetRange, Reset invoked before Next cause the Next function to send another query to refresh the buffer.

procedure ChangeFilterInsideLoop()
var
    GLEntry: Record "G/L Entry";
begin
    GLEntry.SetRange("G/L Account No.", '1140');
    GLEntry.FindSet();
    repeat
        GLEntry.SetRange("Document No.", 'START');
    until GLEntry.Next() = 0;
end;

This trick is often used intentionally to skip a group of records and move the buffer position to the next group. But be aware of the actual database queries triggered by this code. The first FindSet is just a usual SELECT returning all records satisfying the filtering conditions.

SELECT 
    "17"."timestamp","17"."Entry No_","17"."G_L Account No_",...
FROM "CRONUS".dbo."CRONUS International Ltd_$G_L Entry$" "17"  
WITH(READUNCOMMITTED)  
WHERE ("17"."G_L Account No_"=@0) 
ORDER BY "Entry No_" ASC OPTION(FAST 50)

When Next is hit, the query it sends will be "SELECT TOP 50".

SELECT TOP (@0) 
    "17"."timestamp","17"."Entry No_","17"."G_L Account No_",...
FROM "CRONUS".dbo."CRONUS International Ltd_$G_L Entry$" "17"  
WITH(READUNCOMMITTED)  
WHERE 
    ("17"."Entry No_">@1 AND 
    ("17"."G_L Account No_"=@2 AND "17"."Document No_"=@3)) 
ORDER BY "Entry No_" ASC OPTION(FAST 50)

Considering that we rarely need an arbitrary set of 50 records, in many cases the code from the above example can be restructured to use FindFirst or FindSet. Depending on whether the code is intended to process a single record or all records within the filtered set, one of these two functions may be better fit for the purpose.

4. Modification in a loop

Much to my surprise, this "SELECT TOP 50" seems to be unavoidable.

Modification of a record changes the internal record state. It forces BC to issue all subsequent selects on the same table with the READCOMMITTED hint if tri-state locking is enabled and UPDLOCK if it's disabled. And this applies to the record being modified as well. This means that if we select a record with the default isolation level and then modify the record, this modification renders the buffer state invalid. Let's look at the following code example.

procedure ModifyNoForUpdateKey()
var
    MyTable: Record "My Table";
begin
    MyTable.SetRange("Entry No.", 1, 200);
    MyTable.FindSet();
    repeat
        MyTable.Validate(Amount, MyTable.Amount * 2);
        MyTable.Modify(true);
    until MyTable.Next() = 0;
end;

FindSet here sends a query with the default READUNCOMMITTED hint. When a record is modified, all following reads must be locking, and this applies to the Next function too, so it will send the usual "SELECT TOP 50" query, but this time with the READCOMMITTED hint.

What is more surprising is that this query is sent even if the first FindSet is called with the ForUpdate argument (FindSet(true)). Setting the ReadIsolation property to UpdLock does not change the query either - there will be an additional SELECT TOP 50 ... WITH (UPDLOCK). If I set the ReadIsolation to ReadCommitted, Next still sends another SELECT query with the READCOMMITTED hint.

5. Setting the ReadIsolation property before Next

This is a variation of the previous example. Changing the isolation level for a record variable forces the following Next to update the record buffer. And as it always happens when Next reads another batch, it reads the top 50 records.

procedure ReadIsolationInsideLoop()
var
    CustLedgerEntry: Record "Cust. Ledger Entry";
begin
    CustLedgerEntry.ReadIsolation := IsolationLevel::ReadUncommitted;
    CustLedgerEntry.SetRange("Customer No.", '30000');
    CustLedgerEntry.FindSet();
    repeat
        CustLedgerEntry.ReadIsolation := 
            IsolationLevel::ReadCommitted;
    until CustLedgerEntry.Next() = 0;
end;

CustLedgerEntry.Next is this case sends a SELECT TOP 50 query to get the next batch of records with ReadCommitted isolation.

SELECT TOP (@0) 
    "21"."timestamp","21"."Entry No_","21"."Customer No_",
FROM "CRONUS".dbo."CRONUS International Ltd_$Cust_ Ledger Entry$" "21"  
WITH(READCOMMITTED) 
WHERE ("21"."Entry No_">@1 AND "21"."Customer No_"=@2)
ORDER BY "Entry No_" ASC OPTION(FAST 50)

So we better be sure to choose the required isolation level before the loop and not change it once the records have been selected if we want to avoid extra queries.

6. Delete in a loop

The code in the last example is itself inefficient enough even without any hidden queries. But in addition to row-by-row deletes being so slow, Next function here adds a couple of extra selects.

procedure DeleteInsideLoop()
var
    MyTable: Record "My Table";
begin
    MyTable.SetRange("Entry No.", 1, 200);
    MyTable.FindSet();
    repeat
        if MyTable."Posting Date" = 20240101D then
            MyTable.Delete();
    until MyTable.Next() = 0;
end;

Just like in all previous examples with Next, the first call of the Next function selects a set of 50 records (SELECT TOP 50). Once all the selected 50 records have been processed, Next issues another query, this time selecting all remaining records.

Of course this example is very much simplified and the condition inside the loop obviously belongs to table filters, then it's easy to replace the whole loop with a single DeleteAll. There are cases where the condition is not so simple and can't be easily changed to a filter, but it's worth trying. If the OnDelete trigger does not have to be triggered, DeleteAll is much faster when deleting a large number of rows.