LC0082 - StefanMaron/BusinessCentral.LinterCop GitHub Wiki

Consider using a Query or Rec.Find('-') with Rec.Next()

Using Rec.Count() to determine if a table has exactly one or more records can cause significant performance issues, especially on large tables, as it triggers a full table or index scan.

*** Before making any changes to your code, reading The 'IF COUNT = 1' Conundrum – Vjeko.com is an absolute must. ***

Query

To get the best performance, simply use a query object.

Whenever you need to look for a specific count of rows on a table (be it 1, or another exact number) you can write a query over that table, and have it return the first primary key field. Then, you set the TOP clause on the resulting SQL SELECT statement through the TOPNUMBEROFROWS function. If you are looking for COUNT = 1, you use TOPNUMBEROFROWS(2). Then, you count how many rows you retrieved.

Using this approach you can approach the blazing fast performance of ISEMPTY, at the expense of having to create one object per table of interest. This won't be that many query objects, because you very rarely need COUNT = 1, and when you do, it may be that the table in question isn't too busy or too populated, so COUNT would be faster anyway no matter what. Large, busy tables are when you need to start worrying about performance of COUNT, and only these tables will require you to write the COUNT-replacement queries.

procedure MyProcedure()
var
    GLEntry: Query "G/L Entry";
    NumberOfRecords: Integer;
begin
    GLEntry.TopNumberOfRows(2); // If you are looking for .Count() = 1, you use .TopNumberOfRows(2) here
    GLEntry.SetRange(PostingDate, WorkDate());
    GLEntry.Open();
    while GLEntry.Read() do
        NumberOfRecords += 1;
    GLEntry.Close();
end;

query 50100 "G/L Entry"
{
    elements
    {
        dataitem(GLEntry; "G/L Entry")
        {
            column(EntryNo; "Entry No.") { }
            filter(PostingDate; "Posting Date") { }
        }
    }
}

Rec.Find('-') with Rec.Next()

Index scans are not slow operations, so COUNT merely accesses indexes to locate rows, and this is in fact extremely fast. Certainly faster than possible row retrievals that FIND('-') will definitely have to do. It takes far more time to locate top 50 rows the retrieve them, than it does to index-scan a much larger number of rows. But the key word here is "much larger". Because no matter how much faster scanning an individual row is than retrieving an individual row, FIND('-') will do only 50 retrievals, while COUNT will go on as long as there are rows.

Checking for exactly one record

if Rec.Count() = 1 then

Can be replaced by

if Rec.Find('-') and (Rec.Next() = 0) then

Checking for more than one record

if Rec.Count() > 1 then

Can be replaced by

if Rec.Find('-') and (Rec.Next() <> 0) then

Read more: