CODEWITHSUNDEEP
sqlsql-serverperformanceunion
Milo Cabs
Milo Cabs

Reputation: 513

SQL Union overhead

Im having a performance issue on using union. I have two sql queries with same column results but from different tables:

SELECT COL1, COL2, COL3
FROM TABLE1

(which takes about 4 secs to finish) and:

SELECT COL1, COL2, COL3
FROM TABLE2 INNER JOIN TABLE3 ON ID

which also takes about 4 secs to finish. When I use UNION ALL for the two queries the result takes 22 seconds to finish.

Any suggestions to improve performance?

Upvotes: 0

Views: 382

Answers (3)

Aaron Digulla
Aaron Digulla

Reputation: 328556

As always with performance questions: Measure.

So the next step would be to get the execution plan from your database and look at that. Open a new question if you don't understand the plan.

Upvotes: 0

Yaroslav
Yaroslav

Reputation: 6534

UNION clause removes duplicates by using DISTINCT on the select clause and creating some temporary tables and those are permformance expensive tasks. Do you need those duplicates or not?

Check this link by Pinal Dave it has a detailed analysis on using UNION and UNION ALL, with execution plans analysis too.

I would check too if indexes are correctly used on these select clauses, without a detailed view of tables structures is hard to say.

Upvotes: 2

Andrey Gurinov
Andrey Gurinov

Reputation: 2885

Try to use UNION ALL - it avoids finding duplicates. It is a good practice not to use UNION without ALL and DISTINCT, except you really need it.

Upvotes: 0

Related Questions