February 5, 2009 at 8:08 am
Hi All,
I know that UNIONS should be avoided when ever possible.
Now I've drawn up some tests to read some execution plans for unions.
And I don't really see anything that looks very costly?
So I guess I am missing something...
Why are unions bad?
How does sql deal with them?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 5, 2009 at 8:29 am
Union should be avoided if Union All will do what you need, because Union has the additional step of removing duplicate rows. If there are a lot of rows to deal with (large dataset), that can hurt performance.
It's not a matter of "avoid if at all possible", it's a matter of "use appropriately".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2009 at 8:31 am
So it's more that the UNION is not that good rather than UNION ALL is not that good.
So UNION ALL is ok basically if needed?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 5, 2009 at 8:36 am
The one thing I understand about UNION and UNION ALL is that UNION ALL should perform a bit better because it does not have to eliminate duplicates (sort and compare) before returning results.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 5, 2009 at 8:41 am
Christopher Stobbs (2/5/2009)
Now I've drawn up some tests to read some execution plans for unions.And I don't really see anything that looks very costly?
See a hash aggregate or a sort?
Union requires that SQL eliminates duplicates from the final resultset. Union All does not. Do union will have some operator that does that removal. On small rowsets it will be a Sort/Distinct Sort. On a larger ones it will be a hash aggregate.
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
February 5, 2009 at 9:12 am
Awesome thanks
I thought there was a hash bad body in there somewhere.
Thanks again to all
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 5, 2009 at 9:24 am
Christopher Stobbs (2/5/2009)
So it's more that the UNION is not that good rather than UNION ALL is not that good.So UNION ALL is ok basically if needed?
Either is okay if you need it. Just don't use Union if Union All will do what you need.
Nothing wrong with using what you need. It's only wrong if you use what you don't need and that causes wrong results or poor performance. The key word is "need".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2009 at 11:16 am
Hi
The problem with UNION is that it required sorting operation to eliminate duplicate row sets which is added cost for executing a statement, while UNION ALL return all rows without any sorting or duplication checking.
If duplicate rows are not important, using UNION ALL can save cost in terms of expensive sorts, merge and filtering operation.
Thanks -- Vj
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply