How to Use SetLoadFields in Business Central for performance

With the release of Business Central Wave 1 2020 the partial records capability is available.

The partial records capability is allows for loading a subset of normal table fields when accessing the data source based on SQL.

To access the data source we use GET ,FIND NEXT. When we use these methods then runtime it load all fields and will hamper the performance while fetching the records.

Now from business central wave 2 2020 we can use ‘Partial records’ capability.

To accommodate partial records following methods available in business central. These methods are avaaible on RecordRef and Record datatype in AL. These methods are loaded in two groups i.e. Subsequent loads and Current load

SetLoadFieldsSpecifies a set of fields to be initially loaded when the record is retrieved from its data source.
AddLoadFieldsAdds fields to the current set of fields to be initially loaded when the record is retrieved from its data source.
AreFieldsLoadedChecks whether the specified fields are all initially loaded.
LoadFieldsAccesses the table’s corresponding data source to load the specified fields.

Lets See few examples of these methods

Following example is for summation of Quantity fields from Item Ledger Entry .In this we would like to make calculation of only Quantity fields and not require the others fields to be loaded.

If you see SetLoadFields is call before data fetching operation starts. This will determine which fields needed for Find Set Call.

Advantage of this is that it gives you ability to load only those fields which are required to perform the operation which will make execution of code faster and give more performance. This feature is recommended to use while fetching the records and not for Insert,Modify or Delete operation.

Using the above method we added the required fields but what will happen to other fields which hasn’t been loaded .In such cases platform does an implicit GET and loads the missing fields, called as JIT Loading (Just in Time).

When JIT loading occurs, another access to the data source is required. These operations tend to be faster because they’re GET calls. GET calls can often be served by the server’s data cache or become clustered index seeks on the database.

Iterating over records in the database, enumerator is created based on selected fields and row is fetched when NEXT is called. but certain operations will not follow enumerator like if we passing a record by value and not by VAR .This operation actually creates new copy of the record and original records and copy of the record not share the filters ,selected field for loading. That means to access unloaded fields it need to trigger JIT load and but it will not update enumerator which means for future iterations also require JIT load.

To overcome such situations we can do following

  1. Pass the record reference using VAR.
  2. Call AddLoadFields on the original records before passing by value.
  3. Use SetLoadFields before calling FIND,GET,NEXT.

Hope this will help you to understand for partial records and stay tuned for more updates.

For more updates you can follow my blog on following channels.