Chapter 13: Performance Optimization

Objectives

By the end of this chapter you will be able to:

• Explain why performance habits matter more in a multi-tenant SaaS system than they might in other software
• Filter records before looping instead of after
• Use SetLoadFields to avoid pulling columns you don’t need
• Recognize and avoid database calls inside a loop
• Apply performance best practices you can defend in a code review

13.1 Why performance habits matter

Business Central is a SaaS product. Your extension’s code runs alongside every other extension in the same tenant and shares the same database resources. A loop that’s “fine” on a test company with 50 records can time out on a customer’s production company with 500,000. So performance is something you build into the first draft as a habit, instead of leaving it for an optimization pass late in the project.

13.2 Filter before you loop

The most common mistake is to read every record and then check a condition in AL code, when you could have asked the database to filter first.

// Slow: reads every row, checks Paid in AL for each one
LoanRepaymentSchedule.FindSet();
repeat
    if not LoanRepaymentSchedule.Paid then
        // ...
until LoanRepaymentSchedule.Next() = 0;

// Fast: the database only returns unpaid rows
LoanRepaymentSchedule.SetRange(Paid, false);
if LoanRepaymentSchedule.FindSet() then
    repeat
        // ...
    until LoanRepaymentSchedule.Next() = 0;

Hint: SetRange/SetFilter always go before FindSet. Calling them afterward has no effect on a find that already ran.

13.3 SetLoadFields: only ask for what you use

By default, AL loads every field of a record, even if your code reads only two of them. On a wide table, that’s wasted work on every row.

LoanRepaymentSchedule.SetLoadFields("Loan No.", "Due Date", Paid);
LoanRepaymentSchedule.SetRange(Paid, false);
if LoanRepaymentSchedule.FindSet() then
    repeat
        // only "Loan No.", "Due Date", and Paid are populated here
    until LoanRepaymentSchedule.Next() = 0;

Hint: If you call SetLoadFields and then read a field you didn’t list, AL fetches it with a just-in-time load: another database read for that record. Inside a loop, that’s an extra read on every row, and it fails with an error if someone else changed or deleted the record in the meantime. List every field your loop body touches.

13.4 Don’t call the database inside a loop

A Get(), .Insert(), or another record’s FindSet() inside a loop multiplies your database round-trips by the number of iterations.

// Slow: one database read per installment, inside the loop
LoanRepaymentSchedule.SetRange("Loan No.", LoanNo);
if LoanRepaymentSchedule.FindSet() then
    repeat
        Loan.Get(LoanRepaymentSchedule."Loan No.");  // repeated, but Loan No. never changes here
        // ...
    until LoanRepaymentSchedule.Next() = 0;

// Fast: read the loan once, outside the loop
Loan.Get(LoanNo);
LoanRepaymentSchedule.SetRange("Loan No.", LoanNo);
if LoanRepaymentSchedule.FindSet() then
    repeat
        // Loan is already available here
    until LoanRepaymentSchedule.Next() = 0;

13.5 Best practices for performance

• Always filter first: call SetRange/SetFilter before FindSet, and never check a condition and skip rows inside the loop.
• Use SetLoadFields on any loop over a table with more than a handful of fields, especially in reports and background jobs over large tables.
• Hoist anything that stays constant across iterations out of the loop. A Get() that doesn’t depend on the loop variable doesn’t belong inside it.
• Before you call something “done,” test it against realistic data volumes, not just your five-row demo company.

Chapter summary

In this chapter you:

• Learned why SaaS multi-tenancy makes performance habits matter more
• Filtered with SetRange/SetFilter before looping, not after
• Used SetLoadFields to avoid loading unused columns
• Learned to hoist unchanging lookups out of a loop

Tasks

1. Filter GenerateSchedule’s caller. Write a procedure that loops over all unpaid installments across every loan and prints a count. Use SetRange(Paid, false) before FindSet, not an if inside the loop.
2. Add SetLoadFields. To the procedure from task 1, add SetLoadFields listing only the fields you actually read.
3. Find the hoist. Imagine a procedure that, for every installment, looks up the loan’s borrower name from a Loan header table. If the procedure only ever processes installments from a single loan at a time, where should that lookup happen — inside or outside the loop? Rewrite the pseudocode to put it in the right place.
4. Try it yourself. GenerateSchedule from Chapter 9 inserts records one at a time with Insert(true). For very large installment counts, would you change anything about how it writes records? What would you check before deciding that’s worth doing?

Check your work: compare your procedures against the reference solution in the Chapter 13 solutions.