In SQL 2008, I have a query like so:
QUERY A
UNION
QUERY B
UNION
QUERY C
Will it be slower/faster than putting the result of all 3 queries in say, a temporary table and then SELECTing them with DISTINCT?
views:
60answers:
2
A:
DISTINCT and UNION stand for totally different tasks. The first one eliminates, while the second joins result sets. I don't know what you want to do, but it seems you want distinct rows from 3 different queries with joined results. In that case:
query A UNION query B......
that would be the fastest, depending of course on what you want to do.
Alexander
2010-10-04 08:25:41
As far as I know UNION automatically does a DISTINCT internally.. UNION ALL, does not apply that DISTINCT to the result set.
Saurabh Kumar
2010-10-04 08:28:17
Indeed, a `UNION` both pastes two result sets and eliminates the duplicate rows. `UNION ALL`, however, only pastes two result sets together, and doesn't do any elimination. So `DISTINCT` only does one thing; elimination.
Kezzer
2010-10-04 08:48:41
Thanks for the information. I used them to rarely to ask this question. But basically UNION is like SELECT DISTINCT * FROM (... UNION ALL...). I'll correct my answer.
Alexander
2010-10-04 08:53:34
+1
A:
It depends on the query -- without knowing the complexity of queries A, B or C it's not one that can be answered, so your best bet is to profile and then judge based on that.
However... I'd probably go with a union regardless: a temporary table can be quite expensive, especially as it gets big. Remember with a temporary table, you're explicitly creating extra operations and thus more i/o to stress the disk sub-system out. If you can do a select without resorting to a temporary table, that's always (probably) going to be faster.
There's bound to be an exception (or seven) to this rule, hence you're better off profiling against a realistically large dataset to make sure you get some solid figures to make a suitable decision on.
Chris J
2010-10-17 17:48:47