Simple vs Searched CASE statements

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.

6 responses

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

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

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

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: