Hi, In 3.10 you can indicate which SIFT-levels must be maintained. In my database I have a lot of dimensions, with a lot of dimensions-posting for each receipt,invoice,gen. journal line/… (the biggest one, was 20.000 dimension-records for one big invoice) When I’m looking in the database, a lot of place is eaten up by the SIFT tables. I know I can disable the levels to reduce some space, but has anybody done this before. If i’m disabling keys on what’s the result * for speed (calculating flowfields, calcfields, … * for deadlocks when posting documents, … * … Anyone ?? Thanks in advance Nico
Hi, I remember from navison conference, that if choose SIFT level maintain No then navision will not make calculation in to sift table for these key. But Navision will calculate amount by scaning records when you require it. So maintain No must increase speed for posting and decrease speed for analysing. Proposal from navision was to use it on not very change key values. It’s teoretical because i not used it:)))
From the help text: MaintainSIFTIndex Applies to Keys This property determines whether SIFT structures should be created (when set to Yes) or dropped (when set to No) in SQL Server to support the corresponding SumIndexFields for the Navision Attain key. SumIndexFields are created in Navision Attain to support, for example, FlowField calculations and other fast summing operations. SQL Server can sum numeric data by scanning the table. If the SIFT structures exist for the SumIndexFields, summing the fields will be faster, especially for large sets of records, but modifications to the table will be slower since the SIFT structures must also be maintained. In situations where SumIndexFields must be created on a key to allow FlowField calculations, but the calculations are performed infrequently or on small sets of data, you can disable this property to prevent slow modifications to the table. SIFTLevelsToMaintain Use this property to specify which SIFT levels are maintained for a key. Applies to Keys Comments Applies only to the Microsoft SQL Server Option for Navision Attain. For more information about SIFT in the SQL Server Option for Navision Attain, see the Application Designer’s Guide. Lars Strøm Valsted ------------------------- Why can’t programmers tell the difference between Christmas and Halloween? Because OCT(31) = DEC(25)
The SIFTLevelsToMaintain property allows you to determine the combinations maintained by the SIFT table correpsonding to the key you are looking at. This is provided since although all fields in the key may be required (presumably they are) for either filtering or sorting, they are often not all needed for SIFT summing. Those that are not needed can be disabled using this property. This will increase update time of the table (since all levels of the SIFT table must also be updated) and perhaps increase concurrency (depending on your system) since this can be reduced by the need to update locked SIFT levels in the SIFT tables. Summing will be just as fast provided that you have not removed a level for a dimension that you wish to sum for. For example, removing a Location level from a key where your application is still performing sums by Location will mean a base table sum will be done. This is a simple explanation but it can be tricky to remove levels since you must know your key usage in the application very well. It can be easy to find areas where you have positively improved update performance without a summing penalty, but you may be unaware of areas which do have a summing penalty because of the change.
When working with big tables (5.000.000 and more record) and lots of key, the SIFT can dramaticly degrade performance. This is specialy true for SQL option. Bostjan