Is MDX query slow? Here are four guidelines for improving performance

The key reason for implementing OLAP databases is performance related to data retrieval and what if the wrote query does not offer expected performance? In a way, troubleshooting MDX query is not as easy as troubleshooting TSQL, if you are not much familiar with the concepts related to MDX statements. However, you can keep following guidelines in mind and do the needful if you experience slow performance with written MDX statement.

Guidelines are based on the time spent on Query Processor and Storage Engine. Duration can be determined using following events with Profiler;

• Query Subcube event - Duration column - for time spent on extracting data from the storage.
• Serialize Results End event - Duration column - for time spent on manipulating data after taken from the storage.

Here are four guidelines;

1. If you see more time on Query  Serialize Results End event - Duration (Processing) than Subcube event - Duration (Storage), you need to optimize the MDX statement reducing added calculation and complexity.
2. If you see more time on Query  Subcube event - Duration (Storage) than Serialize Results End event - Duration  (Processing), it shows that it needs partitioning, user-defined hierarchies and attribute relationship among used attributes defined.
3. If you see more time on Query  Subcube event - Duration (Storage) than Serialize Results End event - Duration  (Processing), and data is retrieval rarely from aggregation, it needs aggregation added based on usage.
4. If you see more time on Query  Subcube event - Duration (Storage) than Serialize Results End event - Duration  (Processing) and data is retieved from the cache, you need to check the resource usage of the server and if required, scale up by adding memory, CPU, etc.

Await for next post on how to use the Profiler for capturing these for troubleshooting.