May 23, 2006 at 9:15 am
I did a few searches and I am not seeing anything saying Union queries are bad. Are they?
I understand that they have to make two selects instead of one but if it is for the same record count total is it ok or even optimized?
I can not figure out a way to do a query without using either a case statement or a union. I am leaning toward the union query.
This is in SQL 2000
May 23, 2006 at 9:48 am
There is nothing wrong with UNION queries. If that's the only way to retrieve the data set required, then use it.
The primary optimization concern is whether or not the data in each unioned query is mutually exclusive, thereby allowing the use of UNION ALL. Using UNION ALL means the query engine does not ned to perform a DISTINCT on the resultset, and it is this final step of getting distinct rows that often proves to be expensive in UNION queries.
Try running a query plan on these:
Select 1 As TestValue
UNION ALL
Select 2
UNION ALL
Select 3
versus:
Select 1 As TestValue
UNION
Select 2
UNION
Select 3
... the merge join/union execution steps in the 2nd version of the query become expensive if the queries return large numbers of rows, versus the simple concatenation step in the 1st version.
May 23, 2006 at 10:13 am
Wow that was pretty cool. I never new that. I of course shared this to co-workers and got kudos. Thanks a ton. that helps me in the future in chioces I make
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply