IF ISCROSSFILTERED performs better than IF HASONEVALUE

See this post for the functional difference between ISCROSSFILTERED and HASONEVALUE.

This DAX query on AdventureWorks runs in less than a second (SP1).

DEFINE
MEASURE 'Date'[Bad Measure] =
   COUNTROWS(GENERATE(ALL('Internet Sales'), VALUES('Date'[Week Of Year])))
MEASURE 'Date'[Conditional Evaluation] =
   IF(ISCROSSFILTERED('Product'[Product Name]), [Bad Measure], 0)
//  IF(HASONEVALUE('Product'[Product Name]), [Bad Measure], 0)
EVALUATE
ADDCOLUMNS(
   VALUES('Date'[Calendar Year])
   ,"Conditional Evaluation", [Conditional Evaluation] //('Product'[Product Name] = "Mountain Pump")
)
ORDER BY 'Date'[Calendar Year]

Uncomment the product filter [‘Product'[Product Name] = “Mountain Pump”] to include it. Now it takes about 8 seconds. Looking at the query, this makes sense because it has to call the “Bad Measure”. This suggests that IF ISCROSSFILTERED is optimized to perform conditional logic only when necessary. This behavior is similar to short-circuit evaluation, but not quite the same thing.

Now comment out the line containing ISCROSSFILTERED and uncomment the line containing HASONEVALUE. It takes about 8 seconds whether the product filter is applied or not. This suggests that using IF HASONEVALUE is not optimized; it does perform conditional logic unnecessarily.

Having watched Marco Russo’s Inside DAX Query Plans session from the PASS 2012 Summit, I ran Profiler traces to confirm this theory. For the above query at least, this does seem to be the case.

  • Even when the product filter is not applied, IF HASONEVALUE executes a Vertipaq SE Query on ‘Internet Sales'[RowNumber]. RowNumber is an internal, unique column on every table.
  • The physical DAX query plan includes the following operation, which I believe means it is iterating 19.2 million rows for the formula engine to deal with.
Spool_Iterator<Spool>: IterPhyOp IterCols(0, 108, 136)('Date'[Calendar Year], 'Internet Sales'[RowNumber], 'Date'[Week Of Year]) #Records=19206564 ...

Note: 19,206,564 = 60,398 (number of rows in Internet Sales) * 53 (number of distinct Week of Year values) * 6 (number of rows in the resultset)

Needless to say, none of this happens with IF ISCROSSFILTERED when the product filter is not applied. There are less Vertipaq SE Queries, and the largest Spool_Iterator operation in the physical plan is for 6 rows (the number of rows in the resultset).

Advertisements

2 Responses to IF ISCROSSFILTERED performs better than IF HASONEVALUE

  1. Marco Russo says:

    Christian, this is really interesting! Thanks for sharing these notes, you also reminded me I *have* to work on a public release of DAX Studio – I promise to work on that in January!
    Regarding the difference between the two functions, the ISCROSSFILTERED and HASONEVALUE functions have different semantics and even if in this case you can use both for that query, in general I use HASONEVALUE to protect the access to VALUES in a generic statement that could be used in a different filter – the CROSSFILTER could have more products active (imagine a FILTER with two products in an OR condition).
    But it worths to spend time for further investigation.
    Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: