Analysis View: s-l-o-w update on SQL Server 2000

jbechjbech Member Posts: 2
edited 2007-03-04 in SQL Performance
One of my clients (running Navision Attain 3.60 on SQL Server 2000) reported extremely slow update of their analysis view after adding a new dimension. Their database contains only some 90,000 G/L Entries but after adding the new dimension code and confirmed deletion of existing analysis views, a renewed update took roughly 20 hours (speed: 1 record/sec)!

I have made tests on a local SQL Server based on a Navision Backup from the client. This revealed that updating G/L Entries ran as expected but when it came to G/L Budget entries, the speed slowed down to 2 records/sec.

Tests on a native Navision Attain ran smoothly so the problem is exclusively related to SQL Server.

We have tried updating stats, recreating indexes and truncating the transaction log with no apparent effect.

Any hints would be greatly appreciated.


  • krikikriki Member, Moderator Posts: 9,076
    Tables :
    -365:"Analysis View Entry"
    -366:"Analysis View Budget Entry"

    You need to maintain less SIFTLevels on the tables:
    Try keeping only levels on the following levels:
    -the level ending with "Posting Date",
    -the level ending with "Dimension 4 Code" (dimension 3 if you don't have dimension 4)
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • krikikriki Member, Moderator Posts: 9,076
    [Topic moved from Navision forum to SQL Performance forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Try to have a look at the code executed. NAV Native is still way quicker when it comes to transactions.

    Have you tought of beaking into smaller transactions instead of one huge?

    Be carefull with the sifts since you don;t want to kill your reporting performance by waiting for matrixforms to populate.

    You might also want to consider moving to SSAS for your reporting needs.
Sign In or Register to comment.