In this post I´ll show seven basic tips for increasing performance when coding in Business Central.
Some of them are well-known as they have been part of C/AL for a long time. And others like partial records were introduced recently.
At the end of the post, you will find three Youtube links in which you can dig into more detail about the topics presented.
- FINDSET VS FINDFIRST
- PARTIAL RECORDS. SETLOADFIELDS
- SETAUTOCALCFIELDS
- CALCSUMS
- LAZY EVALUATION
- ISEMPTY
- SETCURRENTKEY
FINDSET VS FINDFIRST
Use FindSet instead of FindFirst or FindLast when you wish to loop through a series of records.
Also, use FindSet(true) when you wish to modify any record in the set, otherwise use FindSet(false).
Only use FindFirst() when you wish to retrieve one record and a loop isn´t needed.
PARTIAL RECORDS. SETLOADFIELDS
SetLoadFields only loads the fields included. Not retrieving the rest of the fields in the table and providing a faster load. Use it to look for specific values as in the example below.
Don´t use it if you need to perform a modify, insert, delete, rename, or transferfield of the record as all fields are needed to perform the operations. This will decrease performance and will be counter-productive.
SETAUTOCALCFIELDS
You can improve performance by using the SetAutoCalcFields function before looping through records with FlowFields instead of calling CalcFields on each record in the loop.
https://docs.microsoft.com/en-us/dynamics-nav/setautocalcfields-function–record-
CALCSUMS
Use CalcSums instead of looping through all the records in the table to perform the sum of a specific field or fields. In this example, both pieces of code achieve the same result but the last one is better for performance.
LAZY EVALUATION
The ‘lazy evaluation’ is a strategy that holds the evaluation of an expression until its value is needed. This method is not applicable in AL.
The idea is that when we need to evaluate two conditions, always place the fastest first and separate from the other. This way the more intensive operation isn´t executed if it´s not needed.
You will find more information about it in Stefano´s webinar at the link at the end.
ISEMPTY
Use IsEmpty if you just want to know if a record exists but don´t need to know any values.
For example, when using DeleteAll, even if there is nothing to delete the system will still perform a lock. So it´s better to first check if it´s empty and avoid the lock when possible.
SETCURRENTKEY
Selects a key for the table. It sorts the data of this specific table and allows better performance for large datasets.
Links
Stefano´s webinar at DynamicsCon. I strongly recommend checking it as it has several tips not only for coding but for codeunits, pages, etc.
https://www.youtube.com/watch?v=AwWWUiM1g8Y&t=1655s
Tomas´s webinar at DynamicsCon. Very interesting as he focuses on data structure, keys, performance tests, etc.
https://www.youtube.com/watch?v=g5tigd1TOhU&t=2395s
Business Central Channel. Overall good video for performance tips.
https://www.youtube.com/watch?v=VN7V4GyULtY
Hope you find it useful.
Hi Erik
Thanks for a great post among many others.
One feed-back on the statement “Only use FindFirst() when you wish to retrieve one record and a loop isn´t needed.” related to “FINDSET VS FINDFIRST”.
I believe this statement should be updated to “Only use FindFirst() when you wish to retrieve one record, specificly the first record or a loop where the order is important.
Thanks for the kind comment.
I will still use FindSet even if the order is important. You can order by any key and then perform the loop. However, I agree that FindFirst will especificly retrieve the first record.