CASE vs UNION

Question

Post reply

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

mithun.gite

SSCrazy

Points: 2333

March 31, 2009 at 4:35 am

#968943

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: 110192 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