SSAS (Sql Server Analysis Services) UPDATE performance with multiple update statements in a transaction

128 Views Asked by At

Our business logic involves sending multiple MDX UPDATE statements to SSAS in a single transaction. Each update statement typically has 600-800 cells that are update. All of these updates touch the same set of measure groups within the same partition. MOLAP partition is being used.
The first update statement takes less than 2 seconds, but the time taken for each successive update statement progressively increases and for the 100th update statements, it takes as long as 41 seconds!
(If I take the last update statement that took 41 seconds and run it a new transaction, it takes less than 2 seconds again)
Any pointers to reduce this performance?
- I've already tried increasing the Total Memory Limit on SSAS properties, but there is no improvement.
- The business logic doesn't permit doing corresponding updates on the relational db directly, as the user should have ability to Rollback the changes.
- The number of UPDATE statements depend on user interaction, and hence we can't use any solution that optimizes perf for multiple update statements while reducing the performance for a single update.

0

There are 0 best solutions below