CALCFIELDS vs. SETAUTOCALCFIELDS

This week, one of our customers reported a performance problem with Dynamics NAV 2013 R2. The symptoms were an unresponsive system and users unable to login. After some investigation, it turned out that the server was using serious memory: up to 2500 MB. While only 6 users were logged in. Normal memory usage should be about 300 MB.

Soon it turned out that each time the memory usage was increasing, one of the users was running a specific report. This report iterates over the Item table and calculates some quantities, like Inventory, Qty. on Purch. Order, etc. There were about 120.000 items in the item table. Maybe more records than with a regular customer, but not an extraordinary number of records in my opinion. The user who started the report had to wait for more then 15 minutes before the report was finished.

In the report, I noticed a CALCFIELDS statement in the OnAfterGetRecord trigger with 5 FlowFields. And each FlowField results in a separate query. So in this case 600.000 extra database calls!

I changed the CALCFIELDS to a SETAUTOCALCFIELDS in the OnPreDataItem. The results were impressive: the report now needed about 300 MB extra on the server side and finished in 5 minutes.

Of course I could also set the CalcFields property on the DataItem. Nevertheless, the SETAUTOCALCFIELDS is one of the improvements in NAV 2013 you should make use of. Any REPEAT … UNTIL loop that contains CALCFIELDS within the loop can benefit from this function. Just set a SETAUTOCALCFIELDS before the FINDSET and all FlowFields you have defined will be calculated with one SELECT command.

How does that work? Very simple: the SETAUTOCALCFIELDS automatically updates the SELECT query with JOINS for each FlowField. So one query with all your FlowFields calculated.

Happy coding!