need help speeeding up a query

[sean78]

I ran into a problem with this query. The second not in statement generates about 88,000 records and is slowing this statement down. Does anyone have any ideas on how to speed this guy up. It works great everyother time I run it except in this one instance. It is timming out my asp page..

SELECT count(*), s.divisionid
FROM storevisit sv join store s on s.id = sv.storeid
WHERE sv.projectID = 305 AND sv.status in (1,2) AND s.divisionid not in
(select divisionID from TaskDivisionException where taskid = 2744 and projectid = 305)
AND sv.id not in
(Select storevisitid from TaskStoreVisitException where taskid = 2744)
GROUP BY s.divisionid Order By s.divisionid

 

[SQLSister]

first, is taskid in TAskSToreVisitException indexed?

I don't know what database you are using but often using a left join where the join field is null in the second table is more efficient than using not in.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[PHV]

You may also try to play with a NOT EXISTS correlated subquery.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886