IIF Function Query Hints in SQL Server Analysis Services 2008

IIF Function Query Hints in SQL Server Analysis Services 2008

Rate This
  • Comments 3

Thomas Kejser and I are doing some work to produce the SQL Server 2008 Analysis Services Performance Guide. Among a bunch of other things, it discusses the IIF function. But because I recently mentioned MDX query hints in a recent podcast, I wanted to get this out quickly. Some references are a bit hazy (expensive vs inexpensive query plans, default values and the like) and I’ll elaborate either in future blogs or in the white paper itself.

Anyway, the IIF mdx function is a commonly used expression that can be very costly to evaluate. It takes 3 arguments:

iif(<condition>, <then branch>, <else branch>)

Where the condition evaluates to true, the value from the “then branch” is used otherwise the “else branch” expression is used.

Note the term “used” – one or both branches may be evaluated even if its value is not used.  It may be cheaper for the engine to evaluate the expression over the entire space and use it when needed (termed an eager plan) rather than chop up the space into a potentially enormous number of fragments and evaluate only where needed (a strict plan).

The first consideration is whether the query plan is expensive or inexpensive. An expensive query plan is one that is evaluated in cell-by-cell mode (more on this in a future post) or one that requires reading a cube data (most commonly because a member navigation function is used).  Most IIF condition query plans are inexpensive – but nasty nested conditions with more IIFs can force it to go to cell-by-cell (functions that are not cell by cell are listed in the sql documentation).

The next consideration the engine makes is what value the condition takes most. This is driven by the condition’s default value.  If the condition’s default value is true, then the “then branch” is the default branch – the branch that is evaluated over most of the subspace. Simple investigation and knowing a few simple rules on how the condition is evaluated helps to determine the default branch:

·         In sparse expressions most cells are empty.  So the default value of the isempty function on a sparse expression is true.

·         Comparison to zero of a sparse expression is true

·         Default value of IS operator is false

·         If the condition cannot be evaluated in subspace mode, there is no default branch

For example, one of the most common uses of the IIF function is to check whether the denominator is non-zero:

 

iif([Measures].[Internet Sales Amount]=0, null, [Measures].[Internet Order Quantity]/[Measures].[Internet Sales Amount])

 

There is no calculation on Internet Sales Amount so it is sparse.  Therefore the default value of the condition is true and therefore the default branch is the then branch with the null expression.

The table below shows how each branch of an IIF function is evaluated:

 

Branch Query Plan

Branch is default branch

Branch expression sparsity

Evaluation

Expensive

n/a

n/a

Strict

Inexpensive

Unknown

n/a

Eager

Inexpensive

True

n/a

Eager

Inexpensive

False

Dense

Strict

Inexpensive

False

Sparse

Eager

 

In SQL Server 2008 Analysis Services, you can overrule the default behavior with query hints:

 

Iif( <condition>, <then branch> [hint  [Eager|Strict]], <else branch> [hint [Eager | Strict]] )

 

When would you want to override the default behavior? The most common scenarios where you might want to change the default behavior are:

·         Engine determines the query plan for the condition is expensive and evaluates each branch in strict mode

·         Condition is evaluated in cell by cell mode and each branch is evaluated in eager mode

·         Branch expression is dense but easily evaluated.

For example, consider the simple expression below taking the inverse of a measure:

 

with member

measures.x as

iif(

   [Measures].[Internet Sales Amount]=0

   , null

   , (1/[Measures].[Internet Sales Amount]))

select {[Measures].x} on 0,

[Customer].[Customer Geography].[Country].members on 1

from [Adventure Works]

cell properties value

 

The query plan is not expensive,  the else branch is not the default branch and the expression is dense, so it is evaluated in strict mode. This forces the engine to materialize the space over which it is evaluated. (This can be seen in profiler with query subcube verbose events selected – but it’s a time consuming investigating each event’s subspace definition).

To prevent the query plan from partitioning the space, the query can be modified as follows:

 

with member

measures.x as

iif(

   [Measures].[Internet Sales Amount]=0

   , null

   , (1/[Measures].[Internet Sales Amount]) hint eager)

select {[Measures].x} on 0,

[Customer].[Customer Geography].[Country].members on 1

from [Adventure Works]

cell properties value
Leave a Comment
  • Please add 7 and 5 and type the answer here:
  • Post
Page 1 of 1 (3 items)