Category Archives: SQL

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

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.

Partial Records

Partial Records are a new capability of Business Central introduced in Business Central 2020 release wave 2. It allows specifying fields that should be loaded when accessing SQL based data.

How does it work? Without using partial records, Business Central normally load all data from the record even if only one of them is required. The partial records, developer specify which fields are needed and the Business Central than load only that fields.

This is especially (based on Microsoft notes) important when Tableextensions are used. In that case, each Tableextension is stored as a separate table on the SQL server and, when the data from this table are loaded, SQL server joins the primary table, extension table (and other extension tables if exist) using join. Although the join is done using primary keys, the query is still much more performance-intensive than the query that accesses only one table.

Related Functions

There are four related functions:

  • [Ok := ] Record.SetLoadFields([Fields: Any,…])
    • Using the function SetLoadFields we specify a set of fields that server should load from the database. Fields have to be specified before the record is retrieved from the database (similar as SetAutoCalcFields()).
    • If the function is called more than once, only fields specified within the last call are loaded.
  • [Ok := ] Record.AddLoadFields([Fields: Any,…])
    • Similar function to SetLoadFields that has one big difference: if the function is called multiple times, the new call does not reset fields that were already set (even if they were set using SetLoadFields).
  • Ok := Record.AreFieldsLoaded(Fields: Any,…)
    • Checks whether the fields specified as parameters are already retrieved from the database.
  • [Ok := ] Record.LoadFields(Fields: Any,…)
    • This function allows loading data from the data source that was not loaded when the last partial record was retrieved (because the field was not specified using SetLoadFields nor AddLoadFields).

More specifically

In the first part, I wrote that only specified fields are loaded when the row is fetched from the database; however, if the fields were not loaded (using SetLoadFields / AddLoadFields) and system needs their value, they are automatically fetched using Just-In-Time (JIT) mechanism.

When JIT loading occurs, the system automatically loads data using the primary keys of the current record. This fetch might fail if the record was changed (modified / renamed / deleted) since the original data was retrieved. And that is the reason why the function LoadFields exists – when JIT loading occurs automatically, there is no way how to resolve if the loading fails. With LoadFields developers can implement explicit error handling for these fails.

Example

The example below is from Microsoft Docs and shows how to use SetLoadFields. As is mentioned on the Docs, in this case, the example is nine times faster than the same code without the Partial Records functionality.

 procedure ComputeArithmeticMean(): Decimal;
 var
     Item: Record Item;
     SumTotal: Decimal;
     Counter: Integer;
 begin
     Item.SetLoadFields(Item."Standard Cost");
     if Item.FindSet() then begin
         repeat
             SumTotal += Item."Standard Cost";
             Counter += 1;
         until Item.Next() = 0;
         exit(SumTotal / Counter);
     end;
 end

Microsoft example

As this is a very different approach to developing custom functionalities, I will definitely check the performance with some advance example codes in some of the next articles.

Access Business Central database running in a container from the Microsoft SQL Server Management Studio

Whether your instances are running directly on the server or in containers, it is sometimes necessary to access a database directly from the SQL Server Management Studio. When you have everything installed on your device, it is usually straightforward – you have the database on SQL Express Server installed together with the Business Central, or the database is deployed on your existing SQL Server.

However, when you use BC Containers (with the standard setup – everything running inside the container, even database), it could be unclear how to access the database.

The first thing to know is that the database can be accessed like any other database, without any special setup.

So, how we can access the database? The most important things to know are that the database is accessible only from the computer where the container is running, and that database server name is the same as the name of your container. The same is also your user name and password (as you used to create a BC container/instance).

Access database hosted in BC container from Microsoft SQL Server Management Studio

Start SQL Server Management Studio on the computer where the container is running, set the server name to the same value as your container name and use your own credentials that you set for the container and that’s it!