SQL query improvement - MSAccess 2007

Question

I have a need to Select all records based on the following SQL:

Select ID, ID2 From Table1 Where ID2 NOT IN (Select ID2 From Table2 Where ID3 IN (151,157))

There are 171k records in Table1 and 70k records in Table2 where 'ID3 IN (151,157)'.

Unfortunately, that query takes forever; in fact, I have never seen it complete on a system with 32GB memory and quad I7 processors. I give up and cancel after 30 minutes.

I figure there is an SQL guru or two here that can tell me how to improve this query and get it to complete in under a minute.

Answer 1

You could try joining the subquery:

Select ID, ID2
From Table1
LEFT JOIN 
(Select ID2
 From Table2
 Where ID3 IN (151,157))
WHERE ID2 IS NULL

If that doesn't work, I would consider creating the subquery as temp table and referencing that.