In two of my previous blog posts I reviewed some pros and cons of the SumIndexField Technology (or SIFT) in Business Central.
On the one hand, SIFT views are beyond doubt the fastest way to obtain aggregated values in BC. On the other hand, they hinder updates and can be the cause of deadlocks in case of multiple parallel updates. If SIFTs are so disruptive for update queries, should we look for alternatives? And what are the alternatives, anyway?
One possible solution is the covering index, which offers a good compromise between performance of aggregations and update queries. Covering index is an index that satisfies the query without the need for row data lookup. All data necessary for the query can be obtained from the index itself.
In this post I will not be going deep into details of what a covering index is and how to define the best index, but rather want to compare performance of queries based on a SIFT view vs the same queries with a covering index and no SIFT.
Test Data
The table that I use for the test is the same one I ran my previous tests against. It's a table with some fictitious sales data that includes Customer No., Item No., Date, and Amount. There are also customer group code and item category code, but these two fields are not involved in the following tests, so I ignore them.
table 50501 "My Sales Table"
{
DataClassification = CustomerContent;
fields
{
field(1; "Entry No."; Integer)
{
Caption = 'Entry No.';
}
field(2; "Customer No."; Code[20])
{
Caption = 'Customer No.';
}
field(3; "Item No."; Code[20])
{
Caption = 'Item No.';
}
field(4; "Item Category Code"; Code[20])
{
Caption = 'Item Category Code';
}
field(5; "Customer Group Code"; Code[20])
{
Caption = 'Customer Group Code';
}
field(6; "Posting Date"; Date)
{
Caption = 'Posting Date';
}
field(7; Amount; Decimal)
{
Caption = 'Amount';
}
}
keys
{
key(PK; "Entry No.")
{
Clustered = true;
}
}
}Test data are prepared in a way that there are exactly 100 records for each combination of Customer, Item, and Date. And the result that I want to receive is the aggregated amounts per customer and item in a certain date range.
Test data cover date range from 01.01.2024 to 19.02.2024.
Test
This is the procedure I run to produce the result and measure time.
procedure CalcAmountByDimensionCalcSums()
var
MySalesTable: Record "My Sales Table";
DimValues: List of [Code[20]];
Dim1No, Dim2No, I : Integer;
Iteration: Integer;
Results: array[10] of Integer;
InitialDate: Date;
StartTime: Time;
begin
InitDimValuesList(DimValues);
InitialDate := 20240125D;
for I := 1 to 10 do
for Iteration := 1 to 10 do begin
SelectLatestVersion();
DropSqlBufferCache();
MySalesTable.SetRange(
"Posting Date", InitialDate, InitialDate + I - 1);
StartTime := Time();
for Dim1No := 1 to 10 do
for Dim2No := 1 to 10 do begin
MySalesTable.SetRange(
"Customer No.",
GetDimValue(DimValues, 1, Dim1No));
MySalesTable.SetRange(
"Item No.",
GetDimValue(DimValues, 2, Dim2No));
MySalesTable.CalcSums(Amount);
end;
Results[I] += Time() - StartTime;
end;
for I := 1 to 10 do
Results[I] := Round(Results[I] / 10, 1);
end;As you can see, I run the test on 10 data samples, increasing the number of records in each iteration. Since the test data is generated such that each date contains exactly 100 records for each combination of customer and item, simply expanding the date range by one day, I add 100 records to each of the 100 groups (10 customers multiplied by 10 items).
To minimize effects of random fluctuations, I execute each test 10 times and then divide the total time by 10. Besides that, I reset Business Central server cache and SQL Server buffer cache before each iteration, running every test on a cold cache. For BC, I do it by calling the SelectLetestVersion function. In case of SQL Server, I can benefit from a little .Net injection, since the test runs on an on-prem Docker container. I have my .Net component that connects to the server and executes the DBCC DROPCLEANBUFFERS command. It is called from the DropSqlBufferCache AL procedure in the code above.
To spice it up a little, I did another test with a query object, replacing the nested loops on dimension values and CalcSums with the following query.
query 50500 "MySales by Customer/Item"
{
QueryType = Normal;
elements
{
dataitem(MySalesTable; "My Sales Table")
{
column(CustomerNo; "Customer No.")
{ }
column(ItemNo; "Item No.")
{ }
filter(PostingDate; "Posting Date")
{ }
column(Amount; Amount)
{
Method = Sum;
}
}
}
}I can run this query once with the required date filter, and receive the set of amounts grouped by the customer and item numbers.
For the query test, I replace the loop on dimension values with the following code.
MySalesByCustomerItem.SetRange(
PostingDate, InitialDate, InitialDate + I - 1);
StartTime := Time;
MySalesByCustomerItem.Open();
while MySalesByCustomerItem.Read() do;
MySalesByCustomerItem.Close();
Results[I] += Time() - StartTime;Indexes
In the very first part of the test, I run both calculations - CalcSums in nested loops and the query - without any indexes on the table. After measuring the time for the unindexed table, I added the following SIFT key:
key(DateCustomerItem; "Posting Date", "Customer No.", "Item No.")
{
SumIndexFields = Amount;
}And finally, in the third part of the test, I change the index removing SumIndexFields and declaring Amount as an included field. This index works for the query that filters records on the Posting Date value and groups the amounts by Customer No. and Item No.
key(DateCustomerItem; "Posting Date", "Customer No.", "Item No.")
{
IncludedFields = Amount;
}I could actually make both Customer No. and Item No. included fields and declare the index like this:
key(DateCustomerItem; "Posting Date")
{
IncludedFields = "Customer No.", "Item No.", Amount;
}Since both Customer No. and Item No. are used only in the aggregation and not as search predicates, this alternative index does not impact read performance. What is important though, is that the same index that works for the aggregating query, is much less efficient for CalcSums. To make CalcSums perform as fast as possible, I need to have an index with the most selective field in the first place. This can be either Customer No. or Item No., since both have only 10 distinct values. Unlike the case with the query, I place filters on fields Customer No. and Item No. to run CalcSums, meaning that these must be key fields and cannot be added to the index data as included fields.
So this will be my covering index for the CalcSums test.
key(CustomerItemDate; "Customer No.", "Item No.", "Posting Date")
{
IncludedFields = Amount;
}Test results
After running all tests, I collected the results in the following table. The first column in this table represents the number of records in one group - records that a single CalcSums call must aggregate. All times are given in milliseconds.
No. of records | No index – CalcSums | No index – Query | SIFT – CalcSums | SIFT – Query | Covering index – CalcSums | Covering index – Query |
|---|---|---|---|---|---|---|
100 | 5319 | 254 | 28 | 3 | 59 | 17 |
200 | 5575 | 266 | 29 | 3 | 56 | 31 |
300 | 5651 | 276 | 29 | 2 | 58 | 46 |
400 | 5749 | 289 | 30 | 2 | 61 | 61 |
500 | 5874 | 300 | 29 | 2 | 65 | 73 |
600 | 5955 | 312 | 28 | 2 | 75 | 88 |
700 | 6059 | 324 | 28 | 2 | 73 | 103 |
800 | 6125 | 333 | 30 | 2 | 77 | 116 |
900 | 6206 | 352 | 29 | 3 | 80 | 131 |
1000 | 6373 | 361 | 29 | 3 | 83 | 144 |
Presented in a chart, the numbers look like this.
It's obvious from the data that running CalcSums to calculate each number separately on a table without a proper index is a bad idea. Even a simple aggregating query that can produce all numbers in a single run can dramatically reduce the total time required for the calculation.
The "bruteforce" calculation is so slow compared to other methods that the respective chart line dwarfs all other data. For better visibility, the next chart shows the same data excluding the slowest method.
Here we can see that the query combined with the covering index (included fields) can even beat CalcFields with a SIFT when the number of records is relatively small. On the other hand, the time for the query with included fields (brown line in the chart) grows proportionately to the increment of the input data, while the time for the SIFT-based calculations (red and yellow lines) is almost constant, independent from the number of records. Which is perfectly reasonable, considering the pre-aggregated nature of the SIFT data.
What is more remarkable, though, is that the query run time grows faster than the time for CalcSums in a loop with the same covering index (green line). I'll explain this phenomenon in a following section, and for now let's just take a note of it. When a covering index is used, as the amount of data reaches some critical point, a query with the GROUP BY aggregation performs worse than separate CalcSums in a loop. In my test this tipping point is approximately 400 records in each of one hundred groups, but of course, in practice, this number depends on many factors, including data distribution, number of groups, and the hardware performance (specifically CPU and hard drive).
Test with larger dataset
I also repeated the same tests with a larger amount of data. For this test run I prepared 10 times larger dataset, so that each combination of customer, item, and date contains 1000 records.
No. of records | No index – CalcSums | No index – Query | SIFT – CalcSums | SIFT – Query | Covering index – CalcSums | Covering index – Query |
|---|---|---|---|---|---|---|
1000 | 63870 | 2001 | 34 | 3 | 105 | 150 |
2000 | 69333 | 2122 | 34 | 3 | 159 | 301 |
3000 | 70788 | 2255 | 33 | 3 | 227 | 448 |
4000 | 72278 | 2370 | 34 | 3 | 276 | 609 |
5000 | 67271 | 2514 | 34 | 3 | 330 | 791 |
6000 | 70470 | 2622 | 35 | 3 | 371 | 894 |
7000 | 71866 | 2757 | 34 | 3 | 415 | 1045 |
8000 | 82874 | 2906 | 35 | 3 | 455 | 1193 |
9000 | 75073 | 3005 | 36 | 3 | 495 | 1332 |
10000 | 74478 | 3173 | 36 | 4 | 542 | 1505 |
Again, same numbers presented in a chart, with the bruteforce CalcSums and no index being way slower that any other calculation.
The slowest calculation removed from the dataset, all other methods demonstrate results similar to the previous table where the smaller dataset was used.
Time it takes to get data from a SIFT view is nearly the same, no matter the number of entries in the set because the burden of the aggregation is shifted to the update, and the performance of the SELECT query depends on the number of groups, and not the number of entries in the group.
And with the covering index available, the GROUP BY query is consistently slower than CalcSums in a loop.
Execution plans for different queries
Now a few words about query execution plans for different scenarios and performance of the aggregating query. First, let's have a look at the query produced by the CalcSums function and its execution plan.
The query itself is a simple SELECT SUM with a set of WHERE conditions corresponding to record filters in AL.
SELECT
SUM("50501"."Amount")
FROM "CRONUS".dbo."CRONUS International Ltd_$My Sales Table$382b9cf1-7c89-48eb-bdc6-be609426126a" "50501" WITH(READUNCOMMITTED)
WHERE ("Customer No_"=@0
AND "Item No_"=@1
AND "Posting Date">=@2 AND "Posting Date"<=@3)When the table does not have a suitable index that the query can use, the query optimizer produces an execution plan based on the clustered index scan. The next operator downstream (stream aggregate) takes only a fraction of the query time. And this plan is repeated for each invocation of CalcSums. Even the fact that the first execution caches table data, and all subsequent runs read table rows from the buffer cache does not change much. This is the slowest scenario, far behind all other calculation methods.
Now let's see what happens when we run the query object. This is the SQL query generated from the AL query.
SELECT
"MySalesTable"."Customer No_" AS "CustomerNo",
"MySalesTable"."Item No_" AS "ItemNo",
ISNULL(SUM("MySalesTable"."Amount"),0.0) AS "Amount"
FROM "CRONUS".dbo."CRONUS International Ltd_$My Sales Table$382b9cf1-7c89-48eb-bdc6-be609426126a" AS "MySalesTable" WITH(READUNCOMMITTED)
WHERE (
ISNULL("MySalesTable"."Posting Date",'1753.01.01')>=@0
AND ISNULL("MySalesTable"."Posting Date",'1753.01.01')<=@1)
GROUP BY "MySalesTable"."Customer No_","MySalesTable"."Item No_"
ORDER BY "CustomerNo" ASC,"ItemNo" ASC OPTION(FAST 50)SQL query here rolls up amounts by Customer No. and Item No. and returns all groups in a single run. It still has to scan the table if no index can facilitate the search. As you can see in the plan below, the actual execution time is very close to the previous plan, even though the number of rows it returns is 10 times higher. But, as opposed to CalcSums called 100 times in a loop, this query is executed only once.
When we give the query optimizer a good index which can eliminate the need for the table scan and involve an index seek, the ratio between the read and subsequent processing turns upside down.
Here, thanks to the low cardinality and high selectivity of the index on the Customer No., index seek reads 1000 rows literally in no time. Stream aggregate is also a very fast operation. In the context of this query, stream aggregate simply summarizes the amount from all rows sent to the operator.
Stream aggregate requires the input to be sorted, and in case of the GROUP BY query SQL Server cannot use the stream aggregate anymore and switches to the hash match operator to calculate the sums. And here we see how hash match starts taking toll on the query performance.
Index seek is still fast enough - it takes only 30 ms to retrieve 100 000 rows, but now the slowest part of the query is hash match due to the relatively high cost of the hash table maintenance.
Still this test result is not a recommendation to always prefer CalcSums over a grouping query. Index seek operation for CalcSums is so fast in my test only thanks to the index on a field with only 10 distinct values. With the index seek being so fast, the hash match that takes 130 milliseconds is the relatively slow part of the query. With thousands of customers and items, the balance could shift the other way and the GROUP BY query would be preferable.
Covering indexes with included fields can be a good alternative to SIFT, a compromise solution offering acceptable performance of aggregating queries with less impact on concurrent updates. In some cases, with the low number of entries to aggregate, a query object with a good index can even outperform CalcSums with a SIFT view. But finding a good index requires some analysis of the data and use cases.
Comments