Business central blog
Subscribe to blog and get news about new posts.
How to group data in AL? Best practicies. Performance review.
Data manipulation is one of the most trivial things for Navision/Business Central Developer. We are constantly working with data in tables, adding, deleting, modifying, and so on. Today we're going to discuss data grouping. What does it mean to group data? How do you do it in AL? What is the best and fastest way to do it? I hope we can find answers to all these questions.
Agenda
Wikipedia has a rather succinct definition for grouped data:
“
Grouped data are data formed by aggregating individual observations of a variable into groups, so that a frequency distribution of these groups serves as a convenient means of summarizing or analyzing the data. There are two major types of grouping: data binning of a single-dimensional variable, replacing individual numbers by counts in bins; and grouping multi-dimensional variables by some of the dimensions (especially by independent variables), obtaining the distribution of ungrouped dimensions (especially the dependent variables).
To understand what "grouping data" means, let's look at a simple example of grouping data. Suppose we have a "Customer" table with several records. Our task is to group this data by "Location Code" to calculate the amount for each of the locations in the customer list.
In this case, the result of grouping the data will be as follows:
It is important to note that grouping data is not always necessary to calculate something. Sometimes you just need to get that data for another process, sometimes you just need to go through the values in a group. I will use Amount fields in my examples just for clarity because such data is easier to understand. In addition, your data must be sorted before grouping. I will use the built-in function Record.SetCurrentKey() for this.
I would like to point out that the methods I have described are not an exhaustive list of possible ways to group data. These are just the basic and generic approaches that I am aware of. Sometimes it is enough to use Dictionary, sometimes something else, each case should be considered separately.
Using the tempo variable of the same record we have the ability to group the data, this is an easy and simple way to do it. The general scheme of this method looks something like this:
procedure TempRecordGroupMethod()
var
RecordToGroup: Record "RecordToGroup";
TempRecordToGroupResult: Record "RecordToGroup" temporary;
GroupNo: Integer;
begin
RecordToGroup.SetCurrentKey(GroupField1, GroupField2, ..., GroupField999);
if RecordToGroup.FindSet() then
repeat
//Check if group is exist
TempRecordToGroupResult.SetRange(GroupField1, RecordToGroup.GroupField1);
TempRecordToGroupResult.SetRange(GroupField2, RecordToGroup.GroupField2);
...
TempRecordToGroupResult.SetRange(GroupField999, RecordToGroup.GroupField999);
if not TempRecordToGroupResult.FindFirst() then begin
//New group record initialization
GroupNo += 1;
TempRecordToGroupResult := RecordToGroup;
TempRecordToGroupResult.Insert();
end else begin
//Continuation of the group
//here you can update values for group, maybe sum some amounts, etc.
TempRecordToGroupResult.Amount += RecordToGroup.Amount;
TempRecordToGroupResult.Modify();
end;
until RecordToGroup.Next() = 0;
end;
Let's take a more realistic example, let's say we want to group table 37 "Sales Line" by the fields "Sell-to Customer No.", "Type" and "No.". In this case, the Temporary Record method code could look like this:
procedure SalesLineTempRecordGroupMethod()
var
SalesLine: Record "Sales Line";
TempSalesLineResult: Record "Sales Line" temporary;
GroupNo: Integer;
begin
SalesLine.SetCurrentKey("Sell-to Customer No.", Type, "No.");
if SalesLine.FindSet() then
repeat
//Check if group is exist
TempSalesLineResult.SetRange("Sell-to Customer No.", SalesLine."Sell-to Customer No.");
TempSalesLineResult.SetRange(Type, SalesLine.Type);
TempSalesLineResult.SetRange("No.", SalesLine."No.");
if not TempSalesLineResult.FindFirst() then begin
//New group record initialization
GroupNo += 1;
TempSalesLineResult := SalesLine;
TempSalesLineResult.Insert();
end else begin
//Continuation of the group
//here you can update values for group, maybe sum some amounts, etc.
TempSalesLineResult.Amount += SalesLine.Amount;
TempSalesLineResult.Modify();
end;
until SalesLine.Next() = 0;
end;
Prons and Cons
+
Easy to understand
+
Flexible to use, data can be grouped by any field
-
Poor performance
-
You have to go through each record in the table with Next() method
-
Additional Insert/FindFirst in temporary record(faster than real record)
We can also group data using Query objects. This method has the best performance and important SQL optimizations. But it is quite inconvenient and difficult to use. Grouping as a whole depends on the Query object we create. A general schema for Query:
query 50000 "Query Group Method"
{
Caption = 'Query Group Method';
QueryType = Normal;
OrderBy = ascending(FieldToGroup1, FieldToGroup2, ... ,FieldToGroup999);
elements
{
dataitem(RecordToGroup; "RecordToGroup")
{
column(FieldToGroup1; FieldToGroup1)
{
}
column(FieldToGroup2; FieldToGroup2)
{
}
...
column(FieldToGroup999; FieldToGroup999)
{
}
column(Amount; Amount)
{
Method = Sum;
}
}
}
}
procedure QueryMethod()
var
QueryGroupMethod: Query "Query Group Method";
DictOfGroup: Dictionary of [Integer, Decimal];
GroupNo: Integer;
begin
QueryGroupMethod.Open();
while QueryGroupMethod.Read() do begin
//New group per read, store sum of amount in dictionary
GroupNo += 1;
DictOfGroup.Add(GroupNo, QueryGroupMethod.Amount);
end;
end;
Same example as from previous variable method we want to group table 37 "Sales Line" by the fields "Sell-to Customer No.", "Type" and "No.":
query 50000 "Sales Line Group Method"
{
Caption = 'Sales Line Group Method';
QueryType = Normal;
OrderBy = ascending(SelltoCustomerNo, "Type", No);
elements
{
dataitem(SalesLine; "Sales Line")
{
column(SelltoCustomerNo; "Sell-to Customer No.")
{
}
column("Type"; "Type")
{
}
column(No; "No.")
{
}
column(Amount; Amount)
{
Method = Sum;
}
}
}
}
procedure SalesLineQueryGroupMethod()
var
SalesLineGroupMethod: Query "DAT Sales Line Group Method";
DictOfGroup: Dictionary of [Integer, Decimal];
GroupNo: Integer;
begin
SalesLineGroupMethod.Open();
while SalesLineGroupMethod.Read() do begin
//New group per read, store sum of amount in dictionary
GroupNo += 1;
DictOfGroup.Add(GroupNo, SalesLineGroupMethod.Amount);
end;
end;
Prons and Cons
+
Easy to understand
+
Best performance
-
Not flexible, you have to use predefined grouping of fields
-
Require additional object in DB per one grouping
My favorite method for data grouping. The first time I learned about it was from my fellow developer Viktor Kravchenko. The point of this method is that we don't need to go through each record, we can filter the group and do only one Next() for the whole group. Schema for filter method:
procedure FilterMethod()
var
RecordToGroup: Record RecordToGroup;
ResultGroupedRecord: Record ResultGroupedRecord temporary;
GroupFilter1, GroupFilter2, ... , GroupFilter999: text;
GroupNo: integer;
begin
//Get default filters (list of fields from grouping)
GroupFilter1 := RecordToGroup.GetFilter("FieldToGroup1");
GroupFilter2 := RecordToGroup.GetFilter("FieldToGroup2");
...
GroupFilter999 := RecordToGroup.GetFilter("FieldToGroup999");
RecordToGroup.SetCurrentKey(FieldToGroup1, FieldToGroup2, ...., FieldToGroup999);
if RecordToGroup.FindSet() then
repeat
//Set current group filters
RecordToGroup.SetRange(FieldToGroup1, RecordToGroup.FieldToGroup1);
RecordToGroup.SetRange(FieldToGroup2, RecordToGroup.FieldToGroup2);
...
RecordToGroup.SetRange(FieldToGroup999, RecordToGroup.FieldToGroup999);
//Go to last record of group
RecordToGroup.FindLast();
//New group, calc amount and put it to dictionary
GroupNo += 1;
RecordToGroup.CalcSums(Amount);
DictOfGroup.Add(GroupNo, RecordToGroup.Amount);
//Set default filters
RecordToGroup.SetFilter(FieldToGroup1, GroupFilter1);
RecordToGroup.SetFilter(FieldToGroup2, GroupFilter2);
...
RecordToGroup.SetFilter(FieldToGroup999, GroupFilter999);
until RecordToGroup.Next() = 0;
end;
If we need to group the data and at the same time go through each value, it is also very easy. But because we have to go through every record, it will be slower:
procedure FilterMethod()
var
RecordToGroup: Record RecordToGroup;
ResultGroupedRecord: Record ResultGroupedRecord temporary;
GroupFilter1, GroupFilter2, ... , GroupFilter999: text;
GroupNo: integer;
begin
//Get default filters (list of fields from grouping)
GroupFilter1 := RecordToGroup.GetFilter("FieldToGroup1");
GroupFilter2 := RecordToGroup.GetFilter("FieldToGroup2");
...
GroupFilter999 := RecordToGroup.GetFilter("FieldToGroup999");
RecordToGroup.SetCurrentKey(FieldToGroup1, FieldToGroup2, ...., FieldToGroup999);
if RecordToGroup.FindSet() then
repeat
//Set current group filters
RecordToGroup.SetRange(FieldToGroup1, RecordToGroup.FieldToGroup1);
RecordToGroup.SetRange(FieldToGroup2, RecordToGroup.FieldToGroup2);
...
RecordToGroup.SetRange(FieldToGroup999, RecordToGroup.FieldToGroup999);
//Go through each record
repeat
RecordToGroup.Next() = 0;
//New group, calc amount and put it to dictionary
GroupNo += 1;
RecordToGroup.CalcSums(Amount);
DictOfGroup.Add(GroupNo, RecordToGroup.Amount);
//Set default filters
RecordToGroup.SetFilter(FieldToGroup1, GroupFilter1);
RecordToGroup.SetFilter(FieldToGroup2, GroupFilter2);
...
RecordToGroup.SetFilter(FieldToGroup999, GroupFilter999);
until RecordToGroup.Next() = 0;
end;
To group table 37 "Sales Line" by the fields "Sell-to Customer No.", "Type" and "No.":
Important: be careful with CalcSums(), do not call this method without the necessary key on the table.
procedure SalesLineFilterMethod()
var
SalesLine: Record "Sales Line";
GroupNo: Integer;
FieldFilter1, FieldFilter2, FieldFilter3 : Text;
DictOfGroup: Dictionary of [Integer, Decimal];
begin
//Get default filters
FieldFilter1 := SalesLine.GetFilter("Sell-to Customer No.");
FieldFilter2 := SalesLine.GetFilter(Type);
FieldFilter3 := SalesLine.GetFilter("No.");
SalesLine.SetCurrentKey("Sell-to Customer No.", Type, "No.");
if SalesLine.FindSet() then
repeat
//Set current group filters
SalesLine.SetRange("Sell-to Customer No.", SalesLine."Sell-to Customer No.");
SalesLine.SetRange(Type, SalesLine.Type);
SalesLine.SetRange("No.", SalesLine."No.");
//Go to last record of group
SalesLine.FindLast();
//New group, calc amount and put it to dictionary
SalesLine.CalcSums(Amount);
GroupNo += 1;
DictOfGroup.Add(GroupNo, SalesLine.Amount);
//Set default filters
SalesLine.SetFilter("Sell-to Customer No.", FieldFilter1);
SalesLine.SetFilter(Type, FieldFilter2);
SalesLine.SetFilter("No.", FieldFilter3);
until SalesLine.Next() = 0;
end;
Prons and Cons
+
Good performance
+
Flexible to use, data can be grouped by any field
-
Need to create variables according to the number of fields for grouping
Let's move on to performance measurements, in my example, I used a new table with a PrimaryKey which consists of two fields: "Document Type" enum "Sales Document Type" and "Document No." code[20]. In addition, the table contains the following fields: "Group No." Integer, "Customer Name" text[100], Amount Decimal.
I also created a page on which you can make tests. The data generation code looks like this:
I will run my tests for 750,000 records in our table, grouping the data by "Document Type" and "Customer Name". Since I randomly generate these values between 1 and 5, I expect the number of groups to be 5*5=25. So, if running my tests in their current form, the result is this:
This is very strange, with so many entries and only 25 groups, the difference between Filter vs. TempRecord is negligible. And the answer is very simple, I used CalcSums(Amount) in Filter method code.
But use CalcSums() very carefully, the ideal is to add an additional key with SumIndexFields for the field we are summing up:
key(GroupKey; "Document Type", "Customer Name")
{
SumIndexFields = Amount;
}
After I have added the secondary key, we can do a final test of the methods.
Yep, query method is faster than the measurement error.
Important: all performance measurements are rather relative, they depend on many factors: the number of fields in the table, what keys are available, server capacity, by which fields we group and so on.
Summary
In this article, we explored some of the ways to group data and examined the performance of these examples. Data grouping is one of the most powerful methods for solving problems, a good developer knows how to manage data. I hope this information will be useful for AL Developers.
APRIL 01, 2022
Author: Volodymyr Dvernytskyi