Optimizing Microsoft SQL Server Analysis Services: MDX Optimization Techniques: Caching and Additional Techniques

Let’s look first at creating a cache with a query scope. To do so, we will take the following steps:
 

1. Create the following new query:
 

-- SSP08-1: WITH CACHE Query

WITH CACHE

AS

'([Product].[Product Department].Members)'

SELECT

{[Measures].[Warehouse Sales]} ON COLUMNS,

{[Product].[Product Department].Members} ON ROWS

FROM

Warehouse

2. Execute the query using the Run Query button.
 

The results dataset appears as shown in Figure 3.
 

Figure 3: WITH CACHE Query
 

3. Save the query as SSP08-1.
 

Keep in mind that the “life” of a product of the WITH CACHE statement is only as long as that of the query in which it resides. Use of the WITH CACHE statement can sometimes result in more rapid completion of the overall query, because the full set of cells that we have specified in the statement arrives at the client before the multidimensional data set is returned. In scenarios where the server is accessed via a high speed LAN by the application generating the query, the small performance enhancement may be negligible. By contrast, scenarios where access is over WAN links or modem connections, query results may be very slow in first making an appearance, but will likely require less time to retrieve overall.
 

Now, let’s take a look at the use of the CREATE statement to create a cache in an MDX query. We will create a cache with session scope, as we have already created a cache with query scope above.
 

4. Create the following new query:
 

-- SSP08-2: CREATE CACHE Query
 

CREATE SESSION CACHE FOR Warehouse

AS

'(
 

   Descendants (

      [Time].[1998],

         [Time].[Month]
),

[Warehouse].[Country].Members

)'
 

5. Execute the query using the Run Query button.
 

6. Save the query as SSP08-2.
 

The query creates a cache with session scope; we notice that no measures are specified this time. This is because all the cube’s base measures are loaded into the cache at runtime. The CREATE statement above does not take noticeably more time to execute than would the core query; immediate execution of the query will occur subsequent to cache creation, however, because the query would process in its completeness from RAM, where the cache is housed.

Before we decide to use caching, we need give thought to whether the query we are attempting to improve through caching will actually benefit from means other than redesign of the query itself, and whether the caching process can realistically provide improved performance in general. Obviously, a one-time query is not likely to benefit from caching. In addition, numerous situations will exist where, although a large population of cells are specified in a query, only a few cells are actually accessed; to use caching, for example, in a scenario where only specific tuples out of a large CrossJoin are actually used, may mean more processing time to cache the population of cells than will be saved in performance gains for the few cells actually used. The cache statements are best left to scenarios where their effects are likely to increase performance and where the query actually has a need for tuning from the outset.
 

6. Close the Sample Application.