This is another article about Partial Records (as we already discussed earlier in the article about Partial Records and later in the first part of articles about partial record’s performance). In this article, we will explore the ration between performance, partial records and system with more tableextensions.

Let’s start with the example of our AL Code which we use for performance testing. This code is very similar to the previous article’s code; we only removed parts that were linked to the JIT loading example.

 report 90001 "TKA Partial Records 2"
 {
     Caption = 'Partial Records 2';
     UsageCategory = Administration;
     ProcessingOnly = true;
     ApplicationArea = All;

     trigger OnPostReport()
     var
         GLEntry: Record "G/L Entry";
     begin
         ForEachGLEntries(GLEntry);
         Clear(GLEntry);

         GLEntry.SetLoadFields(Amount);
         ForEachGLEntries(GLEntry);
     end;

     procedure ForEachGLEntries(var GLEntry: Record "G/L Entry"): Decimal;
     var
         SumTotal: Decimal;
         Counter: Integer;
         TempName: Text;
     begin
         if GLEntry.FindFirst() then
             repeat
                 SumTotal += GLEntry.Amount;
                 Counter += 1;
             until GLEntry.Next() < 1;
         Message(TempName);
         exit(SumTotal / Counter);
     end;
 }

The first example is trivial. We just run our AL example without any change on the empty Cronus database. Unsurprisingly, if we loop through all G/L Entry table records, generated SQL Command load all fields.

 SELECT 
      "17"."timestamp",
      "17"."Entry No_",
      "17"."G_L Account No_",
      ... All Table Fields (56 fields hidden) ...
      "17"."Last Modified DateTime"
      "17"."$systemId",
      "17"."$systemCreatedAt",
      "17"."$systemCreatedBy",
      "17"."$systemModifiedAt",
      "17"."$systemModifiedBy"
 FROM "CRONUS".dbo."CRONUS International Ltd_$G_L Entry$437dbf0e-84ff-417a-965d-ed2bb9650972" "17"  WITH(READUNCOMMITTED)  
 ORDER BY "Entry No_" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)

If we change our code to use SetLoadField method and specifies all fields that we need, the generated SQL Command is optimized. It contains fewer fields (= less data that needs to be transferred from a database server to an application server).

 
 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 International Ltd_$G_L Entry$437dbf0e-84ff-417a-965d-ed2bb9650972" "17"  WITH(READUNCOMMITTED)  
 ORDER BY "Entry No_" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)

Although it could look like a significant difference, from database server performance point of view, both queries are almost the same (the first one needs a bit more performance to load & process additional unnecessary fields).

HOWEVER, the biggest difference comes with the environment, which uses table extensions!

Table Extension impacts on performance.

Let’s imagine that we have an environment that has our extensions (that has one table extension on G/L Entry table with two Text[2048] fields) and also another 3 AL Extensions each with G/L Entry table extension each with two Text[2048] fields. For example, the table extension can look like:

 tableextension 90003 "TKA G/L Entry Extension 4" extends "G/L Entry"
 {
     fields
     {
         field(90006; "TKA Veeery Long Description 7"; Text[2048])
         {
             Caption = 'Veeery Long Description 7';
             DataClassification = CustomerContent;
         }
         field(90007; "TKA Veeery Long Description 8"; Text[2048])
         {
             Caption = 'Veeery Long Description 8';
             DataClassification = CustomerContent;
         }
     }
 }

We have one table from the Base App, one Table from our Extension and another 3 Tables from other extensions.

Once we rerun the example, the generated SQL Command is much complicated, contains INNER JOIN for all tables and also in addition to standard table fields, also all fields from all tables extensions (and we have absolutely no control about their design and performance-requirements).

 
 SELECT 
      "17"."timestamp",
      "17"."Entry No_",
      "17"."G_L Account No_",
      ... All Table Fields (56 fields hidden) ...
      "17"."Last Modified DateTime",
      "17_e1"."TKA Veeery Long Description",
      "17_e1"."TKA Veeery Long Description 2",
      "17_e3"."TKA Veeery Long Description 5",
      "17_e3"."TKA Veeery Long Description 6",
      "17_e4"."TKA Veeery Long Description 7",
      "17_e4"."TKA Veeery Long Description 8",
      "17_e5"."TKA Veeery Long Description 3",
      "17_e5"."TKA Veeery Long Description 4",
      "17"."$systemId",
      "17"."$systemCreatedAt",
      "17"."$systemCreatedBy",
      "17"."$systemModifiedAt",
      "17"."$systemModifiedBy" 
 FROM "CRONUS".dbo."CRONUS International Ltd_$G_L Entry$437dbf0e-84ff-417a-965d-ed2bb9650972" "17"  WITH(READUNCOMMITTED)  
 JOIN "CRONUS".dbo."CRONUS International Ltd_$G_L Entry$425df973-0cda-4173-9e7c-e91ae677bae1" "17_e1"  WITH(READUNCOMMITTED)  ON ("17"."Entry No_" = "17_e1"."Entry No_") 
 JOIN "CRONUS".dbo."CRONUS International Ltd_$G_L Entry$fa14d844-0ad9-4210-9171-9a86c033de53" "17_e3"  WITH(READUNCOMMITTED)  ON ("17"."Entry No_" = "17_e3"."Entry No_") 
 JOIN "CRONUS".dbo."CRONUS International Ltd_$G_L Entry$fa14d844-0ad9-4210-9271-9a86c033de53" "17_e4"  WITH(READUNCOMMITTED)  ON ("17"."Entry No_" = "17_e4"."Entry No_") 
 JOIN "CRONUS".dbo."CRONUS International Ltd_$G_L Entry$fa14d944-0ad9-4210-9171-9a86c033de53" "17_e5"  WITH(READUNCOMMITTED)  ON ("17"."Entry No_" = "17_e5"."Entry No_") 
 ORDER BY "Entry No_" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)

Nevertheless, the second command generated absolutely same SQL Command as earlier when we had no additional tables installed. The performance difference is obvious now.

 
 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 International Ltd_$G_L Entry$437dbf0e-84ff-417a-965d-ed2bb9650972" "17"  WITH(READUNCOMMITTED)  
 ORDER BY "Entry No_" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)

SQL stats

What is behind these SQL Commands? In the previous article, we saw that the environment’s execution plan without any extension is not really interesting… It is just one Index Scan from the table itself.

However, the same Plan is used for environments with table extensions too, if we use fields specified using SetLoadFields! Furthermore, if we use the “old” approach (= without using SetLoadFields), SQL Server must join all table extension using INNER JOIN command that results in much complicated (and much performance-consuming)! It can be easily seen in the picture below; for example, the relative query cost of the command generated using SetLoadField is 4% compared to 93%! That is a huge difference and could result in many troubles and performance issues.

Conclusion

To summarize, it is essential to start using Partial Records for any logic with bigger-than-small complexity. Furthermore, it is crucial to minimize the number of extensions on tables, which can have many rows and/or are used within standard processes (which unfortunately does not use Partial Records yet).

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.

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.