Partial Records in detail (part 1)

by Nov 27, 2020AL Language, SQL

Home 9 Development 9 AL Language 9 Partial Records in detail (part 1)

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

  1. Load all fields (use Amount field only)
  2. Load all fields (use Amount and Description fields)
  3. Load only Amount field (use Amount field only)
  4. 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.

Execution plan for example 1 (without SetLoadFields)
Execution plan for example 3 (with SetLoadFields)
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.

Recent Articles from the category

Connect to Azure Function in BC 2022 wave 2 (v21)

Connect to Azure Function in BC 2022 wave 2 (v21)

The new version of the Microsoft Dynamics 365 Business Central brought a new system module "Azure Functions" that makes integration with Azure Functions much easier and straightforward. The typical scenario, why to use Azure Functions together with Business Central,...

read more
Collectible Errors?! Is it already in use?

Collectible Errors?! Is it already in use?

Collectible Errors?! Is it already in use? This is the second part of my new article series about Collectible Errors. Let's check out the first part here: Collectible Errors?! | Microsoft Dynamics 365 - Ing. Tomáš Kapitán (kepty.cz) or you might be interested in my...

read more
Collectible Errors?!

Collectible Errors?!

Collectible Errors?! It has been already almost a year since ErrorInfo datatype & CollectibleErrors were introduced (I already have an article about basic structure: ErrorInfo data type & Collectible Errors). This article was released for the first time in...

read more
Substituting standard reports

Substituting standard reports

Report objects cannot be extended in versions previous to the Business Central 2021 release wave 1 version when ReportExtensions object was introduced. Since then, many of changes we need to do in reports can be done without creating a new copy of object. On the other...

read more
Isolated events

Isolated events

With Business Central 2022 wave 1, a new setting for event publishers was introduced. Until this version, any error in any event subscriber caused interruption to the current running process and stopped the whole activity. In some cases (such as log-in), this is...

read more
Custom Filter Tokens

Custom Filter Tokens

As a user of the Business Central you have some constants you can use to filter or insert values. These constants contain useful values for data manipulation such as t / today for date field - return current dateq / quarter for date field filters - return range of the...

read more
1D & 2D Barcodes in Business Central 19.1

1D & 2D Barcodes in Business Central 19.1

Business Central (cloud-only!) includes one-dimensional (1D) barcode fonts since Microsoft Dynamics 365 Business Central 2020 wave 2 (17.0) was released in October 2020. It is a great improvement to how developers can handle requirements from their clients to print...

read more

Sign Up for News

Certifications

Highest certification
Microsoft Data Management and
also in D365 Business Central

Microsoft Certified: Dynamics 365 Business Central Functional Consultant Associate

See other certifications here