February 13, 2013 at 5:38 am
Hi,
I've been having this discussion. See this example:
DECLARE @i INTEGER = 1
SELECT CASE @i
WHEN 0 THEN
(SELECT COUNT(1) FROM table1)
ELSE
(SELECT COUNT(1) FROM table2)
END
The outcome is the number of rows of table2.
Question: when this query is executed, is also SELECT COUNT(1) FROM table1 executed? In other words: are both SELECT statements executed, or only the SELECT statement in the "true" clause like in this example SELECT COUNT(1) FROM table2 ?
February 13, 2013 at 5:58 am
the statement in the true clause will only
executed
February 13, 2013 at 7:37 am
jeetsingh.cs (2/13/2013)
the statement in the true clause will onlyexecuted
And only the first one that evaluated to true
February 13, 2013 at 7:52 am
Thx!
February 13, 2013 at 8:13 am
i decided to do a test and view the execution plan.
DECLARE @i INTEGER = 1
SELECT CASE @i
WHEN 0 THEN
(SELECT COUNT(1) FROM table1)
WHEN 2 THEN
(SELECT COUNT(1) FROM table2)
ELSE
(SELECT COUNT(1) FROM table3)
END;
the execution plan shows:
an Index Scan with Cost 4% on table1
an Index Scan with cost 74% on table2
an Index Scan with cost 2% on table3
considering, in this example, it is Table3 that i'm getting the Count back from, and that has a cost of 2%, i'm presuming that SQL Server is doing something with the other tables to give Table1 & 2 a combined cost of 78%?
or am i looking into the Execution Plan wrong?
February 13, 2013 at 10:23 am
Remember, SQL has to build a single plan ahead to time to cover any value. It doesn't look at the value of the variable when building the plan.
But at run time it will only execute the first matching CASE statement.
(At least typically: in certain cases, to gain speed, separate threads might run different CASE statements simultaneously, and discard those results that aren't actually needed. But that's due to hyper-threading, not the CASE statement itself.)
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 14, 2013 at 5:11 am
The entire query is compiled when you execute it. But the path through the actual data will follow the CASE statement.
But, this example shows the problem with using this approach. Let's say you have parameters that you're going to pass to these tables, different parameters for different tables with different values. When you pass a set of parameters, say for TableA, and null values for the other tables, the query plan compiles for the NULL values due to parameter sniffing. This can seriously impact performance when you then pass specific values for TableB, etc. If you're going to use this approach, I strongly recommend breaking out the queries into separate procedures so that your case statement determines which procedure to call. Then each procedure compiles on its own.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply