Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

MDX Performance - burdens of IIF

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...

Comments

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

Leave a Comment

Please register or log in to leave a comment.