Performance Patterns for AL Code (Part 1)

In Business Central, performance optimization is crucial for ensuring a smooth and efficient user experience.

As developers, we have the power to significantly enhance performance through thoughtful AL programming.

In this blog series we will cover some of key strategies to consider, along with real-life code examples, starting from easier stuff in first parts and then moving on to advanced.

Avoid Unnecessary Data Retrieval

Only retrieve the data you need. Use the SETFILTER and SETRANGE methods to limit the data set.

Wrong:

SalesLine.SetRange("Document No.", '1001');
SalesLine.SetFilter("Line No.", '>1');

Correct:

SalesLine.SetRange("Document No.", '1001');

Unnecessary filter was applied for “Line No.”, since it’s always greater than 1.

Partial Records

Use partial records to load only the necessary fields, reducing the amount of data transferred and processed. You can accomplish that with SetLoadFields method.

Wrong:

Customer.FindSet();
repeat
   CountryCity := Customer."Country/Region Code" + '-' + Customer.City;
until Customer.Next() = 0;

Correct:

Customer.SetLoadFields("Country/Region Code", "City"); // Load only the necessary fields
Customer.FindSet();
repeat
   CountryCity := Customer."Country/Region Code" + '-' + Customer.City; // Use only loaded fields
until Customer.Next() = 0;

Let’s see next example:

Customer.SetLoadFields("Country/Region Code", "City"); // Load only the necessary fields
Customer.FindSet();
repeat
    Customer.Validate(City, Customer.City.Trim()); // Use only loaded fields
until Customer.Next() = 0;

Thanks to Erik Hougaard, Microsoft MVP, for bringing it up and explaining this one.

In the example above you would first say it is Correct. Actually validating “City” field, will also touch the “Post Code” field, meaning, the “fast” version is actually slower because it will trigger JIT loading of the record. “Validate” could be complicated in combination with SetLoadFields because it’s not known what code will be triggered, there could be event subscribers etc.

Use Indexed Fields

Ensure that fields used in filters and joins are indexed to speed up data retrieval. You can accomplish that with SetCurrentKey method.

Wrong:

CustLedgEntry.SetRange("Customer No.", Customer."No.");
CustLedgEntry.SetFilter("Posting Date", '>=%1', AccountingPeriod."Starting Date");
if CustLedgEntry.FindSet() then
    //Process the records

Correct:

CustLedgEntry.SetCurrentKey("Customer No.", "Posting Date");
CustLedgEntry.SetRange("Customer No.", Customer."No.");
CustLedgEntry.SetFilter("Posting Date", '>=%1', AccountingPeriod."Starting Date");
if CustLedgEntry.FindSet() then
    //Process the records

Temporary Table

Temporary tables can be used to store intermediate results, reducing the load on the database.

Wrong:

if RecordedEventBuffer.IsEmpty() then
    exit;
if RecordedEventBuffer.FindSet() then
    repeat
        Rec.Init();
        Rec := RecordedEventBuffer;
        Rec.Insert();
    until RecordedEventBuffer.Next() = 0;

Correct:

//Populate Temporary Table
PopulateRecordedEventBuffer(TempRecordedEventBuffer);
if TempRecordedEventBuffer.IsEmpty() then
    exit;
if TempRecordedEventBuffer.FindSet() then
    repeat
        Rec.Init();
        Rec := TempRecordedEventBuffer;
        Rec.Insert();
    until TempRecordedEventBuffer.Next() = 0;

Use Methods Instead of Looping

Built in methods for Modify and Delete can be more efficient than looping through records one by one. You can accomplish that by using ModifyAll and DeleteAll methods.

Wrong:

SalesLine.SetRange("Document Type", SalesHeader."Document Type");
SalesLine.SetRange("Document No.", SalesHeader."No.");
if SalesLine.FindFirst(true) then
   repeat
      SalesLine."Bill-to Customer No." := SalesHeader."Bill-to Customer No.";
      SalesLine.Modify();
   until SalesLine.Next() = 0;

Correct:

SalesLine.SetRange("Document Type", SalesHeader."Document Type");
SalesLine.SetRange("Document No.", SalesHeader."No.");
SalesLine.ModifyAll("Bill-to Customer No.", SalesHeader."Bill-to Customer No.");

Wrong:

SalesLine.SetRange("Document Type", SalesHeader."Document Type");
SalesLine.SetRange("Document No.", SalesHeader."No.");
if SalesLine.FindFirst() then
   repeat
      SalesLine.Delete();
   until SalesLine.Next() = 0;

Correct:

SalesLine.SetRange("Document Type", SalesHeader."Document Type");
SalesLine.SetRange("Document No.", SalesHeader."No.");
SalesLine.DeleteAll();

FlowFields Calculation

The SetAutoCalcFields method sets the specified FlowFields to be automatically calculated when the record is retrieved from the database. Using this method, It would reduce the number of database queries in this loop.

Wrong:

Item.FindSet();
repeat
    Item.CalcFields(Inventory);
    // Use calculated value of the field
until Item.Next() = 0;

Correct:

Item.SetAutoCalcFields(Inventory);
Item.FindSet();
repeat
    // Use calculated value of the field
until Item.Next() = 0;

Stay tuned for the next parts of this series, where we’ll dive deeper into more performance patterns for AL code. Follow along to learn more tips and tricks to enhance your Business Central development skills! 🚀