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

Retrieve Business Central Report Print-out Using API - Get pdfDocument

Hello folks today I'm going to introduce you one very cool Business Central API that will help you to get the Report PDF file in binary format and use it in further business process.

HTTP Request for the pdfDocument is:

GET https://api.businesscentral.dynamics.com/v2.0/{Business Central Tenant ID}/{Sandbox Environment Name}/api/v1.0/companies({ID})/salesInvoices(ID)/pdfDocument

for request Headers you would pass the Authorization as per your Business Central User.

Not only that, we can generate the Report in Any preferred language you must specify Accept-Language in the Request header, for example Accept-Language: nl-NL for Dutch

Now I'll show you how to get the dynamic parameters of this API.

1) {Business Central Tenant ID}: You can get Tenant Id by simply login to Admin Center. select the Sendbox/Production environment.

In the Tenant URL you can find the Tenant ID. 

Business Central Admin Center - URL

2){Companies ID}: Each Company in your tenant have one unique GUID key associated with it as shown in below screenshot.

Company ID

Other Way of getting all companies Information is that use Get API for Company. As shown below we can use this API in Postman and get required details.

https://api.businesscentral.dynamics.com/v2.0/df4c3c4d-297f-4f04-92e3-617e98144580/geeky/api/v1.0/companies

3){salesInvoices(ID)}: Here we are taking Sales Invoice Example. Documents have unique GUID field attached to it as per the below screenshot we use that GUID as a Sales Invoice ID. 

SalesInvoice ID

If the successful call , this method returns a 200 OK response code and a link to the PDF document in the response body.

HTTP Response for the pdfDocument is:

Response of successful API call is shown below. from this response we got the Sales Invoice Report PDF Content file.

The "content@odata.mediaReadLink" in the response contains the Link that have PDF Data in Binary form.

Response Details

By clinking this Link we get the Report PDF file in Binary form.....Gotcha..Its so easy isn't it.

PDF Report Output in Binary

Hope you like this Blog. Give me your thoughts about the same in Comment Box below. Also Follow me on Social Media Platforms for content like this.


In the Next Blog I'll show you guys how to use this API to create a cool Microsoft Power Automate Integration till then stay tuned.

LinkedIn :  Preet Gor