Improved performance features of Business Central 17

 In the training I’ve done to some partners last week, when talking about performances I shared an example of an extension with 3 features that I think are not so well known but that have a significant impact on how your code performs, expecially on a SaaS environment. That extension used the following features:

• Partial record loading
• Temporary Tables
• QueryCategory

The Partial record capability is a new feature available starting from Dynamics 365 Business Central 2020 Wave 2 (v17) and I think it’s one of my top personal desiderata from years.

This feature permits you to load only the needed fields of a recordset (when accessing a data source) instead of loading the entire set of fields. This is particularly useful on reports and OData pages because you can avoid to do a SELECT * (plus JOINS with all the extension’s tables) and instead doing a SELECT of only the fields you need.

Now you can do something like:

procedure TestPartialRecord(): Decimal
    var
        Item: Record Item;
        total: Decimal;
    begin
        Item.SetLoadFields(Item."Unit Cost");
        if item.FindSet() then
            repeat
                total += item."Unit Cost";
            until Item.Next() = 0;
        exit(total);
    end;

As you can see in the above sample, you can use SetLoadFields to set which fields you want to retrieve in your operation ad the next FindSet() will retrieve only those fields (without loading extra fields and loading fields that comes from tableextensions of the Item table).

If you try to access a field that was not set to be loaded, in this case the platform performs an implicit GET operation on the record and loads that missing field.

You should not use partial record loading if you’re doing operations like inserts, deletes or if you’re using temporary records because in this case it’s required that the record is fully loaded.

The second feature explained on that example was the usage of Temporary tables. The concept of temporary tables is the same as in the past: they’re a temporary variable that holds data, but its data is not stored in the database but held in memory until you close or deallocate the table (A temporary table reduces the load on both the network and the SQL database backend).

For creating a temporary table, you’ve essentially the following ways:

• using a temporary record variable (like TempItem: Record “Item” temporary;)
• setting the SourceTableTemporary property on a page to true
• Setting the TableType property of a table to Temporary (finally!)

With Dynamics 365 Business Central v17 you can declare a table as temporary like in the following example:

table 50100 MyTable
{
  DataClassification = CustomerContent;
  TableType = Temporary;
  fields 
   {
    ...
   }
}

The advantage of this way of declaration is that the table schema isn’t synchronized with the database and so it does not have restrictions on breaking schema changes.

The third feature present on that sample was the usage of the QueryCategory property. I think that this is quite hidden, but with this property you can now execute a query object from a page by defining a query in AL and then specifying that property by setting a comma-separated list of query categories that this object can support.

As an example, this is a query defined in AL:

query 50100 "Top 10 Customer for Sales SD"
{
    Caption = 'Top 10 Customer Sales SD';
    OrderBy = Descending(Sum_Sales_LCY);
    TopNumberOfRows = 10;
    QueryCategory = 'Item List', 'Customer List';

    elements
    {
        dataitem(Cust_Ledger_Entry; "Cust. Ledger Entry")
        {
            filter(Posting_Date; "Posting Date")
            {
            }
            column(Customer_No; "Customer No.")
            {
            }
            column(Sum_Sales_LCY; "Sales (LCY)")
            {
                Method = Sum;
            }
        }
    }
}

The result of this is that you can immediatly execute the query from the specified pages (like a SmartList):

These are small tips, but that can affect a lot the performances of your code. Please keep them in mind and start using them when needed. Loving this work on the performance side… 

D365BC,