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

Rate

Share

Share

Rate