- 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
Importance of Optimal Arrangement
As we have seen, queries that contain levels whose population of members exceeds the large level parameter are processed by the server, and queries with levels whose members do not exceed the large level threshold in number are within the processing capabilities of the client-based PivotTable Service operation. The manner in which the PivotTable Service processes queries can become a consideration within the scope of the way it determines location of processing. The service acts in such a way that each set (and every combination existing therein) defined in the query is fully incarnated in memory before proceeding with operations. The demands on resources can be crippling on even the most robust machines.
Let's consider an example:
Create the following new query:
-- Step 2-1: Pre-optimized Set Operation SELECT {[Measures].[Units Shipped]} ON COLUMNS, TopCount ( CrossJoin( [Warehouse].[City].Members, [Product].[Product Name].Members ), 7, [Measures].[Units Shipped] ) ON ROWS FROM Warehouse
We have expanded our existing query well beyond its last incarnation, adding another dimension and a CrossJoin to enhance it to meet (as an illustration) a specific need of an information consumer. We know that the query will be evaluated on the client if the population of the [Product].[Product Name] level is less than the large level threshold (1,000 default).
An important fact to remember is that the threshold refers to the level's members; it does not refer to the tuples that exist within a given set within a query. Say our large level threshold has been set at 2200. We already know that we have 1560 named products (from the count we did previously); we also have 23 warehouse cities. While the "memberships" of both levels fall well below the threshold of 2200 and thus qualify for client processing, and while our result set is the top seven city-product tuples from the perspective of units shipped, we have far more in the way of resource requirements with which to contend from the scope of our query.
Our query will assemble over 35,000 combinations (1560 x 23)—all to arrive at the small result set that we obtain in the next step.
Execute the query using the Run Query button.
The results set appears, as shown in Figure 8.
Figure 8 Results set, crossjoin query.Our query executes in a matter of moments, even though it is completing myriad more steps than we may have considered. But things might have turned out quite differently, within the scope of the realities of similar—but much larger—scenarios that we might have encountered in the real world.
Save the query as Step2-1.
)
Figure 8Not taking the real cost of the actual combinations, and relying upon the large level and other level-member-based safeguards to protect us may actually place a load upon the client that would challenge even a robust server. At the heart of this resource intensive situation lies our old friend the CrossJoin, although there are many other similar potential participants in inefficient query construction and operation. We will examine ways to manage these scenarios in the next section.