Partial Records in detail (part 2)

by Dec 29, 2020AL Language, SQL

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

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).

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