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