IF ISCROSSFILTERED performs better than IF HASONEVALUE
December 31, 2012 2 Comments
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).
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!
Marco – thanks for the comment!
Yes, there are of course cases where we need to ensure there is only one value in filter context – typically to allow implicit conversion to a scalar value using the VALUES function (like the query at the bottom of http://christianwade.wordpress.com/2012/08/21/currency-conversion-formatting-symbols-in-tabular/ ). And of course there are other cases where a calculation may support one or multiple values in filter context.
In any case, it’s worth being aware that IF HASONEVALUE has this behavior.
Let me know when the public release of DAX Studio is available!