Subscribe to this blog
Briefcase
Print
I used to think naively that IIF is an innocent little function that can only do good and help me make a calculated measure evn faster! Wrong! Wrong! Wrong!
I found this very helpful article by Mosha Pasumansky
http://sqlblog.com/blogs/mosha/archive/2007/01/29/performance-of-iif-function-in-mdx.aspx
where he urges MDX developers to avoid using IIF function and showing how bad it could be for the performance.
I have rewritten my calculated measures which widely used IIF functions and results in performance gain were very impressive: IIF worsened performance almost in geometric progression:
| with IIF | without IIF | |
| Query 1 | 5 s | 2 s |
| Query 2 | 18 s | 8 s |
| Query 3 | 87 s | 25 s |
Lesson learned: before writing a calculated member - try to think more about performance...
Posted by Anonymous on 11 February 2011
Pingback from Twitter Trackbacks for SQL Server Central, MDX Performance - burdens of IIF - aedna@SQL [sqlservercentral.com] on Topsy.com
Posted by m.yasir on 16 February 2011
THANKS, DEAR I NEED YOUR HELP TO MORE UNDERSTAND MDX QUERY AND WANT TO LEARN.... PLEASE HELP IF YOU HAVE SOME STUF.
Posted by Jeffrey on 16 February 2011
Well, Mosha's blog is kind of old. We, Microsoft AS team, have made significant improvements to IIF since those days. Here is a more recent blog on this subject.
mdxdax.blogspot.com/.../mdx-iif-execution-plans-and-plan-hints.html
Posted by z3bull on 16 February 2011
msdn.microsoft.com/.../bb934106.aspx
seems they have made some improvements in 2008
Posted by aedna on 18 February 2011
jwang8888, z3bull - thanks! BTW, I was running my tests in SSAS 2008