Software

Ask something
Discussion Locked

Why does union perform better?

0 helpful
3 replies

by shanghai sam

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have found that using a UNION to break up a select statement makes it perform much faster (5-10x). The select statement involves inner joins on about 5 tables, and does multiple simple math calculations. The WHERE clause of the first half of the UNION includes selecting 7 of 15 items from a specific table, and the other 8 are selected in the second half. Otherwise, the two parts of the UNION are identical. How can I eliminate the UNION and still maintain the performance? And why is the UNION faster?

3 replies

by aikimark

ROGER,
Select statement performance tweaking/coding depends on several factors.
1. What database is this (MSAccess, SQLServer, Oracle, DB2, etc.)?
2. What are the 5 tables joined on?
2a. number of columns in the join
2b. data type(s) of the joined column(s)
2c. defined indexes on the tables
2d. method of join (INNER JOIN, WHERE clause, function invocation or expression used)
3. What order are the tables joined?
3a. smaller tables are more limiting that larger tables on intermediate (internal) result sets
3b. does your database pay attention to this order?
4. Does your database have a tracing/explain facility that you can use?
_________________________________
Things to try:

It seems that you might be able to add seemingly redundant selection criteria to your Where clause and improve your performance. For example, if you are joining two tables with similar data, both containing a limiting column, you may get better performance by specifying the limiting criteria on both tables, rather than allowing the join of one limited table to reduce the result set.

You might pre-join some (all) of these tables in a query/view, stored procedure, or user-defined function.

Related to the prior sentence...Is this dynamic SQL or static? Static SQL performs much better than dynamic SQL, since the database engine has less to do and might make better decisions.

by alongjordan

The first and possibly most relevant piece of information you ommitted was what Engine/Application/Server is the Database in. The differences between MS SQL and MySQL are immense. After that, the above question is still relatively irrelevant without knowing what your schema looks like. What is the structure of the data? Even then, I don't think you can start to think about why querying one way seems faster than another. Also, what kind of empirical data do you have to back up the assertion that the UNION based query performs faster, especially in the 5 to 10 times faster range. It would seem to me that changing your query so fundamentally would also change the way you process the resultant data. Which brings to mind another question are you dealing with raw query output, or is an application processing the data further after the query returns.

by richard_hulse

When joining tables, you are looking at cartesian products for size. With several joins, especially when not based on key or indexed values this can produce long calculations. Unioning smaller select statements with fewer joins and conditions can make each more efficient.

This process is also true with splitting complex multiple joins into multiple sequential select statements if a union is not appropriate (the results of the first select required for the subsequent.) The key in both is to make the first select statement the most restrictive, that is the join with the fewest expected results.

This discussion is closed to new replies.