how do I speed up a "not in" ?

[karen4201]

Hi,

I have a query that looks like (variables are set before running the query):

select id
from tableA myA
where
a > @start_date
and a < @end_date
and a.userid not in (
select userid from userTable where age > myA.age
)

The purpose of the query has been made up, but the problem really exists. The userTable has about one million rows, so doing a "not in" takes a really long time. How could I speed it up? Is there some type of join that could be done to make it more efficient?

Thanks in advance.

 

[philhege]

Do a left outer join and evaluate for userid = NULL. Tune the query in QA by showing the execution plan and determining which indices are used or could be created to optimize performance.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.

 

[vongrunt]

That query looks weird... correlated condition in NOT IN() query... exactly what is it supposed to do?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[karen4201]

but if my subquery is based off of a value in the "parent" query, then how could I use an outer join?

an example would be greatly appreciated.

 

[raghu3]

Though I never tried, I understand EXISTS and NOT EXISTS are typical SQLSERVER very fast query.
Per Phil, you check out the execution plan , tune up the indexes.