Hi Readers,
When building custom solutions in Dynamics 365 Business Central, performance is always a key consideration. As developers, we often find ourselves working with large datasets, and retrieving unnecessary fields from the database can lead to performance bottlenecks. One effective way to optimize your AL code and improve performance is by using SetLoadFields. This method, when combined with insights from SQL and Business Central Telemetry, can drastically enhance how your solution interacts with the database.
What is SetLoadFields?
The SetLoadFields method allows you to specify only the fields you need when retrieving data from a table. By loading only a subset of fields, you can reduce the amount of data retrieved from the database, thereby improving the speed of your queries.
In Business Central, when you perform a GET or FIND operation, the system fetches all fields by default. This can be inefficient, especially when you only need a few fields from a record. SetLoadFields comes in handy by allowing you to specify the exact fields you want to load, thus avoiding unnecessary data retrieval.
Microsoft Document:
Record.SetLoadFields([Any,…]) Method – Business Central | Microsoft Learn
Using partial records – Business Central | Microsoft Learn
How to Use SetLoadFields
Version: Available or changed with runtime version 6.0.
Here’s a basic example of how to use SetLoadFields in your AL code:
In the above code, we specify that only the Standard Cost field should be loaded when fetching records from the Item table. This ensures that Business Central only retrieves the data that is actually required, improving the overall performance of the query.
Key Benefits of SetLoadFields
- Reduced Data Load: By limiting the fields that are retrieved, you reduce the load on the database and the network, which leads to faster queries.
- Improved Performance: With less data to process, your AL code will execute faster, especially when dealing with large datasets or tables with many fields.
- More Efficient Use of Resources: Loading only what you need means you’re not wasting system resources fetching unnecessary data, which is especially important in performance-critical applications.
Performance Test: SetLoadFields vs. Without SetLoadFields
Test Setup
In a recent test, I used two procedures to calculate the total Sales (Qty.) and Purchases (Qty.) for items from the Item table (both of which are FlowFields). Here’s what I observed:
- Procedure with SetLoadFields: This method loads only the necessary fields (the keys, system fields, and the fields explicitly defined in
SetLoadFields).- SQL Execution time: 22ms (tested on Cronus demo data)
- SQL query: Simple, querying only the relevant fields and consuming fewer system resources.
- Procedure without SetLoadFields: This method loads all fields by default.
- SQL Execution time: 121ms (same Cronus demo data)
- SQL query: A more complex query fetching almost 150 fields, including a join with the
Item_Exttable extension. This naturally consumed more system resources, affecting the overall performance.
To ensure the test results reflect actual processing loads, you need to perform small steps like restarting the environment to clear any cached data. This helps in ensuring more accurate performance results.
When Should You Use SetLoadFields?
While SetLoadFields can significantly improve performance, it’s important to use it in scenarios where you only need a small number of fields from a table. Some typical use cases include:
- Large tables with many fields, where you only need a few fields for a specific operation.
- Performance-sensitive operations, such as batch jobs or reports, where every millisecond counts.
- Scenarios where you’re working with complex filters or joins and want to minimize the amount of data retrieved.
An interesting observation from the Business Central Telemetry (D365BC Usage – Power BI Report, I will add 1 article related to BC Telemetry later), specifically under the Performance -> Long Running SQL Queries -> SQL Detail section, is that there’s a column titled Explanation. In it, if a SQL query involves selecting more than 10 columns or includes a JOIN statement, the report suggests using SetLoadFields with partial records to optimize performance.
This provides a useful rule of thumb: If a table query involves more than 10 columns or involves complex joins, consider using SetLoadFields to limit the number of fields loaded.
Explanation Measure formula:
Additionally, you should always assess:
- The number of records being processed
- The tables being joined
- The overall system load
Conclusion
In this first edition of Friday Tips, we’ve explored how SetLoadFields can help you write more efficient AL code by optimizing the data retrieval process in Dynamics 365 Business Central. By selectively loading fields, you can avoid unnecessary data transfer, improve performance, and ensure that your custom solutions are as efficient as possible.
Remember to always evaluate the need for loading entire records and consider using SetLoadFields wherever applicable. Happy coding, and stay tuned for more tips next Friday!
