Select Query taking too long (needs new syntax?)

[wrbodine]

Hi,

I have a Select query that I can't figure out how to do. There's one table that has a list of 5,000 different Dept numbers (tblDepts), and another table (tblAccess, with 12,000 rows) where users can be assigned to various departments (a user can be assigned from 0 to 5,000 depts), and a table w/ Users. (The User_ID is a foriegn key in tblAccess)

I need a query that will return a list of Depts that haven't been assigned to a specific User_ID.

I'm using the following query, but it takes 5 minutes to run:

Select D.Dept_Number
From tblDepts D
Where D.Dept_Number Not in
(Select A.Dept_Number
From tblAccess A
Where A.User_Id = '65')

I do have clustered indexes on Dept_Number in both tables, and have run DBCC ShowContig and the results aren't that bad. I'm sure there's a better way to write the query. Any ideas would be appreciated!

Thanks,
Ray

 

[tlbroadbent]

Correlate the subquery to the main query by adding a Dept_Number criteria. Then change the criteria of the main query to "Not Exists" rather than "Not In." "Not In" is very inefficient.

Select D.Dept_Number
From tblDepts D
Where D.Dept_Number Not Exists
(Select *
From tblAccess
Where User_Id = '65'
And Dept_Number=D.Dept_Number)

Make sure you have an index on Dept_Number on each table as well as on User_ID on tbl_Access. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous