I recently had a customer show me an MDX calculation using a CASE statement, where they had achieved a big improvement in performance when they moved from using a searched CASE to using a simple CASE. For the record, here’s an example of a simple CASE statement:
CASE [Measures].[Internet Sales Amount]
WHEN 0 THEN 0
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
ELSE 4
END
And what the equivalent searched CASE looks like this:
CASE
WHEN [Measures].[Internet Sales Amount]=0 THEN 0
WHEN [Measures].[Internet Sales Amount]=1 THEN 1
WHEN [Measures].[Internet Sales Amount]=2 THEN 2
WHEN [Measures].[Internet Sales Amount]=3 THEN 3
ELSE 4
END
In my customer’s code they were doing lots of complex conditions involving calculated measures, but nonetheless I couldn’t work out why their searched CASE performed so much worse because the logic in both the simple and the searched CASE was the same. Therefore, in turn I asked my friends in Redmond and Marius Dumitru gave me some very useful insights that I thought I’d pass on. Basically, a searched CASE requires all previous branch conditions to have been evaluated before a given branch is chosen, which in turn makes it harder for the query optimiser to evaluate a searched CASE in bulk mode. As a result it’s always better to use a simple CASE where possible; in the worst scenarios (and I guess this is what was happening to my customer) the performance of a searched CASE can be several times worse than a simple CASE even when they seem to be doing the same thing.
Hi Chris,What about using a nested IIF statement? Which approach would be the fastest?I always thought using IIF\’s would be the fastest..–Jorg Klein
I think a nested IIF is easier to optimise (especially if you use IIF query hints), but it won\’t necessarily perform better than a CASE statement.
Hi Chirs,I made a test and found there is no difference in Excution plan, I think if you use the same logic to calculate with simple case statement and search statement, you will spend the same time.Please see my test code:DROP TABLE dbo.NumsGO–================================================================–CREATE TABLECREATE TABLE dbo.Nums( ID INT)GO–================================================================–padding dataDECLARE @num INTSET @num =1WHILE @num<1000BEGININSERT INTO dbo.Nums(ID)VALUES(@num)SET @num=@num+1ENDGODECLARE @max INTSELECT @max = MAX(Id) FROM dbo.NumsWHILE @max<1000000BEGINSELECT @max = MAX(Id) FROM dbo.NumsINSERT INTO dbo.Nums(ID)SELECT ID+@max FROM dbo.NumsENDGO–================================================================–Test–SIMPLE CASE STATEMENTSELECT CASE ID%10 WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 WHEN 5 THEN 5 WHEN 6 THEN 6 WHEN 7 THEN 7 WHEN 8 THEN 8 WHEN 9 THEN 9 ELSE 0 ENDFROM dbo.Nums–SEARCH CASE STATEMENT SELECT CASE WHEN ID%10=1 THEN 1 WHEN ID%10=2 THEN 2 WHEN ID%10=3 THEN 3 WHEN ID%10=4 THEN 4 WHEN ID%10=5 THEN 5 WHEN ID%10=6 THEN 6 WHEN ID%10=7 THEN 7 WHEN ID%10=8 THEN 8 WHEN ID%10=9 THEN 9 ELSE 0 ENDFROM dbo.NumsGO
Sorry, I should have pointed out that this is in MDX and not SQL… And in any case, you won\’t see a difference in the query plans in all cases in MDX, it\’s just that in general the simple case is more likely to perform better.
Case statements in MDX
http://www.consultguru.me/post/2011/08/19/Case-statements-in-MDX.aspx
Great Blog post! Any idea why this will not work for me:
CASE
WHEN &SubVar = “Y”
Then
MISSING
ELSE
([Member])
END
The issue is the string “Y”. I tried wrapping it with [], (), {} and also tried single quotes ‘. Not sure why its not working.