how to optimize query with union all

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to optimize query with union all

Author

Topic

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-03-22 : 01:08:04

Hello Experts,

I have one question regarding how to make the below mentioned type of query more optimized so that mine query perfomace can be increased.

Select a.name1,a.address1,b.field3,c.field4,d.field6,e.field7
from a
inner join b on a.id=b.id
inner join c on c.id=b.id
inner join d on d.id=c.id
iner join e on e.id=d.id

union all

Select a1.name1,a1.address1,b1.field3,c.field4,d.field6,e.field7
from a1
inner join b1 on a1.id=b1.id
inner join c on c.id=b1.id
inner join d on d.id=c.id
iner join e on e.id=d.id

union all

Select a2.name1,a2.address1,b2.field3,c.field4,d.field6,e.field7
from a2
inner join b2 on a2.id=b2.id
inner join c on c.id=b2.id
inner join d on d.id=c.id
iner join e on e.id=d.id

All the above three select query at last using the same inner join condition, means last two inner join condition are the same for all the select query.

Is there any way so that this query can be optimized?

Regards,
abhi

Kristen
Test

22859 Posts

Posted - 2010-03-22 : 03:49:03

Don't know if it will be any different but you could do:


Select X.name1,X.address1,X.field3,c.field4,d.field6,e.field7
FROM
(
    Select a.id, a.name1,a.address1,b.field3
    from a
    inner join b on a.id=b.id
    union all
    Select a1.id, a1.name1,a1.address1,b1.field3
    from a1
    union all
    Select a2.id, a2.name1,a2.address1,b2.field3
    from a2
) AS X
inner join c on c.id=X.id
inner join d on d.id=c.id
iner join e on e.id=d.id

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-22 : 05:04:44

quote:

Originally posted by Kristen

Don't know if it will be any different but you could do:


Select X.name1,X.address1,X.field3,c.field4,d.field6,e.field7
FROM
(
    Select a.id, a.name1,a.address1,b.field3
    from a
    inner join b on a.id=b.id
    union all
    Select a1.id, a1.name1,a1.address1,b1.field3
    from a1
    inner join b1 on a1.id=b1.id
    union all
    Select a2.id, a2.name1,a2.address1,b2.field3
    from a2
    inner join b2 on a2.id=b2.id
) AS X
inner join c on c.id=X.id
inner join d on d.id=c.id
iner join e on e.id=d.id

it will not work
Red part was missing in the above query.

Vaibhav T

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-22 : 05:17:31

one more way to do this


Select X.name1,X.address1,
case tbl 
when 'a' then b.field3
when 'a1' then b1.field3
when 'a2' then b2.field3
end as field3,
c.field4,d.field6,e.field7
FROM
(
    Select a.id, a.name1,a.address1, 'a' tbl
    from a
    union all
    Select a1.id, a1.name1,a1.address1, 'a1' tbl
	from a1
    union all
    Select a2.id, a2.name1,a2.address1, 'a2' tbl
    from a2
) AS X
left join b on b.id = x.id
left join b1 on b1.id = x.id
left join b2 on b2.id = x.id
inner join c on c.id=X.id
inner join d on d.id=c.id
inner join e on e.id=d.id

Vaibhav T

Kristen
Test

22859 Posts

Posted - 2010-03-22 : 05:18:34

Thanks, missed that bit when cutting & pasting

Kristen
Test

22859 Posts

Posted - 2010-03-22 : 05:20:22

The left joins may give you a different result because the do not enforce selection from the "A" tables only where corresponding records exist from "B" tables. That could be worked around, but I very much doubt it will perform well.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-22 : 05:29:35

Yes kristen you are right i should rewrite the query like below to avoid unexpected output.


SELECT * FROM 
(
Select X.name1,X.address1,
case tbl 
when 'a' then b.field3
when 'a1' then b1.field3
when 'a2' then b2.field3
end as field3,
c.field4,d.field6,e.field7
FROM
(
    Select a.id, a.name1,a.address1, 'a' tbl
    from a
    union all
    Select a1.id, a1.name1,a1.address1, 'a1' tbl
	from a1
    union all
    Select a2.id, a2.name1,a2.address1, 'a2' tbl
    from a2
) AS X
left join b on b.id = x.id
left join b1 on b1.id = x.id
left join b2 on b2.id = x.id
inner join c on c.id=X.id
inner join d on d.id=c.id
inner join e on e.id=d.id
) d
where field3 is not null

Vaibhav T

Kristen
Test

22859 Posts

Posted - 2010-03-22 : 08:10:37

"where field3 is not null"

Its more complicated that that I think. field3 can (probably) be null, but the ID of the appropriate TableB cannot be NULL.

So you would need to add to WHERE:


   (tbl = 'a' AND b.ID IS NOT NULL)
OR (tbl = 'a1' AND b1.field3 IS NOT NULL)
OR (tbl = 'a2' AND b2.field3 IS NOT NULL)

there are further problems in that the ID may have matched the ID in the other B-tables - possibly non-uniquely, which will have caused multiple rows to be returned.

e.g. when matching the first UNION


from a
inner join b on a.id=b.id

will have matched N rows
but it is possible that


) AS X
left join b on b.id = x.id
left join b1 on b1.id = x.id
left join b2 on b2.id = x.id

will have matched b1.id and/or b2.id multiple times, which will cause a cartesian join with the row(s) selected matching b.id, and in that case would distort the results (even though the values from the other "B" tables are not used).

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-22 : 09:32:42

Couldn't you just do:

Select ax.name1,ax.address1,bx.field3,c.field4,d.field6,e.field7
FROM (SELECT id, name1, address1 FROM a
UNION ALL SELECT id, name1, address1 FROM a1
UNION ALL SELECT id, name1, address1 FROM a2 ) AS ax
INNER JOIN (SELECT id, field3 FROM b
UNION ALL SELECT id, field3 FROM b1
UNION ALL SELECT id, field3 FROM b2) AS bx on ax.id=bx.id
inner join c on c.id=bx.id
inner join d on d.id=c.id
inner join e on e.id=d.id

There are 10 types of people in the world, those that understand binary, and those that don't.

Kristen
Test

22859 Posts

Posted - 2010-03-22 : 10:34:12

Suffers from the same problem that a.ID will be matched with b.ID (as required) but also any entries that there happen to be for b1.ID and b2.ID (which are not relevant to a.ID)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources