Performancetuning of detailed xx tables

After upgrading our system from version 2.6 to version 3.6 (sorry our supplier is behind on versions), we expierienced very poor performance on our system, most often when displaying customer entries, where the lines slowly rolled down the screen - loading less than 3 records per second. Did a lot of SQL tracing on our server ( Dual Xeon MP 3,0 Ghz, 4 GB memory, 8 disks for logfiles and 24 for database devices), and found that the SQL server did a lot of work calculating values. Read and re-read the application designer guide, and found that the indexes was not designed properly. The worse performance on table lookups were on tables 379 (Detailed Cust. Ledg. Entry), 380 (Detailed Vendor Ledg. Entry). Did a lot of testing on the keys on the tables and optimized the performance by modifying the second key of the indexes ie. key “Cust. Ledger Entry No.,Posting Date”. On the property page of the index set up the following: Enabled Key <Cust. Ledger Entry No.,Posting Date> SumFieldIndex <Amount,Amount (LCY),Debit Amount,Credit Amount,Debit Amount (LCY),Credit Amount (LCY)> KeyGroups <> BackupKey MaintainSQLIndex MaintainSIFTIndex SIFTLevelsToMaintain <{Cust. Ledger Entry No.}> (no need for further) Close, save and the server will create the indexes/keys In out situation this speeded up the performance on displaying customer postings several 1000% (we are having millions and millions of customer postings) According to our solution provider, this index is default MBS, and if so, all of you should have a benefit of applying this change on the detailed indexes. Best regards…

That’s really strange. What about the Cust. Ledger Entry table, was it changed? Maybe some fields were added or some of the existing FlowFields were changed.

2.60 → 3.70 = Detailed Ledgers !!

No, David, what I am eager to know is if there are any changes to Table 21 between the objects they are running on and the standard 3.60. So, basically, if their NSC modified or expanded Cust. Ledger Entries for this implementation.

Check SIFT tables for full scan. After table keys modification, in some situations, SIFT have empty statistics. “Update statistics” can’t create statistics. Use “reindex” on SIFT tables for statistics maintance.