|Tuning Queries with the WITH CACHE Statement|
|Written by Chris Webb|
|Tuesday, 12 June 2012 21:00|
Reposted from Chris Webb's blog with the author's permission.
One of the side-effects of the irritating limitations that SSRS places on the MDX you can use in your reports is the widespread use of calculated measures to get the columns you want. For example, a query like this (note, this query isn’t on the Adventure Works cube but on a simpler cube built on the Adventure Works DW database):
…which wouldn’t be allowed in SSRS, could be rewritten like so:
…to get it in an SSRS-friendly format with only measures on columns.
For the last few days I’ve had the pleasure of working with Bob Duffy (a man so frighteningly intelligent he’s not only an SSAS Maestro but a SQL Server MCM as well) on tuning a SSRS report like this on a fairly large cube. As Bob found, the problem with this style of query is that it isn’t all that efficient: if you look in Profiler at what happens on a cold cache, you can see there are seven separate Query Subcube events and seven separate partition scans (indicated by the Progress Report Begin/End events) for each calculated measure on columns.
The first thing that Bob tried to tune this was to rewrite the query something like this:
…and pivot the data in the SSRS tablix to get the desired layout with the Day Numbers on columns. The interesting thing, though, is that for this particular report while rewriting the query in this way made it run faster (there is only one Query Subcube event and partition scan now) it actually made the SSRS report run slower overall, simply because SSRS was taking a long time to pivot the values.
Instead, together we came up with a way to tune the original query using the WITH CACHE statement like so:
What WITH CACHE statement does here is load all the data needed for the query into the Storage Engine cache before anything else happens. So even though there are still seven different Query Subcube events for each column, there’s now only one partition scan and each of the seven Query Subcube events now hits cache:
There’s no guarantee that this approach will result in the best performance even when you have a query in this form, but it’s worth testing if you have. It’s certainly the first time in a long while that I’ve used the WITH CACHE statement in the real world – so it’s interesting from an MDX point of view too.
Latest Author Articles
- A New Events-In-Progress DAX Pattern
- Analysis Services Multidimensional Now Works With Power View-And Why That's Important
- Sharing Data From Excel as OData with FlatMerge
- Aggregating the Result of an MDX Calculation Using Scoped Assignments
- Using Scoped Assignments To Show Calendar and Financial Year-To-Date Values In The Same Calculated Measure