10 AL Performance Tips Every Business Central Developer Should Know (011–020)

Most Business Central performance problems are often caused by AL code that reads too many records, loads unnecessary fields, or makes more database calls than the process actually needs.

These 10 tips focus on the fundamentals that matter: how you filter records, what data you load, and when you can avoid looping altogether.

Whether you are optimizing an existing process or writing a new one, these techniques will help you write AL code that uses the database efficiently and continues to perform well as your Business Central environment grows.

Tip 011 – Use FindFirst When You Only Need One Record

Use FindFirst when you only need one record.

❌ Bad: FindSet() for a single lookup ✅ Good: FindFirst() — or Get() when you have the primary key

❌ Bad

ShipToAddress.SetRange("Customer No.", CustomerNo);
ShipToAddress.SetRange(Code, 'DEFAULT');
if ShipToAddress.FindSet() then
    exit(true);

✅ Good

ShipToAddress.SetRange("Customer No.", CustomerNo);
ShipToAddress.SetRange(Code, 'DEFAULT');
exit(ShipToAddress.FindFirst());

Why?

• ✅ The method name tells readers you expect one record, not a list.
• ✅ The database does less work than preparing a set you will never walk.
• ✅ Get() is even clearer when you already have the full primary key.

Rule of thumb: Get for a known key. FindFirst for one filtered record. FindSet only when you will loop.

Once you are asking for the right number of rows, the next cost is how wide those rows are.

Tip 012 – Use SetLoadFields to Reduce Record Loading

Use SetLoadFields to reduce record loading.

❌ Bad: Load all 50+ Customer fields to read two of them ✅ Good: Load only the fields the procedure actually reads

❌ Bad

Customer.SetFilter(Blocked, '<>%1', Customer.Blocked::" ");
if Customer.FindSet() then
    repeat
        BlockedCount += 1;
    until Customer.Next() = 0;

✅ Good

Customer.SetLoadFields("No.", Blocked);
Customer.SetFilter(Blocked, '<>%1', Customer.Blocked::" ");
if Customer.FindSet() then
    repeat
        BlockedCount += 1;
    until Customer.Next() = 0;

Call SetLoadFields before FindSet, FindFirst, or Get. Fields you omit come back as defaults ('', 0, false)—not as an error. Skip it when you are about to Validate() (triggers need extra fields) or when you genuinely need the full record.

Why?

• ✅ Less data moves from SQL on every row.
• ✅ Loops over Customer, Item, and Sales Line get faster as tables grow.
• ✅ The field list documents what the procedure depends on.

Rule of thumb: If the loop only reads three fields, load three fields.

Narrow rows still hurt if you Get a related table on every iteration.

Tip 013 – Avoid Database Calls Inside Loops

Avoid database calls inside loops.

❌ Bad: Item.Get on every sales line (N+1) ✅ Good: Pre-load related records into a temporary table

❌ Bad

if SalesLine.FindSet(true) then
    repeat
        Item.Get(SalesLine."No.");
        SalesLine.Validate("Unit Price", Item."Unit Price");
        SalesLine.Modify(true);
    until SalesLine.Next() = 0;

✅ Good

Item.SetLoadFields("No.", "Unit Price");
if Item.FindSet() then
    repeat
        TempItem := Item;
        TempItem.Insert();
    until Item.Next() = 0;
 
if SalesLine.FindSet(true) then
    repeat
        if TempItem.Get(SalesLine."No.") then begin
            SalesLine.Validate("Unit Price", TempItem."Unit Price");
            SalesLine.Modify(true);
        end;
    until SalesLine.Next() = 0;

Why?

• ✅ One scan of Item instead of one Get per line.
• ✅ Temporary tables are cheap to look up in memory.
• ✅ The pattern scales from tens of lines to thousands.

Rule of thumb: If the inner call does not depend on data you just changed, it does not belong inside the loop.

If the loop exists only to add a field up, you do not need a loop at all.

Tip 014 – Use CalcSums Instead of Summing in a Loop

Use CalcSums instead of summing in a loop.

❌ Bad: Walk every ledger entry in AL ✅ Good: Let SQL compute the SUM in one query

❌ Bad

if CustLedgerEntry.FindSet() then
    repeat
        Total += CustLedgerEntry.Amount;
    until CustLedgerEntry.Next() = 0;

