The Role of Expression Arrangement in Optimization
As many of us have learned, both in working with SQL and with MDX, the greatest strides in performance tuning can often be gained by tuning the expressions themselves. The simple rearrangement of a query can produce dramatic performance gains, as we will see in this section. We have discussed the control of resource use, to some extent, through the direction of a query’s location of execution. We also exposed a scenario in the previous section where, even though the query might contain level populations that fall below the threshold for server processing, client processing can be more expensive than is apparent.
Let’s return to our example above, and see what we can do in the way of arranging expressions to make our query less resource intensive. To do so, we will resume where we left off within the Sample Application:
1. Create a new query to rephrase the last (saved as SSP_007-3), arranged as follows:
— SSP_007-4: Optimized Set Operation
SELECT
{[Measures]. [Units Shipped]} ON COLUMNS,
TopCount (
Generate ( [Warehouse].[City].Members,
TopCount ( CrossJoin ({ [Warehouse].CurrentMember},
[Product].[Product Name].Members
), 7, [Measures].[Units Shipped]
)
), 7, [Measures].[Units Shipped]
) ON ROWS
FROM Warehouse
In our rearrangement of query SSP_007-3, we are exploiting the GENERATE function to rephrase our query to state the following: “Determine the top seven tuples that we obtain from cross-joining the warehouse city and named products, for the current warehouse city. Then marry the results with the rest of the members of the warehouse city level.”
The number of combinations that result within this query is 1,721 (1 x 1,560 named products, plus 7 x 23 cities). This represents a significant reduction over the number of combinations in the pre-optimized query, and results in a corresponding reduction of over 95 percent.
As we will see from the next step, the results will be identical to those in the pre-optimized version.
2. Execute the query using the Run Query button.
The results dataset appears as shown in Figure 6.
Figure 6: Results Dataset, Optimized CrossJoin() Query
3. Save the query as SSP_007-4.
Through this small exercise we have illustrated the dramatic performance gains that can be obtained by simply rephrasing a query. To summarize the approach, we might keep in mind that we can replace complex CROSSJOIN() statements with a GENERATE() and CROSSJOIN() combination, as we have demonstrated above. The following general rule summarizes the approach:
CrossJoin (X,Y) =
GENERATE(X, CrossJoin(X.CurrentMember, Y)
Or
CrossJoin (X,Y) =
GENERATE (CrossJoin(X,Y.CurrentMember), Y)
Let’s take a look at another scenario, where the objective of our query is the summation of two measures, Units Shipped and Warehouse Sales, for a given set of products, stores and warehouses (all products, in all non-U.S. stores and warehouses, in our example). We can approach the query in a couple of ways, using calculated measures to effect the summations.
Let’s build a straightforward query first, where we create calculated members that we combine with the measures we seek to obtain the correct calculations.
1. Create the following new query:
— SSP_007-5: Pre-optimized Set Operation # 2
WITH
MEMBER [Product].[Non-US Total] AS
‘SUM ({[Product].[All Products].[Drink],
[Product].[All Products].[Food]})’
MEMBER [Store].[Non-US Total] AS
‘SUM ({[Store].[All Stores].[Canada],
[Store].[All Stores].[Mexico]})’
MEMBER [Warehouse].[Non-US Total] AS
‘SUM ({[Warehouse].[All Warehouses].[Canada],
[Warehouse].[All Warehouses].[Mexico]})’
SELECT
{[Measures].[Units Shipped],
[Measures].[Warehouse Sales]} ON COLUMNS,
{[Time].[1998]} ON ROWS
FROM Warehouse
WHERE ( [Product].[Non-US Total],
[Store].[Non-US Total], [Warehouse].[NON-US Total] )
Now, let’s run the query and note the results.
2. Execute the query using the Run Query button.
The results dataset appears as depicted in Figure 7.
Figure 7: Results Dataset, Pre-Optimized Summation Query
3. Save the query as SSP_007-5.
Now let’s consider another approach to obtaining the same objective. This time, we will place each set of calculation logic into a respective calculated measure.