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.

7 Comments

  1. If you don’t force the JIT is there a material difference in the performance between ref and var?

    Like

  2. Hi

    This is not related to this post and sorry for asking here.

    Have you published or aware of any method that in BC SaaS that user can schedule a report to run and print? The goal is to schedule a report to run daily and print on specific printer. I searched and could not find any method. When I try Send To and Schedule and change PDF to Print it shows error. It shows same error when I try it in Job Queue and change PDF to Print.

    Thanks

    Like

    1. You can schedule a report to run daily by using Job Queue. First, confirm that the Object Type to Run field is set to Report.
      Then find the object ID of the report you want to run and enter it into the Object ID to Run. Then you can schedule the recurrence. When the report finishes, it will appear in your Report Inbox on the Role Center.

      Like

      1. Thanks. I have done that but still is in Report Inbox. The goal is that report (Inventory – Backorder) to print in different printers for different group of production/warehouse users (they do not have access to BC). It was a way in On-prem (select print instead of pdf/word/etc in job queue then select printer) but not in SaaS (it shows error if you select print). I was wonder if you have had a chance to try it and see if it is any workaround available.

        Thanks

        Like

      2. Thank you

        I have followed your guideline and Roberto Stefanetti about email printer. I have no issue when I select Print (instead of Schedule) to print on those printers. But when I try “Send Tp ” then “Schedule” and change PDF to Print I receive the following message.

        Have you tried Schedule the report (which goes to Job Queue) and then change PDF to Print in BC SaaS?

        Thanks

        You cannot select a printer from this online product. Instead, save as PDF, or another format, which you can print later.

        The output type has been set to PDF.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.