2

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?

Mike Woodhouse
  • 51,832
  • 12
  • 88
  • 127
Saurabh Kumar
  • 2,329
  • 6
  • 32
  • 52

2 Answers2

2

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
  • 30,688
  • 6
  • 69
  • 111
0

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.

Alex
  • 14,338
  • 5
  • 41
  • 59
  • 10
    As far as I know UNION automatically does a DISTINCT internally.. UNION ALL, does not apply that DISTINCT to the result set. – Saurabh Kumar Oct 04 '10 at 08:28
  • 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. – Kieran Senior Oct 04 '10 at 08:48
  • 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. – Alex Oct 04 '10 at 08:53