Replace CASE WHEN expression by boolean logic?

Question

Replace CASE WHEN expression by boolean logic?

amns

SSCrazy

Points: 2628
More actions
September 16, 2014 at 5:22 am

#397350

Hi!
I have a query in which the CASE WHEN expression is used.
If a value is provided for a specific parameter, the table is filtered by that value.
I've read that boolean logic performs well than using CASE WHEN logic but Ive read also that
it is not garanteed that sql server otimizer evaluates filters in the specified order.. So Im a bit confuse which one to pick..
Which one performs better?
Sample code with CASE WHEN
SELECT
COLUMN1,
COLUMN2
FROM
TABLE1
WHERE
COLUMN1 = CASE
WHEN
@FILTER_COLUMN_1 IS NULL
THEN
COLUMN1
ELSE
@FILTER_COLUMN_1
END
AND
COLUMN2 = CASE
WHEN
@FILTER_DATE_START IS NULL
OR
@FILTER_DATE_END IS NULL
THEN
COLUMN2
ELSE
COLUMN2 BETWEEN @FILTER_DATE_START AND @FILTER_DATE_END
END
Sample code with CASE WHEN converted to boolean logic
SELECT
COLUMN1,
COLUMN2
FROM
TABLE1
WHERE
(@FILTER_COLUMN_1 IS NULL OR COLUMN1 = @FILTER_COLUMN_1)
AND
(
(@FILTER_DATE_START IS NULL OR @FILTER_DATE_END IS NULL)
OR
(COLUMN2 BETWEEN @FILTER_DATE_START AND @FILTER_DATE_END)
)
Regards!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Neither. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

amns SSCrazy Points: 2628 More actions · Answer 2

Thanks for your answer!

I ve read the article carefully and it illustrates very well the case.

But the difference for my query is that it resides in an Inline Table Valued Function intead of a stored procedure.

I ve tested it and it seems to me that it is recompiled each time

that the ITVF is called and according provided parameters the correct index is used with index seek.

But, this just happens for the second query option with the boolean logic.

It seems to me that this is the same behaviour as if I converted my ITVF to a stored procedure and add OPTION (RECOMPILE).

Any thoughts on this?

Regards,

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

in-line table-valued functions don't have their own execution plan. They're in-lined into the queries that call them, the combined query has an execution plan which is cached normally. You may see index seeks, that doesn't mean that you don't have the problem, you'll likely have to put the recompile hint on the outer query though.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101118 More actions · Answer 4

The second style would at least have a chance of being fully optimized, particularly if you force a recompile of the query. Stick with it.

I'd avoid the first style when possible (there are times when you must use that construct, but obviously not in this case).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 5

You cannot use CASE and BETWEEN the way you done it. So your first query will not provide a correct result.

Alex Suprun

amns SSCrazy Points: 2628 More actions · Answer 6

GilaMonster,

Thanks for your help. After all reading, I think I will add the OPTION RECOMPILE to the inline function combined query or even convert it to a stored procedure with dynamic sql and guarantee always the best indexes/performance » for the ITVF which the input parameters values can change frequently.

ScottPletcher,

Thanks for your opinion, I'm thinking to keep this approach (boolean logic) to the ITVFs with input values that most of the times does not change. Since in this case the cached plan of the 'combined query' will be reused.

Alexander Suprun,

You are totally right, the first query will not run. I created like that only for this example and I didn´t realize the mistake.

Best Regards!