/skill-performance

name: skill-performance
description: "AL performance optimization patterns for Business Central. Use when optimizing queries with SetLoadFields, working with FlowFields and CalcFields, profiling codeunits, or resolving performance issues."

Skill: AL Performance Optimization

Purpose

Identify, analyze, and fix performance bottlenecks in AL code: inefficient queries, FlowField issues, loop anti-patterns, and data-volume problems in Business Central.

When to Load

This skill should be loaded when:

• A page, report, or batch process is slow or timing out
• CPU profiling is needed to identify hotspots
• A static triage of the codebase is requested for performance issues
• A feature involves large-dataset processing or high-frequency code paths
• AL0896 (circular FlowField) errors appear
• Architecture review requires performance analysis of a new design

Core Patterns

For BC29 query or isolated-storage concurrency work, read the query/storage sections of [AL18 capability checks](../skill-migrate/references/al18-capabilities.md). Confirm supported declarations and test transaction behavior before changing isolation; `DataScope` and transaction isolation are different concepts.

Pattern 1: SetLoadFields + Early Filtering

Always filter before finding, and load only needed fields. Order matters.

// ✅ Correct: SetRange first, SetLoadFields before Find
Item.SetRange("Third Party Item Exists", false);
Item.SetLoadFields("Item Category Code", Description);
if Item.FindSet() then
    repeat
        // Only "Item Category Code" and Description loaded from DB
    until Item.Next() = 0;

// ❌ Wrong: SetLoadFields after SetRange (ignored), loads all fields
Item.SetLoadFields("Item Category Code");
Item.SetRange("Third Party Item Exists", false);
Item.FindFirst();

// ❌ Wrong: No filter — full table scan
procedure GetCustomersByCity(CityFilter: Text): Integer
var
    Customer: Record Customer;
    Count: Integer;
begin
    if Customer.FindSet() then       // loads entire Customer table
        repeat
            if Customer.City = CityFilter then
                Count += 1;
        until Customer.Next() = 0;
end;

// ✅ Correct: Filter pushed to DB
procedure GetCustomersByCity(CityFilter: Text): Integer
var
    Customer: Record Customer;
begin
    Customer.SetRange(City, CityFilter);
    Customer.SetRange(Blocked, Customer.Blocked::" ");
    exit(Customer.Count());
end;

Pattern 2: Set-Based Aggregation (CalcSums / CalcFields)

Avoid manual loops for aggregation — push the sum to the database.

// ❌ Loop accumulation — N rows fetched and processed in AL
procedure GetTotalSales(CustomerNo: Code[20]): Decimal
var
    Entry: Record "Cust. Ledger Entry";
    Total: Decimal;
begin
    Entry.SetRange("Customer No.", CustomerNo);
    if Entry.FindSet() then
        repeat
            Total += Entry.Amount;
        until Entry.Next() = 0;
    exit(Total);
end;

// ✅ CalcSums — single aggregation query at DB level
procedure GetTotalSales(CustomerNo: Code[20]): Decimal
var
    Entry: Record "Cust. Ledger Entry";
begin
    Entry.SetRange("Customer No.", CustomerNo);
    Entry.CalcSums(Amount);
    exit(Entry.Amount);
end;

For FlowFields accessed outside a page context, always call `CalcFields` before reading:

Customer.SetLoadFields("Balance (LCY)");
Customer.Get(CustomerNo);
Customer.CalcFields("Balance (LCY)");   // required — not auto-calculated in code

Pattern 3: Temporary Tables / Dictionary / List

Pre-load data once, then process in-memory multiple times.

// ✅ Temporary table — structured record data, multi-pass processing
procedure ProcessSalesData(var TempSalesLine: Record "Sales Line" temporary)
var
    SalesLine: Record "Sales Line";
begin
    SalesLine.SetLoadFields("No.", Quantity, "Unit Price", Amount);
    if SalesLine.FindSet() then
        repeat
            TempSalesLine := SalesLine;
            TempSalesLine.Insert();
        until SalesLine.Next() = 0;

    // Process in-memory — zero additional DB hits
    ApplyDiscounts(TempSalesLine);
    CalculateTotals(TempSalesLine);
end;

// ✅ Dictionary — key-value lookup cache
procedure CacheCustomerNames(): Dictionary of [Code[20], Text]
var
    Customer: Record Customer;
    Cache: Dictionary of [Code[20], Text];
begin
    Customer.SetLoadFields("No.", Name);
    if Customer.FindSet() then
        repeat
            Cache.Add(Customer."No.", Customer.Name);
        until Customer.Next() = 0;
    exit(Cache);
end;

// ✅ List — simple value collection
procedure GetBlockedCustomerNos(): List of [Code[20]]
var
    Customer: Record Customer;
    Result: List of [Code[20]];
begin
    Customer.SetRange(Blocked, Customer.Blocked::All);
    Customer.SetLoadFields("No.");
    if Customer.FindSet() then
        repeat
            Result.Add(Customer."No.");
        until Customer.Next() = 0;
    exit(Result);
end;

Pattern 4: Batch Processing and Avoiding Nested DB Calls

Move database operations outside loops; batch writes.

// ❌ Nested DB call inside loop — O(n²) database hits
if MainTable.FindSet() then
    repeat
        OtherTable.SetRange(Field, MainTable.Field);
        if OtherTable.FindSet() then          // DB call per iteration
            repeat
                // process
            until OtherTable.Next() = 0;
    until MainTable.Next() = 0;

// ✅ Pre-load into temp table, then join in memory
if OtherTable.FindSet() then
    repeat
        TempOther := OtherTable;
        TempOther.Insert();
    until OtherTable.Next() = 0;

if MainTable.FindSet() then
    repeat
        if TempOther.Get(MainTable.Field) then
            // process — no DB hit
    until MainTable.Next() = 0;

Batch writes — collect changes, write once:

// ✅ Calculate all values first, then single Modify
procedure UpdateCustomerStats(CustomerNo: Code[20])
var
    Customer: Record Customer;
    TotalBalance: Decimal;
    LastPaymentDate: Date;
begin
    CalculateCustomerTotals(CustomerNo,