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 recordsCorrect
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 recordsTemporary 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! 🚀\
Related posts
2 comments
- Gert Lynge
A few comments:
“Unnecessary filter was applied for “Line No.”, since it’s always greater than 1.”
This is wrong – if you insert multiple lines above the first one, “Line No.” will get negative.
Not that the filter makes any sense anyway :-).if X.Count() = 0 then
exit;
…is not well-performing and will most likely lock the primary index of X.
Use this instead (reduces lock time and SQL server load):
if X.IsEmpty() = 0 then
exit;Note/warning:
ModifyAll and DeleteAll can be changed by the runtime to regular Modify/Delete if there are subscribers on the triggers. So this optimization can be dependent on other installed apps.- Stefan ŠošićAuthor
Thanks for pointing out that example, actually true 😀 Didn’t saw that one
For IsEmpty yeah, that is more correct way, wanted to show difference between running on normal record vs temporary. Exactly that, when running on normal record you will get performance downside a lot, but on temporary record it would be nearly equal, so though it would be great example.Thanks for the feedback Gert!