Calc script using IIF and CASE statements could be slower since AS08 vs AS05 due to the lite mode
It have been discussed with the SSAS dev team and this entry is to allow one the customer to followup the resolution of the issue in a future release.
SSAS 2008 introduced a lite evaluation mode, which is eager mode for fast calculations, a very loose term since the actual algorithm to determine whether a calculation is good for lite mode.
For IIF function, SSAS first try lite mode on each branch when there is no hint present. If lite mode succeed, great, if not, it fall back to strict mode. This algorithm falls apart when there is deeply nested IIFs with a leaf node that fails the lite mode. In that case, the failure propagates all the way to the top level IIF, which goes into strict mode on that branch. But then the 2nd level IIF will try lite mode again, fail again at the leaf, etc. A lot of time can be wasted in recursively trying the lite mode for deeply nested IIFs with a slow leaf node at the very bottom.
Since searched Case statements are internally rewritten as nested IIFs, it is one source of such problem. But there are many other cases. For example, IIF(a && b, c, d) is internally rewritten as IIF(a, IIF(b, c, d), d). So with a predicate a && b && c && d it will be rewritten as nested IIFs. Although a single formula may have nested IIFs which are not too deep, the leaf node maybe another calculation which has a formula of another series of nested IIFs. So although each formula only has nested IIFs which are 3-4 levels deep, a concatenation of many such formulas can effectively produces very deeply nested IIFs.
Upvotes: 1<=-=Jan 8 2010 3:46PM=-=>
Thank you for providing this feedback. We are currently investigating the reported functionality and will provide updates as we have them.
Thank you for assistance,
The Analysis Services Team