Performance Patterns for AL Code (Part 2)

Welcome back to the series on performance patterns in AL programming! 🎊

In this post, we’ll delve deeper into the complexities of determining performance in Business Central. As many of you, have pointed out, performance optimization is a multifaceted topic influenced by a variety of factors. Each situation is unique, and the best solution often requires a combination of experience, investigation, and understanding of specific patterns.

One of the challenges in measuring performance is the variability of results across different environments. For instance, measurements taken in a cloud environment can yield mixed results, making it difficult to isolate true performance impacts. This is why, in our previous blog post, I didn’t include specific measurements. Instead, my goal is to introduce the patterns and then dive deeper into analysis and query performance in future posts.

Calculating sum efficiently

By using CalcSums , you can efficiently aggregate data and perform calculations without putting unnecessary load on the database. This approach helps in maintaining performance and ensuring that your Business Central solutions run smoothly.

Wrong:

GLAccount.SetRange("Account Category", GLAccount."Account Category"::Assets);
GLAccount.SetRange("Account Type", GLAccount."Account Type"::Posting);
GLAccount.SetFilter("Account Subcategory Entry No.", CreateFilterForGLAccSubCategoryEntries(GLAccCategory."Additional Report Definition"::"Cash Accounts"));
GLEntries.SetFilter("G/L Account No.", CreateFilterForGLAccounts(GLAccount));
GLEntries.FindSet();
repeat
   CashAccountBalance += GLEntries.Amount;
until GLEntries.Next() = 0;

Correct:

GLAccount.SetRange("Account Category", GLAccount."Account Category"::Assets);
GLAccount.SetRange("Account Type", GLAccount."Account Type"::Posting);
GLAccount.SetFilter("Account Subcategory Entry No.", CreateFilterForGLAccSubCategoryEntries(GLAccCategory."Additional Report Definition"::"Cash Accounts"));
GLEntries.SetFilter("G/L Account No.", CreateFilterForGLAccounts(GLAccount));
GLEntries.CalcSums(Amount);
CashAccountBalance := GLEntries.Amount;

Avoid unnecessary operations when no record

The IsEmpty method in AL programming is used to determine whether a table or a filtered set of records is empty. This method can be particularly useful for performance optimization by avoiding unnecessary operations when no records are present.

Let’s for example image if you have system where most Items have Unit Price greater than 100 euros.

Wrong:

Item.SetFilter("Unit Price", '<%1', 100);
Item.FindSet();

Correct:

Item.SetFilter("Unit Price", '<%1', 100);
if Item.IsEmpty() then
   exit;
Item.FindSet();

Not always, is use of IsEmpty faster!

You should think about your process which you are optimizing. If you have more chances that incoming record will be empty than it’s good idea to check first if it’s empty and you will indeed get better results. Otherwise you will be using FindSet as check if record exist which would be pretty bad for performance. But it’s likewise bad to use IsEmpty when it’s more chances that records are existing, since than you will query twice the database.

Wrong:

SalesLine.SetRange("Document Type", SalesHeader."Document Type");
SalesLine.SetRange("Document No.", SalesHeader."Document No.");
if not SalesLine.IsEmpty() then
   exit;
SalesLine.FindSet();

Must not be a case, but when Sales Header is existing in most cases, we are speaking about document which should have also lines. So, we expect in most cases to have document lines. If we do here IsEmpty check it will actually slow it down.

Use page background processing with heavy processing

Background processing on pages in Business Central allows you to run processes in the background while the user continues to interact with the page. This approach improves the user experience by preventing the UI from freezing during long-running operations.

Wrong:

...
field(complexField;complexField)
{
   ApplicationArea = All;
   Caption = 'Complex Field';
}
...
trigger OnAfterGetCurrRecord()
var
   HelperCU: Codeunit "PTE Helper Codeunit";
begin
   HelperCU.DoComplexCalculation(complexField);
end;

Correct:

...
field(complexField;complexField)
{
   ApplicationArea = All;
   Caption = 'Complex Field';
}
...
trigger OnAfterGetCurrRecord()
var
   //Defines a variable for passing parameters to the background task
   TaskParameters: Dictionary of [Text, Text];
begin
   CurrPage.EnqueueBackgroundTask(WaitTaskId, Codeunit::DoComplexCalculation, TaskParameters, 1000, PageBackgroundTaskErrorLevel::Warning);
end;

Procedure record parameter optimization

In AL programming, passing only the necessary parameters to a procedure instead of the entire record can significantly improve performance. When you pass a record without the VAR keyword, a new instance of the record is created in memory. Using VAR or passing only the needed fields can optimize memory usage and speed up your code, due transferred by reference and not by value.

Wrong:

begin
    NoOfOrders := CountSalesOrdersByCustomerFilter(Customer);
end;

local procedure CountSalesOrdersByCustomerFilter(Customer: Record Customer) : Integer
var
    SalesHeader: Record "Sales Header";
begin
    SalesHeader.SetRange("Document Type", SalesHeader."Document Type"::Order);
    SalesHeader.SetRange("Sell-to Customer No.", Customer."No.");
    exit(SalesHeader.Count());
end;

Efficient but in this case, I wouldn’t use it like this when it’s only one parameter needed:

begin
    NoOfOrders := CountSalesOrdersByCustomerFilter(Customer);
end;

local procedure CountSalesOrdersByCustomerFilter(var Customer: Record Customer) : Integer
var
    SalesHeader: Record "Sales Header";
begin
    SalesHeader.SetRange("Document Type", SalesHeader."Document Type"::Order);
    SalesHeader.SetRange("Sell-to Customer No.", Customer."No.");
    exit(SalesHeader.Count());
end;

Correct:

begin
    NoOfOrders := CountSalesOrdersByCustomerFilter(Customer."No.");
end;

local procedure CountSalesOrdersByCustomerFilter(CustomerNo: Code[20]) : Integer
var
    SalesHeader: Record "Sales Header";
begin
    SalesHeader.SetRange("Document Type", SalesHeader."Document Type"::Order);
    SalesHeader.SetRange("Sell-to Customer No.", CustomerNo);
    exit(SalesHeader.Count());
end;

Your feedback is invaluable as we navigate this complex topic. It helps refine my approach and examples, ensuring that I provide the most relevant and practical insights. As we continue this series, we’ll explore more patterns and their applications, aiming to equip you with the knowledge to make informed decisions in your development work.

Stay tuned for more detailed analyses and practical examples in the upcoming parts of this series. Let’s continue this journey together and see where it leads us. Thank you for your continued support and engagement! 🚀