CALCSUMS, and SIFT, and Addition! Oh My!
In previous versions of Microsoft Dynamics NAV, if you wanted to add Decimal amounts, you had to either have a SumIndexField defined at the key level so you could use CALCSUM, or you would have to loop through each row and keep a running total. CALCSUMS behavior has been changed in Dynamics NAV 2013 and up which makes the record looping obsolete.
I ran across this on MSDN:
“For Microsoft Dynamics NAV 2015, CALCSUMS execution is decoupled from Microsoft Dynamics NAV SIFT index definitions. This means that if any of the conditions for using SIFT indexes are not true, then Microsoft Dynamics NAV traverses all records in the base table to perform the calculation instead of using SIFT. This can reduce the number of required SIFT indexes, which can improve performance.”
Source: https://msdn.microsoft.com/en-us/library/dd301396(v=nav.80).aspx
Hmmmmm…. what does that mean? Because lower in the definition, it also says:
“If you omit this optional return value and if one of the fields is not a SumIndexField, a run-time error occurs. If you include a return value, you must handle any errors.”
Source: https://msdn.microsoft.com/en-us/library/dd301396.aspx
This makes sense because previous to NAV 2013, you would see a runtime error if you tried to use CALCSUMS without a SumIndexField defined on a field you were trying to filter.
Figure 1 – Error when using CALCSUMS without a SumIndexField in NAV versions prior to NAV 2013
How could CALCSUMS be decoupled from SIFT but also cause a run-time error if the field wasn’t a SumIndexField? I decided to try and find out what this meant.
I created a table and codeunit that I used to generate the queries. It should work in NAV 2009 and up. Click here to link to the table and codeunit.
I used a Table called “CalcSums Test” and a Codeunit called “CalcSums Test”. The table will be populated with a million rows of test data and by default doesn’t have the amount column I’m summing indexed.
The Codeunit contains the following code:
SELECTLATESTVERSION;
CLEAR(CalcSumsTest);
//CalcSumsTest.SETCURRENTKEY("Value 1");
CalcSumsTest.SETFILTER("Value 1",'EVEN');
CalcSumsTest.CALCSUMS(Amount);
Amount := CalcSumsTest.Amount;
MESSAGE('Amount 1: %1',Amount);
SELECTLATESTVERSION;
Amount := 0;
CLEAR(CalcSumsTest);
CalcSumsTest.SETFILTER("Value 1",'ODD');
IF CalcSumsTest.FINDSET THEN BEGIN
REPEAT
Amount := Amount + CalcSumsTest.Amount;
UNTIL CalcSumsTest.NEXT = 0;
END;
MESSAGE('Amount 2: %1',Amount);This is the result of the above CAL code in SQL Profiler:
Figure 2 – Result of CAL code in SQL Profiler
#1 generated the following SQL:
SELECT SUM("Amount") FROM "Demo Database NAV (7-1)".dbo."CRONUS USA, Inc_$CalcSums Test" WITH(READUNCOMMITTED) WHERE ("Value 1"=@0) OPTION(OPTIMIZE FOR UNKNOWN)#2 generated the following SQL:
SELECT "timestamp","Entry No_","Amount","Value 1","Value 2","Value 3","Value 4","Value 5","Value 6","Value 7","Value 8","Value 9","Value 10","Text 11","Text 12","Text 13","Text 14","Text 15" FROM "Demo Database NAV (7-1)".dbo."CRONUS USA, Inc_$CalcSums Test" WITH(READUNCOMMITTED) WHERE ("Value 1"=@0) ORDER BY "Entry No_" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)What we see in Query number 1 is that it is dramatically faster with our test data than looping through the table. Although the Page reads are still very high, the RowCounts has dropped (so while it hits every row, it’s not returning every row) and the CPU/Duration is about half a second versus almost 7.5 for the other query. The big bonus? No runtime error like we’d get in NAV 2009.
Let’s see what happens to the query when we index “Value 1” and add a SumIndexField.
Figure 3 – Adding a SumIndexField to the query
Figure 4 – Result of new query
Once again, a dramatic improvement. The Duration, CPU, Reads, and RowCounts all drop to low values. The trade off: You have to maintain an index, which will slow down your write performance. You’ll have to decide if it’s worth it to maintain an index. One thing you won’t have to do is write row looping code to sum decimal values.
Thanks to colleague Todd Tipton for his feedback on this blog posting.
If you are interested in NAV development, check out our collection of NAV Development Blogs.
For step-by-step instructions on how to perform specific tasks in Microsoft Dynamics NAV, see our collection of How-To blogs.