Slow running query - 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 2005 Forums

Transact-SQL (2005)

Slow running query

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-12-09 : 13:38:07

SELECT DISTINCT A.Status
FROM dbo.Test1 A
LEFT JOIN dbo.Test2 B
ON A.Status= B.Status
WHERE B.Status IS NULL
AND A.Status IS NOT NULL

Number of rows in dbo.Test1 A = 150 000
Number of rows in dbo.Test2 B = 4500
There are clusterd indexes on both table on ID column

This query is taking 35 minutes.

How can I make it faster

I have tested the following
1) Included column A.Status & B.Status on the clustered index -Time taken 34 minutes
The query execution plan shows that the LEFT JOIN operation cost is 60% and the filter on B.Status IS NULL is 18%.These two task has the highest %

chadmat
The Chadinator

1974 Posts

Posted - 2010-12-09 : 14:14:45

Put a non clustered index on Status on both tables. For this query in isolation, you don't need any other columns included, but you may want to consider including columns that other queries might need that join/filter on status.

-Chad

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-09 : 14:22:47

select a.Status from Test1 a
where not exists(select * from Test2 b where b.Status=a.Status)
group by a.Status

No, you're never too old to Yak'n'Roll if you're too young to die.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-12-09 : 14:50:41

I tried this and it executed really fast,apprecaite if someone can verify if this logic is the same.

SELECT DISTINCT A.Status INTO #TEMP FROM dbo.Test1 A where A.Status IS NOT NULL

SELECT C.Status from #TEMP C
LEFT JOIN dbo.Test2 B
ON C.Status= B.Status
WHERE B.Status IS NULL

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-09 : 15:01:12

Yes it is the same.

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-12-09 : 15:58:59

Did you try webfred's query? Saves meesing around with a temporary table and I think will be faster.

Or possibly this:

select DISTINCT a.Status from Test1 a
where not exists(select * from Test2 b where b.Status=a.Status)
~~group by a.Status~~

Kristen
Test

22859 Posts

Posted - 2010-12-09 : 16:00:10

"1) Included column A.Status & B.Status on the clustered index -Time taken 34 minutes"

Having the [Status] in the clustered index won't help (unless it is the only column in the clustered index, which is a fairly moot point as it is not unique). Putting it in a separate index probably would help.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-12-09 : 16:38:31

I did try webfred's query but it did not improve on the duration.

I also did try the below, but no improvements

select DISTINCT a.Status from Test1 a
where not exists(select * from Test2 b where b.Status=a.Status)

Im going to create a nonclusterd index on Status column and try again.

Kristen
Test

22859 Posts

Posted - 2010-12-09 : 16:49:41

"Im going to create a nonclusterd index on Status column and try again."

Well that should help (or I'll have to eat my hat ... again ...!) as it will cover the query

TimSman
Posting Yak Master

127 Posts

Posted - 2010-12-09 : 17:04:59

What about:


SELECT DISTINCT
    A.Status
FROM
    dbo.Test1 AS A
EXCEPT
SELECT DISTINCT
    B.Status
FROM
    dbo.Test2 AS b

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-12-13 : 13:17:08

Im going to create a nonclusterd index on Status column and try again.

Yippie this helped me out, thanks guys

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-13 : 13:38:56

How selective is status?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources