Performance Improvements for Business Central - Using Partial Records

When Microsoft released the successor of NAV the Business Central many industry leaders questions about system's performance and scalability particularly for (extension object) customization approach in the new AL Language Extensions and the question is actually valid, if we have a large amount of customization and if a customer is using multiple apps from appsource....there will be a huge number of extension objects associated with it and this will result in a performance loss.     

Well the Business Central has improved a lot in terms of performance / UI lag over its first release (13.0) but from the latest update in Business central 2020 release wave 2 (October 20)  Microsoft introduced the new concept called "Partial Records". 

Words from Microsoft :

The partial records capability in Business Central allows for loading a subset of normal table fields when accessing a SQL based data source. Using partial records improves performance of objects like reports and OData pages - objects whose source code loops through records. It's particularly beneficial when table extensions are used in the application.

 

Accessing a data source from AL code is typically done by using the record's methods GET,FIND, NEXT.

So if we apply some filter in item record and do the process then the system will consider all the fields from table item and do the process and performance would be slower.  

if Item_gRec.FindSet() then begin

    repeat

        //Process 

    until Item_gRec.Next() = 0; 

end;

here the the conventional code shown above means the code will consider all the fields associated with table "Item" but what if I want only one field "Standard cost" from Item to be considered only. In this case considering all the fields from item will be much slower in terms of performance. The code shown below used the new functionality "Partial Records" makes sense and comparatively faster in terms of performance .

Item_gRec.SetLoadFields(Item_gRec."Standard Cost");

if Item_gRec.FindSet() then begin

    repeat

        //Process

   until Item_gRec.Next() = 0;

end;

Partial Record dose the same thing for us in AL code. so in a traditional AL Code, the runtime loads all normal fields when accessing the data source. Using the partial records API, you can select a set of fields and only load them.

So how to use this in AL ? We have two methods available for this named "SetLoadFields" and "AddLoadFields". and we can use this methods both for Record and RecordRef datatypes.

SetLoadFields : 

Specifies a set of fields to be initially loaded when the record is retrieved from its data source. A call to this method will overwrite any fields that were previously set to load.

AddLoadFields : 

Adds fields to the current set of fields to be initially loaded when the record is retrieved from its data source. Subsequent calls to this method won't overwrite fields that were previously selected for loading.

 

Example:

    procedure ComputeArithmeticMean(): Decimal;

    var

        Item_lRec: Record Item;

        SumTotal_lDec: Decimal;

    begin

        Item_lRec.SetLoadFields(Item_lRec."Standard Cost");

        if Item.FindSet() then begin

            repeat

                SumTotal_lDec+= Item_lRec."Standard Cost";

            until Item_lRec.Next() = 0;

        end;

        exit(SumTotal_lDec);

    end

In the example as we can see, SetLoadFields occurs before the data fetching operations. This call determines which fields are needed for the FindSet call. You use the same pattern for AddLoadFields calls.

Some Important Points: 

1.Most significant performance gains can be seen with table extensions, by not loading unnecessary fields in table extensions.

2.Testing on the previous example code showed that the execution time for loading only the "Standard Cost" field was nine times faster than loading all normal fields.

3. It's not recommended to use partial records on a record that will do inserts, deletes, or copies to temporary records.

4. All these operations require that the record is fully loaded, so you lose the performance gains of loading fewer. For this reason, the feature is especially advantageous in reading-based scenarios.

5. In Reports the fields that are selected for loading are fields setup as columns in the report data set. If a report accesses a field that isn't in the data set, it's beneficial to do Add the field as a column in the data set and add the field on the OnPreDataItem trigger.

References:

Here's the Microsoft Doc link that cover all the performance related topics and much more about "Partial Records".

Performance Articles For Developers

that's it for this blog...If you enjoyed this post, share it..!