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

jbech · 2007-03-01

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.

kriki · 2007-03-01

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)

kriki · 2007-03-01

[Topic moved from Navision forum to SQL Performance forum]

Marije_Brummel · 2007-03-04

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.

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

Comments

Categories