Dynamics 365 Business Central: SetLoadFields performances with reference passing or value passing parameters

I’ve written in the past some posts with tips on how to write code for performances and how to be careful on using too much table extensions on heavy used tables if you don’t want to affect the performances of your tenant. There are also some sessions and webcast that I’ve done on these topics during this year (DynamicsCon, webcasts for Microsoft and more).

The partial records capability is one of the latest and more important feature available in Dynamics 365 Business Central for writing performant code. Using Record.SetLoadFields can help you on improving your performances, expecially when looping through records. In this way you can avoid joins with table extensions and you can load only the needed fields for your computation.

But what happens if on a loop you try to access a field that hasn’t been selected for loading?

In this case the platform triggers an implicit GET on the record and loads out the missing field. This is what is called a just-in-time (JIT) loading. A JIT loading means that a new access to the data source is required. This new data access will normally be faster because it’s a GET operation and GET calls can often be served by the server’s data cache or can be resolved as a clustered index operation on the database. But that’s an extra data access needed, so performance will be affected.

SetLoadFields has some hidden behaviour if not used carefully (causing JIT loading due to bad usage of SetLoadFields can cause inconsistent read errors) and when having JIT loads on iterations, the way of passing parameters on your record variable can affect performances a lot.

To show an example of a quite hidden (in my opinion) behaviour, I’ve done a performance test with a demo extension. I’ve created the following simple table:

Then I’ve filled this table with a large number of records (> 500000) with random values for Total Cost and Total Fee.

Then, I’ve created a list page on top of this table with an action called Execute Cost Calculation. The action calls the following ExecuteCalculation method defined in a custom codeunit:

As you can see, here I’m using SetLoadFields for loading the Total Fee field, then for each record in the loop I’m calling the Calculation procedure (by passing the record as value) and inside this procedure I’m using the Total Cost field (not previously loaded, so a JIT loading is required).

Result is the following:

Now, I’ve changed the Calculation procedure for accepting a parameter passed by reference (var). The new code is the following:

What is the result now? This:

Executing a performance test only few times is not 100% reliable, so I’ve repeated it N times, and this is the result of the final test:

As you can see from the chart, the second way (that uses a parameter passed by reference) is always more performant then the first way. Performance gains goes from about 80% to more than 250%.

What does that mean? There are some “hidden” behaviour that you should be aware of.

When iterating over records in the database, an enumerator is created based on selected fields. Then, a row is fetched when NEXT() is called. When using SetLoadFields on loops, the NEXT() operation is able to “correct” the loaded fields.

In the first example, I’m passing the record to work as value to the Calculation procedure, so in this case it’s like using a copy of the original record and then the correction of loaded fields doesn’t work (a JIT load is always executed in this case).

In the second case, I’m passing the record to the Calculation procedure by reference (var keyword). In this case I’m working on the original record (not a copy) and then the NEXT() operation is able to “fix” the JIT loading of fields.

Final considerations:

• Use SetLoadFields on your calculations.
• Please always try to avoid JIT loads.
• When the platform implicitly uses partial records, add the extra fields by calling AddLoadFields from the OnPreDataItem trigger on reports or from the OnOpenPage trigger on OData pages.
• When you have calculations inside loops, passing record parameters by reference helps on performances.