Use Query or Find with Next instead of Count

Count() returns the total number of matching records by executing a SELECT COUNT(*) query. On a ledger or entry table with millions of rows, the query scans the full index — its cost grows linearly with row count.

When the check is whether the table contains exactly one record — if GLEntry.Count() = 1 — that full scan is unnecessary. Two approaches retrieve at most two rows instead of counting every match.

Example

procedure ProcessSingleEntry()
var
    GLEntry: Record "G/L Entry";
begin
    if GLEntry.Count() = 1 then // Use Query or Find with Next instead of Count [LC0082]
        ProcessEntry(GLEntry);
end;

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

Find('-') issues a SELECT TOP n query and positions the cursor on the first record. Next() advances within that batch without another database roundtrip. Checking whether Next() returns 0 answers “exactly one record?” by fetching at most two rows.

To check for exactly one record, replace Count() = 1 with Find('-') and (Next() = 0):

procedure ProcessSingleEntry()
var
    GLEntry: Record "G/L Entry";
begin
    if GLEntry.Find('-') and (GLEntry.Next() = 0) then
        ProcessEntry(GLEntry);
end;

To check for more than one record, replace Count() > 1 with Find('-') and (Next() <> 0):

procedure ProcessMultipleEntries()
var
    GLEntry: Record "G/L Entry";
begin
    if GLEntry.Find('-') and (GLEntry.Next() <> 0) then
        ProcessEntries(GLEntry);
end;

Query

For optimal performance, use a Query object with TopNumberOfRows(2). This limits the SQL result set to two rows — if fewer than two come back, the count is zero or one.

procedure ProcessSingleEntry()
var
    GLEntryQuery: Query "G/L Entry Query";
    NumberOfRecords: Integer;
begin
    GLEntryQuery.TopNumberOfRows(2);
    GLEntryQuery.Open();
    while GLEntryQuery.Read() do
        NumberOfRecords += 1;
    GLEntryQuery.Close();

    if NumberOfRecords = 1 then
        ProcessEntry();
end;

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

The Query approach requires a separate Query object per table, but generates the most efficient SQL.

When the diagnostic is reported

The diagnostic is raised when all of the following are true:

• Count() is compared against 1 — directly (= 1, <> 1, >= 1, <= 1, > 1) or via equivalent expressions (< 2, 2 > Count()).
• The record variable is not temporary.
• The table name contains one of: Ledger, GL, G/L, Posted, Pstd, Log, Entry, Archive — or the primary key includes a field named Entry No..

Tables not matching these name heuristics are assumed to stay small enough that a full count is acceptable.

Exception

The diagnostic is not raised for temporary records. Count() on a temporary record operates on an in-memory dataset, not the database, so the performance concern does not apply.

See also

• LC0081 - Use IsEmpty method instead of Count
• The “IF COUNT = 1” Conundrum by Vjeko