VERY SLOW QUERY UNION - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

VERY SLOW QUERY UNION

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-08 : 09:44:12

Salman Abeed writes "Plz see the query below

SELECT *
FROM RelationshipInstance
WHERE ID2 in (Select ID2 from RelationshipInstance where RelationshipType = 3 and ID1='0000000012')

UNION ALL

SELECT *
FROM RelationshipInstance
WHERE ID1 in (Select ID2 from RelationshipInstance where RelationshipType = 3 and ID1='0000000012')

When these queries are process seperately they take less then a second (In my database there are total 18,000 records in relationinstance table) and Query 1 returns 6500 records and query 2 returns 2 records.

So why it takes more then 10 minutes where i am using
UNION ALL ... It should take 1 or max 2 second
ALL means i need not to check the duplicates and just want to merge the results."

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2002-05-08 : 09:49:33

Here are some suggestions:
1) Check the query execution plan
2) Instead of using an *, select only the fields you need
3) Run each query separately and put the results in a temporary table in a stored procedure

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-08 : 10:24:28

This should produce the same resultset and should be quite a bit faster . ..


select distinct
    <collist>
from
    relationshipinstance ri
where
    exists (
        select 1
        from
            relationshipinstance
        where
            ID2 in (ri.ID1,ri.ID2) and
            RelationshipType = 3 and
            ID1 = '0000000012')

<O>

Edited by - Page47 on 05/08/2002 10:28:00

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources