Common mistakes we make in C/AL - Vol. 3 - Tips, Tricks & Facts #8

In this post I will talk about Significance of Using CALCSUMS in C/AL coding. 

Before writing about this topic I would mention here that, if a tool (or function) is available then we must use it to our system's benefit. Tools or functions are available in AL (or any other development envo.) to reduce the time taken by transactions and faster output generation.

You can find the detailed definition of CALCSUMS on MSDN. This function must be used to calculate sums of a Decimal field. However, the scope of CALCSUMS has been widened to Integer, BigInteger and Duration type fields along with Decimal type fields from NAV 2016 and up.

The mistake that we must not make being an efficient developer is using REPEAT..UNTIL instead of CALCSUMS. Now, this depends majorly on the scenario as well. Use CALCSUMS if possible instead of directly using a REPEAT..UNTIL to calculate a sum, design the table to which the field belongs to and check if this field is present in SumIndexFields in any of the keys which already exist in the table.

• If the field for which you want to calculate the sum is present in SumIndexFields in any of the keys in that table then you must not use REPEAT..UNTIL.
• Vice versa, if the field for which you want to calculate the sum is not present in SumIndexFields in any of the keys in that table then you may use REPEAT..UNTIL. 

NOTE: Creating a key specifically and then adding a field in SumIndexField for calculating sum of this field might not be a good choice.

Only if the number of records are too huge and you want the output to come faster then you must create a specific key and add a field in SumIndexField. Else you may use a REPEAT..UNTIL instead if the records you want the sum from are in hundreds or below a thousand.

Creating a key and using SumIndexField puts load on SQL database because SQL has to maintain an index. You must put this load only if it is necessary and your decision must be based on both the system's and user's perspective. 

Most probably, creating a new key will not be required because either the key will already be there in the particular table and this field might already be present in the SumIndexFields. OR you can add this field for which you need the sum in the SumIndexFields in one of the existing keys. Adding fields in SumIndexFields maintains index in SQL. 

Points to NOTE: 

• The execution of CALCSUMS results in much faster output because SQL is not required to return every record to NAV for calculating a sum. Approx. half a second with CALCSUMS as compared to 5-8 seconds with a REPEAT..UNTIL.
• You need to decide if its worth maintaining an index in SQL to calculate the sum because indexes slow down writes to the database.

You can try comparing with 2 examples using CALCSUMS and REPEAT..UNTIL resp. and use SQL Profiler to see the results.

All you need to do is check the SumIndexFields present in the table once before using a REPEAT..UNTIL :)

Keep Learning!
Don't forget to share your thoughts below.