Hello, We are running Navision Attain 3.01 SQL option and are having trouble with system performance. The system has been customized by external consulting firm and it appears that their code is what causes the trouble. Incidentally there is a table 50009 with 3000 rows in it and in SQL one can see a table 50009$0 with more than 1300000 (sic!) rows. This table is referred to by a flowfield in table 27 and CALCSUMS takes about 500 ms per row. Is there anything we can do to remedy the situation? Konstantin
What ist the purpose of table 50009 and of the flow-field in table 27 ?
The purpose of table 50009 is to store the quantity of item in sales orders (SalesOrder No., Item No., Quantity) for further reference by operators entering sales orders.
I checked just now and found that the same situation is with table 37. That is we have 14000 rows in it and over 3400000 rows in table 37$0.
The $0, $1 etc tables are SIFT table used for maintaining the SumIndexFields set up on some of your table’s keys. They are modified by INSERT/UPDATE/DELETE trigger on the base table. It seems that you have had a lot of update activity for them to be so large, with such a small amount of data in the base tables. You can rebuild these SIFT tables by Optimizing the keys that they are based on - so identify the keys on table 27 that have SumIndexFields, then Optimize from Database Information/Tables window. If there are many records not needed (i.e. their sums yield 0) they will be removed. In general, you should aim to keep the number of fields in SumIndexFields to a minimum. In 3.01 there is only an option to disable the entire SIFT table for a SumIndexField key, but in 3.10 and above you can configure the combination of fields that you want to maintain in the SIFT table, therefore reducing its size but still providing fast summing. To do this you need to have good knowledge of the use of the SumIndexField.
It did work, thank you. After optimization SIFT table size has become comparable with parent table size and CALCSUMS takes now about 15 ms which is perfect. We shall include optimization in regular database maintainance procedures.
Hi, Always CALCSUM with SIFT technology is faster than Using REPEAT <> UNTIL statement for Manual, Thanks