✅ Good

CustLedgerEntry.SetRange("Customer No.", CustomerNo);
CustLedgerEntry.SetRange("Posting Date", FromDate, ToDate);
CustLedgerEntry.CalcSums(Amount);
Total := CustLedgerEntry.Amount;

You can total several fields in one call: CalcSums(Amount, "Remaining Amt. (LCY)"). Keep a loop only when each iteration has real per-record logic.

Why?

• ✅ SQL computes the sum without shipping every row to the NST.
• ✅ The procedure is obviously “this is a total.”
• ✅ Multiple fields can be summed in a single round trip.

Rule of thumb: If the loop body is only Total += Field, it should be CalcSums.

Filters only work if they exist before you find the records.

Tip 015 – Apply Filters Before You Find, Never After

Apply filters before you find, never after.

❌ Bad: SetRange after FindSet—the filter is ignored ✅ Good: Filter, then find

❌ Bad

if SalesHeader.FindSet() then begin
    SalesHeader.SetRange("Document Type", SalesHeader."Document Type"::Order);
    SalesHeader.SetRange(Status, SalesHeader.Status::Open);
    repeat
        SalesHeader.Validate(Status, SalesHeader.Status::Released);
        SalesHeader.Modify(true);
    until SalesHeader.Next() = 0;
end;

✅ Good

SalesHeader.SetRange("Document Type", SalesHeader."Document Type"::Order);
SalesHeader.SetRange(Status, SalesHeader.Status::Open);
if SalesHeader.FindSet(true) then
    repeat
        SalesHeader.Validate(Status, SalesHeader.Status::Released);
        SalesHeader.Modify(true);
    until SalesHeader.Next() = 0;

This is a correctness bug, not a style issue. The bad example can release quotes and invoices you never intended to touch.

Why?

• ✅ The database returns only the rows you meant to process.
• ✅ Indexes can be used because the filters exist at query time.
• ✅ You avoid a silent logic error that code review often misses.

Rule of thumb: If SetRange is below FindSet, the filter is too late.

A loop that finds the right rows can still leave the company in a mess if you commit after every iteration.

Tip 016 – Don't Commit Inside a Loop

Don't Commit() inside a loop.

❌ Bad: Each iteration is its own transaction ✅ Good: One business operation, one transaction

❌ Bad

if GenJournalLine.FindSet() then
    repeat
        GenJnlPostLine.RunWithCheck(GenJournalLine);
        Commit(); // This is a bad idea
    until GenJournalLine.Next() = 0;

✅ Good

if GenJournalLine.FindSet() then
    repeat
        GenJnlPostLine.RunWithCheck(GenJournalLine);
    until GenJournalLine.Next() = 0;

If you need restartable batches, design explicit batch records and a job queue—do not sprinkle Commit() through a loop “for performance.”

Why?

• ✅ An error rolls the whole batch back.
• ✅ Fewer locks escalate.
• ✅ You do not leave a “some lines posted” company.

Rule of thumb: If the loop is one business operation, it is one transaction.

Database cost is not only in jobs. List pages pay it on every refresh.

Tip 017 – Don't Put More Than Four FlowFields on a List Page

Don't put more than four FlowFields on a list page.

❌ Bad: Six ledger totals on every visible row ✅ Good: Keep the two or three totals people actually scan

❌ Bad

field("Balance (LCY)"; Rec."Balance (LCY)") { }
field("Balance Due (LCY)"; Rec."Balance Due (LCY)") { }
field("Sales (LCY)"; Rec."Sales (LCY)") { }
field("Payments (LCY)"; Rec."Payments (LCY)") { }
field("Outstanding Orders (LCY)"; Rec."Outstanding Orders (LCY)") { }
field("Shipped Not Invoiced (LCY)"; Rec."Shipped Not Invoiced (LCY)") { }

✅ Good

field("Balance (LCY)"; Rec."Balance (LCY)") { }
field("Balance Due (LCY)"; Rec."Balance Due (LCY)") { }
field("Sales (LCY)"; Rec."Sales (LCY)")
{
    Visible = false;
}

Put the rest on the Card, a FactBox, or a Query. This is a guideline, not a compiler limit: two cheap FlowFields are fine; six that each sum Cust. Ledger Entry are not.

