if not IsEmpty then FindSet... again

Yes, I'm back again on this inexhaustible topic.

The question of weather the call of IsEmpty before FindSet in AL can have a positive effect on the application performance, or on the contrary, it is nothing but a redundant query dragging the performance down invariably sparks lively discussions with lots of arguments on both sides. Still, for now the BC dev community seems to be in agreement that this pattern can help achieve some marginal performance improvement when the queried dataset is almost always empty, and even in this case the difference is so small that probably not worth the argument.

Some time ago, after one of the iterations of this discussion I ran a few tests and wrote this blog post with test results confirming the statement.

But one thing that was not considered in the discussion at that time (and I've never come across this argument) is that Business Central server already implements this optimisation on the platform level.

Let's have a look at the following code snippet and what happens when it is executed.

procedure ConsecutiveZeroRows()
var
    Customer: Record Customer;
    I: Integer;
begin
    SelectLatestVersion();
    for I := 1 to 5 do begin
        Customer.SetRange(
            "Customer Posting Group", 'DUMMYGROUP' + Format(I));
        if Customer.FindSet() then;
    end;
end;

The first time FindSet is called, the BC server will prepare a stored procedure for the select:

declare @p1 int
set @p1=385
exec sp_prepexec @p1 output,N'@0 nvarchar(20)',
    N'SELECT
        "18"."timestamp","18"."No_","18"."Name","18"."Search Name",...'
select @p1

I omitted most of the details of the query, but you know it - this is a simple and straightforward select which is normally issued by the FindSet function, nothing unusual so far.

Second and third iterations of the loop invoke the same procedure with different argument values. The filter value changes inside the loop, but as long as the filter is applied on the same field, BC runs the same stored procedure.

exec sp_execute 385,@0=N'DUMMYGROUP2'

But on the fourth iteration, the server runtime prepares a new SP instead of reusing the previously compiled one. The query now changes to an "optimized" version:

declare @p1 int
set @p1=66
exec sp_prepexec @p1 output, N'@0 nvarchar(20)',
    N'IF EXISTS(
        SELECT TOP 1 NULL 
        FROM "CRONUS".dbo."CRONUS International Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972" "18" 
        WITH(READUNCOMMITTED)  
        WHERE ("18"."Customer Posting Group"=@0))
    SELECT "18"."timestamp","18"."No_","18"."Name","18"."Search Name",...'
select @p1

From this moment on, all invocations of Customer.FindSet in the loop will be wrapped in the IF EXISTS condition until the query returns a non-empty result. When the first part of the query SELECT TOP 1 NULL returns an empty result, the following subquery is not executed. I don't see any more selects with the full list of table fields in the profiler. Now it's only SELECT TOP 1 NULL.

And this is exactly the same query which would be triggered by the IsEmpty function - just sent by the BC runtime without an explicit IsEmpty call. Which in fact means that the pattern "if not IsEmpty then FindSet" is, first of all, redundant, and secondly, can force the query "SELECT TOP 1 NULL" to be executed twice in case of intermittently empty results.

Since each query with its filters and options is a separate class instance, consecutive empty results are counted for all queries separately. All the calls of FindSet on the Customer record in the next code sample will issue a usual SELECT query in the first three iterations of the loop before switching to the "IF EXISTS" query on the fourth iteration.

for I := 1 to 5 do begin
    Customer.Reset();
    Customer.SetRange("Customer Posting Group", 'DUMMYGROUP' + Format(I));
    if Customer.FindSet() then;

    Customer.Reset();
    Customer.SetRange("VAT Bus. Posting Group", 'DUMMYGROUP' + Format(I));
    if Customer.FindSet() then;

    Customer.Reset();
    Customer.SetRange(
        "Gen. Bus. Posting Group", 'DUMMYGROUP' + Format(I));
    if Customer.FindSet() then;
end;

This feature does not seem to be documented anywhere and can be changed without any notice, but it's a very curious example of internal runtime optimisation in Business Central.