Report Portal

Simple vs Searched CASE statements

Reposted from Chris Webb's blog with the author's permission.

I recently had a customer show me a 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. 


chris-webb

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.spaces.live.com/ .


 

Tags: mdx, performance

 

2007-2015 VidasSoft Systems Inc.