Why?

• ✅ List pages stay responsive as the company grows.
• ✅ Users still get totals where they look for them.
• ✅ Heavy aggregations belong in queries and reports.

Rule of thumb: If you cannot name why a FlowField must be on the list, it belongs on the card.

Nested FindSet loops are how those aggregations sneak back into AL.

Tip 018 – Use a Query Instead of Nested Record Loops

Use a Query instead of nested record loops.

❌ Bad: Inner FindSet per customer ✅ Good: One SQL join with Method = Sum

❌ Bad

if Customer.FindSet() then
    repeat
        CustLedgerEntry.SetRange("Customer No.", Customer."No.");
        if CustLedgerEntry.FindSet() then
            repeat
                SalesLCY += CustLedgerEntry."Sales (LCY)";
            until CustLedgerEntry.Next() = 0;
    until Customer.Next() = 0;

✅ Good

query 50100 "CTX Customer Sales"
{
    elements
    {
        dataitem(Customer; Customer)
        {
            column(No; "No.") { }
            dataitem(Cust_Ledger_Entry; "Cust. Ledger Entry")
            {
                DataItemLink = "Customer No." = Customer."No.";
                SqlJoinType = LeftOuterJoin;
                column(SalesLCY; "Sales (LCY)")
                {
                    Method = Sum;
                }
            }
        }
    }
}

Use LeftOuterJoin when parents with zero children must still appear. Queries are read-only—if you must modify rows, compute with the Query, then Get the few records you will change.

Why?

• ✅ One SQL join instead of one inner query per customer.
• ✅ Aggregations happen in the database.
• ✅ The join lives in one reusable object.

Rule of thumb: Two nested FindSet loops over related tables is a Query waiting to be written.

When every record gets the same field change, you do not need AL to visit each row either.

Tip 019 – Use ModifyAll and DeleteAll Instead of Looping

Use ModifyAll and DeleteAll instead of looping.

❌ Bad: FindSet + Modify for the same value ✅ Good: One set-based statement

❌ Bad

if SalesHeader.FindSet(true) then
    repeat
        SalesHeader.Status := SalesHeader.Status::Released;
        SalesHeader.Modify();
    until SalesHeader.Next() = 0;

✅ Good

SalesHeader.SetRange("Document Type", SalesHeader."Document Type"::Quote);
SalesHeader.SetFilter("Document Date", '<%1', BeforeDate);
SalesHeader.ModifyAll(Status, SalesHeader.Status::Released, false);

The third parameter is RunTrigger. Use true when validation or subscribers must fire. ModifyAll cannot express per-record calculations (Amount := Quantity * Price)—those still need a loop.

Why?

• ✅ One statement instead of N reads and N writes.
• ✅ Less lock time than a long modify loop.
• ✅ The filter is the whole story.

Rule of thumb: Same new value for every filtered record → ModifyAll. Same delete criteria → DeleteAll.

The last bulk trap is numbering. GetNextNo is correct for one Card insert and expensive for ten thousand.

Tip 020 – Use No. Series Batch When Numbering Many Records

Use "No. Series - Batch" when numbering many records.

❌ Bad: GetNextNo inside a 10,000-row import ✅ Good: NoSeriesBatch.GetNextNo for bulk paths

❌ Bad

Vendor."No." := NoSeries.GetNextNo(PurchSetup."Vendor Nos.", WorkDate());
Vendor.Insert(true);

✅ Good

PurchSetup.TestField("Vendor Nos.");
Vendor."No." := NoSeriesBatch.GetNextNo(PurchSetup."Vendor Nos.", WorkDate());
Vendor.Insert(true);

Always TestField the series first. Do not switch everyday Card OnInsert numbering to the batch codeunit—the extra machinery is for volume.

Why?

• ✅ Bulk numbering does not lock the series on every call.
• ✅ Imports and migration jobs finish faster.
• ✅ Interactive inserts can keep using No. Series.

Rule of thumb: One record from the UI → No. Series. Hundreds from a job or import → "No. Series - Batch".

Conclusion

These ten tips are small on their own, but together they cut the round trips that make AL feel slow in production. Good AL performance is rarely about one optimization; it’s about consistently making the database do less work.