MaintainSIFTIndex in primary key

BorisK

The customer runs 3.70SQL version with approx. 50 users logged in on average at a time, database size fast approaching 100Gb. Some time ago they suddenly complained that one of bespoke routines (exporting Sales Invoices) runs very slowly. I debugged the code and discovered that it takes almost a minute to perform a line of code which simply CALCFIELDS "Sales Invoice Header".Amount and Amount Inc. VAT. I ticked MaintainSIFTIndex in the primary key in "Sales Invoice Line" and it seems to have vastly improved speed of the routine in question. However soon after that they also started complaining about excessive user blockings although the blocking object is mostly G/L Entry. Could be just a coincidence but can any of the SQL boffins explain to me:
1) Could my change slow down any Sales Invoice posting procedures to such extent that different user sessions start blocking each other?
2) If MaintainSIFTIndex is not ticked as standard and therefore there is no SIFT Tables for $113 - how does NAV calculate Amount flowfields in Sales Invoice Header?

Thanks in advance

garak

Hello,

The "MaintainSIFTIndex" property is used for SQL Server to simulate the native FlowField technology.
If you set the MaintainSiftIndex prpoerty to yes, NAV creates on the SQL server separate tables like Companyname$37$1, Companyname$37$2.
These tables are only created if you have SumIndexFields and the MaintainSQLIndex is activated. I don't know if under 3.70 you can enable / disable the SQLSumIndexBuckets <- here you can define, wich "bucket depp" the SQL Server should handle.

So if you activate the MaintainSumIndex and you have Sumindexfields, Nav. must write his datas for on Rec on "Sales Line" into the SIFT tables ($37$1 ...).
But if you often calculate this SiftFields there is no problem / but the write transaction could be a little bit longer and bring blocks. If you not have enabled MaintainSiftIndex but you have Sumindexfield Nav calculates the FlowFields directly on base table (here 37).

Example: You need to calculate Amount on Sales Line and you doesn't have enables MaintainSift.

select sum(Amount) from CompanyName$Sales Line where CONDITION

so, that can bring a bad perfomance when datas are reading and there are many recs.
If you have enables SIFT

select sum(s12) from CompanyName$37$1 where CONDITION

<- this is only an example, it's not the right field (s12)
so here nav must only read one rec (or less than directly from sales line). But if you have to many Sifts and Maintain, it's bad for write transactions (insert / update) when you have many recs in your table and these recs are often modified / inserted. You must find the best middle way.

Finally, you must take a look into your Maintain and SIFT index and bucket strukture to see which SIFT tables you need / not need. (on small tables it's no problem to disable MainTainSift).

Regards

stryk

Hi!

The trick with optimizing SIFT is to find the right balance between Read and Write performance. As garak said, SIFT means, that aggregated values - sums - are written into dedicated tables. Thus, writing this SIFT data is costly, reducing the Write performance (aka "Cost Per Record" CPR). But if no (aggregated) SIFT information is available, sums have to be calculated on basis of the original table, e.g. T113 "Sales Invoice Line" - in this case Read performance could be decreased.

Hence, regarding writing the ideal case would be to have no SIFT at all (= no additional CPR). Once you encounter problems here (reading), you should enable SIFT. But by default too many "SIFT buckets" are enabled, thus too much CPR are created: this is slowing down writing data, and causing blocking conflicts. To optimize the SIFT structure you should only enable only the bucket next to the "Primary Key" level - here I gave a recent example: http://www.mibuso.com/forum/viewtopic.php?f=34&t=29145 (or follow the links to my BLOG)

If this is not sufficient enough, then further steps could be taken, like enabling another "SIFT bucket" (NAV feature) or working with "Covering Indexes" (SQL feature).
With SQL 2005 - which you don't run, I suppose - you could also use "Included Columns" with specific indexes to improve the performance.

Hope this helps you!
Best regards,
Jörg

BorisK

Thanks guys!

I think I follow you...

garak

we're sure ;-)
read also the blog of waldo or stryk and / or read the posts in this subforum.

Regards