Can this be run faster (NOT EXISTS)

[dave796]

I have a table Full Register List containing about 8000 registers along with each of the weeks they are run in the fields are register_id, register_group, WeekNo which are combined to make the primary key. So for each register_id and register_group there is usually a record for each week the register runs i.e week 7 to 48. I want to compare this to a list of register weeks that have been marked. These are stored in dbo_stthpwka and I want to return registers that don't exist (haven't been marked) so I am currently looking at:

SELECT A.*
FROM [Full Register List] AS A
WHERE NOT EXISTS (SELECT * From dbo_stthpwka WHERE dbo_stthpwka.register_id = A.register_id and dbo_stthpwka.register_group = A.register_group and dbo_stthpwka.week_no = WeekNo);

This is from MS access so I would obviously need to change syntax but it does seem to take a long time to run. Is there a better way to get the same results. It runs overnight so speed is not of absolute importance but the 8000 records I currently have will probably be multiplied by 20 or 30 in live environment so will take a very long time.

 

[dave796]

I mostly solved the problem changed to this:

SELECT A.*, B.register_group AS Out
FROM [Full Register List] AS A LEFT JOIN dbo_stthpwka AS B ON (B.register_id = A.register_id and B.register_group = A.register_group and B.week_no = WeekNo)
WHERE B.register_group NOT LIKE "*";

This runs near instantly however you may notice I have put a not like statement in the where clause. I dont know if this is an access feature but I tried B.register_group = null and B.register_group = "" and it returns no results. If anyone knows why this is let me know.

 

[PHV]

WHERE B.register_group IS NULL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886