CASE vs UNION

Question

CASE vs UNION

Viewing 8 posts - 16 through 22 (of 22 total)

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

mithun.gite SSCrazy Points: 2333 More actions · Answer 1

Chris Morris (3/31/2009)
mithun.gite (3/31/2009)
Dear Friend,
I think when u use case in select statement it has to process it row by row just like the function we use....so it might hit the perofrmance then the union all...
I may be wrong also, if so then please correct me by providing the correct information to me....
Thanks
Mithun
Sure, here you go...DROP TABLE #TEMP2
DROP TABLE #TEMP3
DROP TABLE #Temp
--==================================================
-- Make some sample data
SELECT number,
number + (number % 2) * 2 AS Seq,
CASE number % 2 WHEN 0 THEN 'E' ELSE 'O' END AS Even
INTO #Temp
FROM dbo.Numbers n
WHERE number <= 1000000
ORDER BY number + (number % 2) * 2
--==================================================
set nocount on;
set statistics time on;
set statistics io on;
--==================================================
-- UNION
SELECT *, 'Even numbers' AS Choice
INTO #TEMP2
FROM #Temp
WHERE Even = 'E'
UNION ALL
SELECT *, 'Odd numbers'
FROM #Temp
WHERE Even = 'O'
/*
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#TEMP2______________________________________________________________________________________________________________00000001287D'.
Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table '#Temp_______________________________________________________________________________________________________________00000001287D'.
Scan count 2, logical reads 5438, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1734 ms, elapsed time = 1747 ms.
SQL Server Execution Times:
CPU time = 1734 ms, elapsed time = 1747 ms.
(8 row(s) affected)
SQL Server Execution Times:
CPU time = 1734 ms, elapsed time = 1747 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.*/
--==================================================
-- CASE
SELECT *,
CASE Even WHEN 'O' THEN 'Odd numbers' WHEN 'E' THEN 'Even numbers' ELSE NULL END AS Choice
INTO #TEMP3
FROM #Temp
/*
One table scan
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#TEMP3______________________________________________________________________________________________________________00000001287D'.
Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table '#Temp_______________________________________________________________________________________________________________00000001287D'.
Scan count 1, logical reads 2719, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1250 ms, elapsed time = 1258 ms.
SQL Server Execution Times:
CPU time = 1250 ms, elapsed time = 1258 ms.
(5 row(s) affected)
SQL Server Execution Times:
CPU time = 1250 ms, elapsed time = 1258 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.*/
--==================================================
Notice that the UNION method requires two table scans, the CASE method requires only one.

superb,, now can u tell me if i have used joins of two-three tables , and

then in case statment i m using these tables's columns based on the conditions...

and same way i m writing union all for these tables.....

how many table scans would be there for case statament then and which one would be faster?????

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

mithun.gite (3/31/2009)
superb,, now can u tell me if i have used joins of two-three tables , and
then in case statment i m using these tables's columns based on the conditions...
and same way i m writing union all for these tables.....
how many table scans would be there for case statament then and which one would be faster?????

Mithun, you have the queries and the data in front of you, why don't you run the tests and find out? Then post back your findings?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mithun.gite SSCrazy Points: 2333 More actions · Answer 3

I think this is not the expected and best of ur reply....

but fine,,, i respect u sir....dnt get angry on me....

I m just raising my doubts........

I will check and will surly update u.....

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Andrew Winch (3/31/2009)
So what you are saying is that even with the complexity of the CASE added to the query, the number of scans needed by the UNION ALL is the determining factor?
That was my hunch. I guess I just needed someone to 'speak' it out loud.

That's what I was trying to make you say.

Don't let your fear to be wrong to stop you from thinking and express you opinion.

Being even double newbie does not make you stupid. 😀

_____________
Code for TallyGenerator

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

mithun.gite (3/31/2009)
I think this is not the expected and best of ur reply....
but fine,,, i respect u sir....dnt get angry on me....
I m just raising my doubts........
I will check and will surly update u.....

No problem, Mithun, no offence seen here.

If you're not already in the habit of performance testing your code, then now would be a great time to start. There's more relevant information on the forum than you can shake a stick at. Once you are in the habit, checking two or three different ways of obtaining the same result set takes only moments of extra time and is well worth it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Andrew Winch (3/31/2009)
Chris,
Very interesting.
So what you are saying is that even with the complexity of the CASE added to the query, the number of scans needed by the UNION ALL is the determining factor?
That was my hunch. I guess I just needed someone to 'speak' it out loud.
Thanks

The complexity of the CASE is mirrored in the WHERE clause of the UNION method - both methods distinguish between rows based on a column value.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden