i have this situation, that i did a calcsum on a table filtered down to 100 records. the calcsum function takes 700 miliseconds. when i changed the coding to use a sequential totalling formula on the same table with the same amount of filtered records, it takes navision 50 miliseconds. similarlay, when i tried to do a calcsum on a table with 350,000 records, the calcsum function takes 4 seconds. when i changed the coding to use a sequential totalling formula on the same table with the same amount of filtered records, it takes ages.
As a first guess, I would assume that the client DBMS cache plays an important role here. 100 records probably fit into the client cache completely, thus speeding up the sequential calculation dramatically. 350,000 records have to be retrieved into the client in several (read: many) chunks, which of course slows things down by orders of magnitude. A CALCSUMS uses internal routines and data structures, avoiding the overhead of your C/AL calculation loop.
In fact a CALCSUM only has to do a very small number of reads no matter what. I believe the number of required reads would be either the same or almost the same no matter how many records are in the included range. (I agree with xorph, am just elaborating).
David, this is an interesting statement of yours. How would CALCSUMS calculate a field sum over a range of records without examining each record at least once? I’m not familiar with the in-depth details of Navision’s routines and data structures, so could you please provide some more information? Does Navision’s “SIFT”-technology (whatever this is [:)]) come into play here?
Yup… navision technology is exactly what it’s coming into play. I’ve optimized speed working on some databases just by replacing loops with calcsums. It really improves the speed when talking about using lots of records… sometimes the differences are not just a matter of miliseconds, but more than 30 seconds…
As i recall, Navision maintains some kind of accumulated sum each time a record is inserted in a file with a sift-index, which would be the reason for the small no. of reads. The documentation used to say something about this, at least in earlier editions.
so does that mean a sequential algorithm to add up a small series of records, is faster than using calcsums to add up a small series of records…?
There is a document called “Navision Database Whitepaper” (or something similar) which explains at least briefly the SIFT technology… as we all know, Navision DB doesn’t maintain special tables with the balances as other financial and accouting software but maintains these values “hidden” within the same tables and therefore calculating a balance, even with thousands of registers is a matter of two database reads on the harddisk. Regarding the CALCSUM and sequential adding with few records, it might be related to the fact that these two harddisk reads (first record, last record) of CALCSUM take more time than accumulating the records sequentially, as the records are already cached locally on the machine, and there is no networktraffic involved… just a guess. Saludos Nils
Just a thought (maybe a silly one). It depends how you did your testing. I believe that when running either of the options something gets cached. To be sure that you get an accurate reading on run times you need to run one of the routines then exit completely then get out and after that get back in and run the second. Also you did not specify I belive if you were using SQL or regular Navision and what version you were using to test. Cristi
Read’s are related to: Sequential read - to count of rec’s, it’s simple CALCSUM - to key and used filters. Sum Index field stored in key. So if we have key: A,B,C,D with SumIndex S. Using filter on A → count of read’s is eq to A combinations in filter. Using filter on D → to A,B,C,D combinations counts where D field is in filter. Exaple: rec’s: [1,2,3,4],[1,3,3,4],[2,3,3,4],[5,5,5,5] 1.Then using filter on A=1 → 1 read [1,,,*] 2.Then using filter on D = 4 → 3 reads [1,2,3,4],[1,3,3,4],[2,3,3,4] So comparing seq.read and CALCSUM: 1. 2 reads in seq. and 1 in calcsum 2. 3 and 3 Cache hit’s speeding up both types of summing.
the test is done on a SQL Server. and for every time, it is tested, i restart navision. so i think cache does not play a part here.