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.