Coding for Performance – Power of Memory – PART 1
We all know it: you get a task to develop a report, you develop it, you test it on your dev environment and everything looks good. Numbers are correct and the report only needs few seconds to be rendered. Its time to bring this development to production!
You share with your customer/user that you deployed a new fancy report. They try it out – and wait and wait and wait, after a while the report open and the user can see the right numbers. What happened? The production system got much more data and the execution takes much longer than on your dev environment.
Does this match the quality level you want to deliver? Do you want to waste the time of your users?
This happens not only in your custom developments. Some of the standard reports are programmed really bad. Let us take a look on such a beautiful standard report: 109 – Customer – Summary Aging Simp.
First we should analyze what it does:
report 109 "Customer - Summary Aging Simp."
{
dataset
{
dataitem(Customer; Customer)
{
// ....
trigger OnAfterGetRecord()
var
DtldCustLedgEntry: Record "Detailed Cust. Ledg. Entry";
PrintCust: Boolean;
i: Integer;
begin
PrintCust := FALSE;
FOR i := 1 TO 5 DO BEGIN
DtldCustLedgEntry.SetCurrentKey("Customer No.","Initial Entry Due Date","Posting Date");
DtldCustLedgEntry.SetRange("Customer No.","No.");
DtldCustLedgEntry.SetRange("Posting Date",0D,StartDate);
DtldCustLedgEntry.SetRange("Initial Entry Due Date",PeriodStartDate[i],PeriodStartDate[i + 1] - 1);
DtldCustLedgEntry.CalcSums("Amount (LCY)");
CustBalanceDueLCY[i] := DtldCustLedgEntry."Amount (LCY)";
IF CustBalanceDueLCY[i] <> 0 THEN
PrintCust := TRUE;
END;
IF NOT PrintCust THEN
CurrReport.SKIP;
end;
}
}
// ....
}
>> See complete Code here <<
For EVERY customer, we check 5 different period-ranges for a cumulated amount which is not 0. If the amount in all 5 periods are 0 we will skip this customer otherwise we will pass the amount + customer into the layout.
What does this mean for the performance? We have: 1 + customer.count() * 5 database calls
This is not a problem if we only have 100 customers, because the database can handle this 501 calls pretty quick. But what happens if we have a B2C business and 100.000 customers? Then we would have for the exact same report 500.001 database calls. Another issue with this kind of development is, if the user decide he wants to have an additional period, it will increase the database calls by customer.count().
What can and should we do?
Our first goal should be reducing the number of database calls and don’t let them scale up together with the number of customers.
var
DtldCustLedgEntry: Record "Detailed Cust. Ledg. Entry";
CustomerAmountList: Dictionary of [Code[20], Dictionary of [Integer, Decimal]];
CustomerPeriodList: Dictionary of [Integer, Decimal];
Period: Integer;
CumulatedAmount: Decimal;
begin
if not DtldCustLedgEntry.FindSet(false, false) then
exit;
repeat
Period := GetPeriodFromDate(DtldCustLedgEntry."Posting Date");
CumulatedAmount := 0;
Clear(CustomerPeriodList);
if CustomerAmountList.Get(DtldCustLedgEntry."Customer No.", CustomerPeriodList) then
if CustomerPeriodList.Get(Period, CumulatedAmount) then ;
CustomerPeriodList.Set(Period, CumulatedAmount + DtldCustLedgEntry."Amount (LCY)");
CustomerAmountList.Set(DtldCustLedgEntry."Customer No.", CustomerPeriodList);
until DtldCustLedgEntry.Next() = 0;
end;
This piece of code will produce a dictionary of all customers where we have a posting in at least 1 period.
{
"CUSTOMER-1": {
"1": 100,
"2": -100
},
"CUSTOMER-2": {
"4": 100,
"5": -300
}
}
How can we use this data now?
report 50000 "CUSTOM - Customer - Aging Simp."
{
dataset
{
dataitem(Customer; Customer)
{
// ....
trigger OnPreDataItem()
var
DtldCustLedgEntry: Record "Detailed Cust. Ledg. Entry";
CustomerPeriodList: Dictionary of [Integer, Decimal];
Period: Integer;
CumulatedAmount: Decimal;
begin
if not DtldCustLedgEntry.FindSet(false, false) then
exit;
repeat
Period := GetPeriodFromDate(DtldCustLedgEntry."Posting Date");
CumulatedAmount := 0;
Clear(CustomerPeriodList);
if CustomerAmountList.Get(DtldCustLedgEntry."Customer No.", CustomerPeriodList) then
if CustomerPeriodList.Get(Period, CumulatedAmount) then ;
CustomerPeriodList.Set(Period, CumulatedAmount + DtldCustLedgEntry."Amount (LCY)");
CustomerAmountList.Set(DtldCustLedgEntry."Customer No.", CustomerPeriodList);
until DtldCustLedgEntry.Next() = 0;
end;
trigger OnAfterGetRecord()
var
CustomerPeriodList: Dictionary of [Integer, Decimal];
PrintCust: Boolean;
i: Integer;
begin
if not CustomerAmountList.Get(Customer."No.", CustomerPeriodList) then
CurrReport.Skip(); // Customer does not have any postings
PrintCust := false;
for i := 1 to 5 do begin
CustBalanceDueLCY[i] := 0;
if CustomerPeriodList.Get(i, CustBalanceDueLCY[i]) then
if CustBalanceDueLCY[i] <> 0 then
PrintCust := true;
end;
if not PrintCust then
CurrReport.Skip();
end;
}
}
// ....
var
// ....
CustomerAmountList: Dictionary of [Code[20], Dictionary of [Integer, Decimal]];
}
>> See complete Code here <<
Let us run the report on my local docker and compare:
My local Docker contains: 144314 Customers and 432808 Detailed Customer Ledger Entries
Original Report 109 runs: 9 minutes 26 seconds vs Modified Report 109 runs: 34 seconds
Just with this small change we can reduce the execution time in large databases dramatically. We reduced the amount of database calls from 1 + customer.count() * 5 database calls to exactly 2. We can also adjust the number of periods without changing the performance of the report.
Just try it!
In next episode we will check how we can use this knowledge in C/AL and how we can save more than one value per period without having a dictionary of dictionary ….. of dictonary.
3 Comments