Partial Records in the Business Central are a new technology in 2020 wave 2 release. Let’s look at the quick description in one of the posts from previous month “Partial Records“.
Now check it in more details. In this first part, we will look at how the partial records work and what is behind these commands. In the second part, we will check what the impact of using/not using partial records on tables with table extensions (or tables where we are not sure whether they will have a table extension in the future / in which quality the table will be designed) is.
Let’s start with the preparation. At first, we need to create an AL extension that serves our purpose – sum all amounts from G/L Entries (yeah, it should not be definitely done using loops (rather using FlowFields/CalcSums, but it is just for the example…).
We do the sum using different techniques
- Load all fields (use Amount field only)
- Load all fields (use Amount and Description fields)
- Load only Amount field (use Amount field only)
- Load only Amount field (use Amount and Description fields)
report 90000 "TKA Partial Records"
{
Caption = 'Partial Records';
UsageCategory = Administration;
ProcessingOnly = true;
ApplicationArea = All;
trigger OnPostReport()
var
GLEntry: Record "G/L Entry";
begin
// Load all fields (use Amount field only)
ForEachGLEntries(GLEntry, false);
Clear(GLEntry);
// Load all fields (use Amount and Description fields)
ForEachGLEntries(GLEntry, true);
Clear(GLEntry);
// Load only Amount field (use Amount field only)
GLEntry.SetLoadFields(Amount);
ForEachGLEntries(GLEntry, false);
Clear(GLEntry);
// Load only Amount field (use Amount and Description fields)
GLEntry.SetLoadFields(Amount);
ForEachGLEntries(GLEntry, true);
end;
procedure ForEachGLEntries(var GLEntry: Record "G/L Entry"; LoadName: Boolean): Decimal;
var
SumTotal: Decimal;
Counter: Integer;
TempName: Text;
begin
if GLEntry.FindSet() then begin
repeat
SumTotal += GLEntry.Amount;
if LoadName then
TempName := GLEntry.Description;
Counter += 1;
until GLEntry.Next() = 0;
exit(SumTotal / Counter);
end;
end;
}How does SQL queries look?
We start with SQL generated from the code above. The example is run on the Cronus DB that has 2 734 records in the G/L Entry table. As we access the code in the loop using FindSet method, the application server loads each time 50 records (fast 50 SQL statement in the options part of the query).
The used G/L Entry table has approximately 60 fields (+ some system-related fields like $systemId or $systemCreated/Modified).
1) Load all fields (use Amount field only)
In the first case, we want to load all data even if we want to work with only one field. The generated SQL contains all fields from the table.
SELECT
"17"."timestamp",
"17"."Entry No_",
... All Table Fields (56 fields hidden) ...
"17"."Closed",
"17"."$systemId",
"17"."$systemCreatedAt",
"17"."$systemCreatedBy",
"17"."$systemModifiedAt",
"17"."$systemModifiedBy"
FROM "CRONUS".dbo."CRONUS CZ s_r_o_$G_L Entry$437dbf0e-84ff-417a-965d-ed2bb9650972" "17" WITH(READUNCOMMITTED)
ORDER BY "Entry No_" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)
2) Load all fields (use Amount and Description fields)
As we loaded all fields, there is no difference in generated SQL between accessing Amount and Description fields. The code and all statistics are the same for both parts.
3) Load only Amount field (use Amount field only)
For this part, we used SetLoadFields(Amount) that specified we want just one field.
The generated code is very different. Only a few fields are required (system fields, primary key fields, used key fields and fields specified using SetLoadFields() method.
In compare to the first example, we load only 12 fields (the first example loaded 64 fields)
SELECT
"17"."timestamp",
"17"."Entry No_",
"17"."G_L Account No_",
"17"."Amount",
"17"."Bal_ Account Type",
"17"."Source Type",
"17"."FA Entry Type",
"17"."$systemId",
"17"."$systemCreatedAt",
"17"."$systemCreatedBy",
"17"."$systemModifiedAt",
"17"."$systemModifiedBy"
FROM "CRONUS".dbo."CRONUS CZ s_r_o_$G_L Entry$437dbf0e-84ff-417a-965d-ed2bb9650972" "17" WITH(READUNCOMMITTED)
ORDER BY "Entry No_" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)
4) Load only Amount field (use Amount and Description fields)
The last example specifies still only Amount field using SetLoadFields; however, in the loop, we also access Description variable. Based on the information from the Microsoft, JIT technology should acquire the value of this field from SQL.
However, based on my experience, the Business Central application server optimize these queries and add this field to the query while obtaining the data for the Amount field. The server now loads 13 fields (12 same as in the previous example + Description).
I do not have any evidence/better knowledge of this behaviour yet, but I assume that the JIT will be used for more complicated queries than this one (when the BC app server optimizer would not be able to merge the field to the source query). I will try to examine this in the future.
SELECT
"17"."timestamp",
"17"."Entry No_",
"17"."G_L Account No_",
"17"."Amount","17".
"Bal_ Account Type",
"17"."Source Type",
"17"."FA Entry Type",
"17"."$systemId",
"17"."$systemCreatedAt",
"17"."$systemCreatedBy",
"17"."$systemModifiedAt",
"17"."$systemModifiedBy",
"17"."Description"
FROM "CRONUS".dbo."CRONUS CZ s_r_o_$G_L Entry$437dbf0e-84ff-417a-965d-ed2bb9650972" "17" WITH(READUNCOMMITTED)
ORDER BY "Entry No_" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)
What about SQL plans/statistics
As the queries for parts one and two (and also for parts three and four) are very similar, we will look on plans for queries 1 and 3 only.
Execution plans
Execution plans are the same for both queries (and as we access only one table, it is not really surprising). The only difference is in the time of the running query.
Client Statistics
The most interesting part of the analysis is statistics. Just to remind, the query load 50 rows each time, so the statistics are for 50 lines too.
The execution time for the query that loads all fields is almost 2.5x higher than the time for the query loading only necessary fields. The similar difference is seen on the “Bytes received from server” where the standard query had to load 1 342 kB in compare to the query with SetLoadFields that had to load only 354 kB (and that is just for 50 rows!).
Conclusion
From my point of view, the advantages of optimizing performance with SetLoadFields are evident. I agree that usage of SetLoadFields method is more complex (because developers must know that they should not access other fields from those specified in this method). Still, for complex queries that need only a few fields from the table (but the query must iterate through many records), it should definitely bring better performance.
In the next part of this article, we will look at SetLoadFields from the view of tables that have one or more table extensions.
