- About the Series
- Introduction to Performance Tuning
- Understanding How Queries Are Processed
- Types of Intervention
- Large Level Threshold Property
- Location of Query Processing
- Optimization of Set Operations
- Importance of Optimal Arrangement
- Expression Arrangement
- Caching and Optimization
- Other Performance Enhancement Options
- A Parting Word
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Caching and Optimization
When queries are not well-underpinned by aggregates, we can often enhance performance by creating and caching the appropriate aggregates in memory. Caching is a feature that MDX provides to improve performance, in which we are given the capability of loading a commonly used slice of a cube into memory and caching it for faster retrieval by our queries.
Analysis Services and the PivotTable Service automatically cache query definitions, data, and metadata on the server and client sides, respectively. This caching increases performance in those cases when queries are repeatedly requesting the same data or metadata, reducing network traffic or execution time. The capability to create caches for data that we specify in MDX gives us another means of fine-tuning query performance; through this capability we realize a great degree of control over the caching of data for which we expect there to be a recurring need.
In terms of creation scope, caches are similar to named sets: We can create a cache for the lifetime of a single query or for a session. To create a cache to be used at the session level, the CREATE CACHE statement can be used. The CREATE CACHE statement can be used to create caches at the query level, but the WITH statement can perform this task just as easily and is more frequently used for this purpose.
Let's take a look at the use of the WITH statement to create a cache in an MDX query:
Create the following new query:
-- Step 4-1: WITH CACHE Query WITH CACHE AS '([Product].[Product Department].Members)' SELECT {[Measures].[Warehouse Sales]} ON COLUMNS, {[Product].[Product Department].Members} ON ROWS FROM WarehouseExecute the query using the Run Query button.
The results set appears, as shown in Figure 12.
Figure 12 WITH CACHE QuerySave the query as Step4-1.
)
Figure 12Keep in mind that the "life" of the WITH CACHE statement is only as long as 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 in which the server is accessed via a high-speed LAN by the application generating the query, the small performance enhancement may be negligible. In scenarios in which access is over WAN links or modem connections, however, query results may be retarded 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 because we have already created a cache with query scope.
Create the following new query:
-- Step 4-2: CREATE CACHE Query CREATE SESSION CACHE FOR Warehouse AS '( Descendants ( [Time].[1998], [Time].[Month] ), [Warehouse].[Country].Members )'
Execute the query using the Run Query button.
Save the query as Step4-2.
The query creates a cache with session scope; 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 does not take noticeably more time to execute than the core query would. 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 to 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 in which only a few cells are actually accessed, although a large population of cells is specified in a query. To use caching, for example, in a scenario in which 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 in which their effects are likely to increase performance and in which the query actually has a need for tuning from the outset.
Close the sample application.