CODEWITHSUNDEEP
sqlquery-performance
Comencau
Comencau

Reputation: 1193

Union SQL query performance

I have a table which gives a value between two items. The table has the following columns :

itemA | itemB | value
1     | 2     | 125
1     | 3     | 56
3     | 2     | 78
2     | 4     | 24
4     | 1     | 994
3     | 4     | 7

I highlight that the symetric values are not repeated. For example there is now row with itemA = 2 and itemB = 1 with value = 125 as this data is already stored with itemA = 1 and itemB = 2.

Now I would like to get all the values between items 1, 2 and 3 but with the symetric values as well. Something like that in the SQL result set :

itemA | itemB | value
1     | 2     | 125
1     | 3     | 56
2     | 1     | 125
2     | 3     | 78
3     | 1     | 56
3     | 2     | 78

I succeed to get that with the follwoing SQL query :

select itemA as iA, itemB as iB, value 
from table 
where itemA in (1,2,3) 
    and itemB in (1,2,3) 
union all 
select itemB as iA, itemA as iB, value 
from table 
where itemA in (1,2,3) 
    and itemB in (1,2,3) 
order by iA, iB;

However, it has been said to me that UNION operator is performance killer and this table has in fact more than 100 M rows. But after hours of brain storming, I don't find another way to get that. Please, could you see a better, more performant SQL query to get this result set.

Thank you in advance

Upvotes: 1

Views: 193

Answers (1)

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174329

UNION can be a performance killer because it is keeping only distinct items.
UNION ALL on the other hand simply concatenates the result sets, so there should be no significant performance hit.

But you really should measure it or check the explain plain to be sure.

Upvotes: 2

Related Questions