Filter in each of multiple union queries or at the end? 1

[xlbo]

Hi all,

just looking for general best practice and possibly a rationale for which would be the best way to approach this issue:

I have a query which has 9 individual queries unioned together - the database table design requires this :-(

I need to be able to restrict data into these queries by joining to a seperate table. In terms of speed of execution, am I better off putting the join and criteria in each of the individual queries that are unioned together or as one lump at the end ie this:

SELECT a,b,c FROM t1 INNER JOIN tRef ON t.Key = tRef.key

union

SELECT a,b,c FROM t2 INNER JOIN tRef ON t2.Key = tRef.key

union

SELECT a,b,c FROM t3 INNER JOIN tRef ON t3.Key = tRef.key

etc etc


or like this:

SELECT a,b,c FROM (

SELECT a,b,c FROM t1

union

SELECT a,b,c FROM t2

union

SELECT a,b,c FROM t3 ) t

INNER JOIN tRef ON t.Key = tRef.Key

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[r937]

you are aware of the difference between UNION and UNION ALL?

as for "general best practice" i hesitate to point this out but having 9 tables with the same layout seems rather problematic

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

If you expect to filter lots of records in each table by a JOIN, I would expect putting JOIN in each of UNION will be faster than one at the end.

You may want to run these queries to compare speed and execution plan.

 

[xlbo]

r937 - I thought the main difference is that union all keeps duplicates and yes - I am well aware that this is far from good database design. I am not the DBA unfortunately and simply have to extract the data from this mess

markros - thank you - that was what I was after. I would've just done a test but the tables do not yet hold enough data for it to make much of a difference at this point - I was trying to use the best solution for when they do (couple of hundred rows each at the moment but will grow over the next couple of years to 60,000 or so in each table

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[LFCfan]

xlbo

Further to markros's post, I would expect that the JOIN within each UNION option will benefit from the (presumably) indexed t1.Key, t2.Key... fields, whereas the inline table approach wouldn't

~LFCfan

 

[xlbo]

Hi LFCfan - yes - you would presume that was the case wouldn't you.....good point though - should've thought of that

Seems like I have my answer - do the joins in each of the queries rather than at the end!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[r937]

I thought the main difference is that union all keeps duplicates

actually it's the other way round -- UNION ALL keeps the duplicates and UNION removes them




r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[xlbo]

please check your quote ;-)

me: I thought the main difference is that union all keeps duplicates

you: actually it's the other way round -- UNION ALL keeps the duplicates and UNION removes them

<cough>


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[r937]

oops, busted

looks like i can't read sometimes, eh





r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[xlbo]

Happens to us all!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[markros]

) Yes, I was about point this out too, that it was the exact same quote