Volodymyr Dvernytskyi
Personal blog about Navision & Dynamics 365 Business Central

Partial records. Performance improvements for Business Central.

Finally, with the latest Business Central 2020 release wave 2 we got a performance improvement tool for records reading! This tool is called Partial Records and I'm sure this will now become the new generally accepted standard. The document from Microsoft describes in great detail the principles of how it works, I propose to test this new feature and assume what is hidden under the hood.

Usually, when we select a record from the table it looks like this on the SQL side:

The following example finds Customer with No. 10000 and returns all fields


SELECT *
FROM Customer  
WHERE ([No.] = '10000')

In SQL, the symbol * means a selection of all fields from a table, this greatly slowed performance. But, what if we don't need every field from a table in code or for a report? Let's say we need only No., Name, and Search Name fields. In this case, we able to write SQL request like this:

The following example finds Customer with No. 10000 and returns only No., Name, Search Name


SELECT [No.], [Name], [Search Name]
FROM Customer  
WHERE ([No.] = '10000')

This boosts the performance a lot, especially considering that we can loop over the record and access the database many times.

Performance measurements

Let's say I need to collect Entry No. and Description from all Item Ledger Entries. I have 186688 Item Ledger Entries in my database, if you loop through these entries in this manner, it will take 10 seconds 742 milliseconds.


procedure ILEPerformanceMeasurements()
    var
        ItemLedgerEntry: Record "Item Ledger Entry";
        ILEDictionary: Dictionary of [Integer, Text];
        StartDateTime: DateTime;
    begin
        StartDateTime := CurrentDateTime();

        if ItemLedgerEntry.FindSet() then
            repeat
                ILEDictionary.Add(ItemLedgerEntry."Entry No.", ItemLedgerEntry.Description);
            until ItemLedgerEntry.Next() = 0;

        Message('Working time: %1 \ Total count is: %2', CurrentDateTime() - StartDateTime, ItemLedgerEntry.Count());
    end;

Result message:

If we added the new function SetLoadFields and load only required fields, it will be faster in 2 times!


procedure ILEPerformanceMeasurements()
    var
        ItemLedgerEntry: Record "Item Ledger Entry";
        ILEDictionary: Dictionary of [Integer, Text];
        StartDateTime: DateTime;
    begin
        StartDateTime := CurrentDateTime();

        ItemLedgerEntry.SetLoadFields(ItemLedgerEntry."Entry No.", ItemLedgerEntry.Description);
        if ItemLedgerEntry.FindSet() then
            repeat
                ILEDictionary.Add(ItemLedgerEntry."Entry No.", ItemLedgerEntry.Description);
            until ItemLedgerEntry.Next() = 0;

        Message('Working time: %1 \ Total count is: %2', CurrentDateTime() - StartDateTime, ItemLedgerEntry.Count());
    end;

Result message:

Summary

This is a pretty telling example of how you can speed up the process of working with records. Acceleration twice, this is not the maximum! Depending on the SQL settings, the number of fields and the number of records can be much faster!

Thursday, October 08, 2020

Author: Volodymyr Dvernytskyi