March 30, 2009 at 12:47 pm
I am building a, to me, complex query but I have come up with an interesting issue of speed.
When needing to produce differing information in different cases is it better to use a CASE with a bunch of WHENs or just UNION the entire set of different versions of the query.
To better explain here is some pseudo T-SQL as an example.
Doing things with CASE:
SELECT Account,
CASE AccountType
WHEN ‘A’ THEN (Some calculation)
WHEN ‘B’ THEN (A different calculation)
ELSE (Yet another)
END AS Sales
FROM Accounts
Doing the same with UNION:
SELECT Account, Some calculation AS Sales
FROM Accounts
WHERE AccountType = ‘A’
UNION ALL
SELECT Account, A different calculation AS Sales
FROM Accounts
WHERE AccountType = ‘B’
UNION ALL
SELECT Account, Yet another AS Sales
FROM Accounts
WHERE AccountType NOT IN (‘A’, ‘B’)
Personally I would think that the CASE would be faster but I could be wrong. 😉
Thanks in advance
Andrew
March 30, 2009 at 1:08 pm
Simple answer, write it both ways and test. Use the one that works best for you. You may want to revisit the testing periodically to see if there is a change as your data changes.
March 30, 2009 at 2:56 pm
Be aware that UNION also has to remove duplicates, which is going to take longer to process. If you can guarantee that each query is unique - you can change that to a UNION ALL instead.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 30, 2009 at 3:02 pm
Andrew Winch (3/30/2009)
Personally I would think that the CASE would be faster but I could be wrong. 😉Thanks in advance
Andrew
Why do you think so?
Can you provide any logical grounds?
Try to prove your case logically and you'll just see the answer.
_____________
Code for TallyGenerator
March 30, 2009 at 8:08 pm
Sergiy (3/30/2009)
Why do you think so?Can you provide any logical grounds?
Jeeze, chill out!
I had a gut feeling or, to better define it, a SQL newbie opinion.
Sorry to disturb your slumber, your highness.
March 30, 2009 at 8:29 pm
Lynn Pettis (3/30/2009)
Simple answer, write it both ways and test. Use the one that works best for you. You may want to revisit the testing periodically to see if there is a change as your data changes.
Thanks for the advice..
Actually I did try that but I came up with timings of less than a second for both on a database with 200K rows. Which is actually about ten times the data that my customer has. I have written my query using the CASE. It is clean to read and works well. If I had the time, I would find some database in which I could hide a few of million rows of test data and try again, but for now...
This was more of a "theory behind" type of question, not "make my query run faster".
But again thanks.
March 30, 2009 at 8:37 pm
Jeffrey Williams (3/30/2009)
Be aware that UNION also has to remove duplicates, which is going to take longer to process. If you can guarantee that each query is unique - you can change that to a UNION ALL instead.
You are correct. In this instance UNION ALL is what I should have put in my example since it does make a huge difference in the timings. I would not even consider the UNION side of things in this sort of situation.
I have changed my example
Thanks
March 30, 2009 at 9:47 pm
Andrew Winch (3/30/2009)
Sergiy (3/30/2009)
Why do you think so?Can you provide any logical grounds?
Jeeze, chill out!
I had a gut feeling or, to better define it, a SQL newbie opinion.
Sorry to disturb your slumber, your highness.
Jeeze, chill out yourself!!!
Since when proposition to think and to believe in your own abilities became an offence?
_____________
Code for TallyGenerator
March 31, 2009 at 12:36 am
hi,
as per me avoid using cases in select stmt... and better u go with the union all option , i think i would have opted for union all as i belive it is faster...
Mithun
March 31, 2009 at 1:45 am
mithun.gite (3/31/2009)
hi,as per me avoid using cases in select stmt... and better u go with the union all option , i think i would have opted for union all as i belive it is faster...
Mithun
Why is that?
Any logical grounds for this statement?
_____________
Code for TallyGenerator
March 31, 2009 at 2:28 am
mithun.gite (3/31/2009)
hi,as per me avoid using cases in select stmt... and better u go with the union all option , i think i would have opted for union all as i belive it is faster...
Mithun
I think I would opt for setting up a test as Lynn suggested, then I'd know for sure.
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
March 31, 2009 at 3:03 am
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
March 31, 2009 at 3:44 am
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.
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
March 31, 2009 at 3:52 am
Mithun,
Interesting thought but in the same vein each one of the UNION ALL selects in my example has to do a comparison on the AccountType column.
Thanks for your input
March 31, 2009 at 4:12 am
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
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply