Please refer to Part I of the blog: How to verify that your query is actually using the physical partition?
In the Part I blog, our focus was to identify the clues and confirm that the BW query was indeed reading from the right partition.
In this second part of the blog, I would like to share some of my experiences with identifying the performance bottlenecks of the BW Query.
In general, below are a few parameters that we take into consideration for query performance optimization for high database time.
- Details on distribution of data across D and F/E tables – helps decision making on ‘Line dimension’
- Details on summarization ratio – helps decision making on ‘Aggregates’
In addition to above, Details on SQL Execution plan to identify the ‘Cost’ly reads should also be considered.
Always, it is a must to dig deeper and pin point the exact root cause for slow running queries. The Statistics tool gives us a good guidance on the problematic areas to start with, but, we often jump to conclusions and proceed with the performance tuning corrective actions without knowing the real root cause.
Therefore, it is required to take a step back, have a re-look at the query design and all the tables involved in the SQL joins.
To continue from where I left my previous blog, I completed the cube partitioning and was also able to confirm that partition pruning was happening and the query was reading from the right partition as desired. But, I was not able to find appreciable improvement in the response times. I missed to note something obvious – here it is.
If you focus on the database reads in this execution plan, it indicates reads on the dimension tables, Fact tables and master data tables.
This one is the execution plan based on Cost based optimizer – so check out the records hit and the cost calculated.
All reads until the Fact table (circled in the screenshot) seems to be okay -but when you look at the database (/BIC/XINFOOBJECT) which is hit soon after the Fact table, it is really scary. Please refer to the red bold records. You see 18 million records hit, 200 times more than your Fact table itself.
More than partitioning the Fact table, my real concern should have been this master data read on /BIC/XINFOOBJECT.
My query was using a Navigational attribute of the XINFOOBJECT and so was hitting the X table. I compared the size of the X table vs. the size of the dimension table that carried the same field and to my rescue, the latter was far better.
So, I replaced the Navigational attribute in the query with the characteristics from the cube. Fortunately, this replacement was okay, as per business logic, for my reporting requirement and so there was not any impact replacing navigational attribute by characteristics.
Now, I came back to my execution plan and could see that the count of records hit and the total cost got drastically reduced.
Finally, the query response was a lot faster.
Had this replacement not been feasible (for whatever reasons), there wouldn’t have been a way out. Unfortunately, there is not much performance tuning that you can do with master data table reads – you can check for degenerated index, if any, on master data and try to repair the index and update the statistics. Also, you can purge unwanted/historical master data from the master data table – if that is not in use and approved by your business.
With this big a table in the schema, there is not much of response gain that I can dream of – doing the cube partition. Basically, the effect gets nullified. The real value of range partitioning started to unfold only after this navigational attribute was removed from the query.