Query too slow - 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)

Query too slow

Author

Topic

youngster
Starting Member

10 Posts

Posted - 2007-11-30 : 11:07:41

I have two tables. First data is inserted into A and then the same data, but this time passing through some filters, inserted into table B. Now I want to check the data which did not pass through the filters.

SELECT TOP 100 *
FROM A
WHERE cathegoryA = 396 AND idnrA NOT IN (
SELECT idnrB
FROM B
WHERE cathegoryB = 396
)

There should be only 15 such entries (which did not pass through filters) and the data which was enetered contained only of 300 rows. Yet it is really slow.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-30 : 11:20:10

Make use of LEFT JOIN instead of NOT IN.

Select Top 100 *
from A LEFT join B on A.idnrA = B.idnrB  and
A.cathegoryA = B.cathegoryB
Where A.cathegoryA = 396 and B.idnrB IS NULL

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-30 : 14:08:51

You can also try NOT EXISTS.


SELECT TOP 100 *
FROM A
WHERE cathegoryA = 396 
AND NOT EXISTS (SELECT * FROM B WHERE cathegoryB = 396 AND B.idnrB = A.idnrA)

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-01 : 21:10:12

All 3 methods will have a nearly identical execution time in SQL Server... please define "slow". Then, tell us how many rows are in both tables. You said you entered 300 rows, but were the tables empty when you did that?

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources