Business Central allows us to set or select the fields that will be initially loaded when the record is retrieved from its data source, improving the performance of objects such as OData pages and reports, and particularly beneficial when using table extensions in the application.
When accessing a dataset, without using partial records, the runtime loads all normal fields when accessing the data source.
The following methods are available on both the RecordRef and Record data type in AL and are divided into two groups:
-Methods that pertain to subsequent loads.
-Methods that pertain to the currently loaded record.
Subsequent load methods
| Method | Description | See more |
|---|---|---|
| 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 previously set fields to load. | Record.SetLoadFields RecordRef.SetLoadFields |
| 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. | Record.AddLoadFields RecordRef.AddLoadFields |
Current load methods
| Method | Description | See more |
|---|---|---|
| AreFieldsLoaded | Checks whether the specified fields are all initially loaded. | Record.AreFieldsLoaded RecordRef.AreFieldsLoaded |
| LoadFields | Accesses the table’s corresponding data source to load the specified fields. | Record.LoadFields RecordRef.LoadFields |
In this post we will use the SetLoadFields method, to demonstrate how its use allows drastically improving calculations made on a data set.
Usage Guidelines
The use of partial records should only be applied for scenarios based exclusively on reading, in other cases, such as inserting, deleting, renaming, transferring fields or copies in temporary records, all the fields of the record will be required to be loaded, for which the platform will issue a JIT upload if they are not already uploaded.
Other considerations:
When working with procedures, the following must be taken into account: when passing a record by value in the procedure parameters, create a new copy of the record, this new copy does not share filters or the fields selected to load, then, access an unloaded field will trigger a JIT load. But it won’t update the enumerator, which means future iterations will also require a JIT load.
The following is recommended:
- Passing the record reference using the var parameter allows the enumerator to be updated.
- Call AddLoadFields(field names) on the original record before passing by value.
- Before calling Get, Find, Next, etc, use SetLoadFields(field names) to set all the fields needed for the load.
Demo Video
The purpose of this video is to show the time difference when calculating the average of the Quantity field.
To carry out this example, I have created a table with 12 fields mixed between decimals and code, except for the key of the table, which is an integer and they have been filled with random values.
For this video, 80,000 records were generated, and the process of calculating the Average of the Quantity column using SetLoadFields and without using SetLoadFields was compared.
Code
Below I show the code that was used to run the video.
As can be seen, there is a method called ComputeArithmeticMean1 that would calculate the average in a normal way (without using SetLoadFields), that is, bringing all the elements contained in the Record even when only the Quantity field is going to be used.
And the other relevant method in the code is ComputeArithmeticMean2, which contains the SetLoadFields function with the Quantity parameter before FindSet() so that only the Quantity field is considered for this calculation.
Conclusion
Because for different reasons, when creating the calculation at different times there may be small variations in duration, this process was run 10 times using the SetLoadFields method and 10 times without using it in order to obtain an average.
The results were the following:
Without using SetLoadFields:
Average: 1,577.70 milliseconds in execution duration.
Using SetLoadFields:
Average: 590.90 milliseconds in execution duration.
You can see that using SetLoadField for this example was 2.67 times faster to perform the calculation.
The use of partial records is highly recommended to perform calculations, run reports, and any process that does not involve insertions, modifications, or eliminations, only for reading processes in order to improve performance.
For more information visit the official documentation at the following link:
Code on GitHub
All the code used in this post can be found at the following link:
I hope this has been helpful.