'Not In' Query Slow - What are my alternatives?

[User 2084]

Hi All,

I'm teaching myself the basics of database design using Microsoft
Access. I have a query that is really slow for large data sets. I was
hoping someone might be able to offer a suggestion or two on whether
or not there are any alternatives to what I'm doing.

The query that's slow uses the 'Not In' clause. Here's the SQL:

SELECT Cars.*, *
FROM FastCarsEnteringDealership INNER JOIN Cars ON
FastCarsEnteringDealership.VIN = Cars.VIN
WHERE ((([FastCarsEnteringDealership].[Cars].[VIN]) Not In (select VIN
from CarsAlreadySold)) AND
(([FastCarsEnteringDealership].[Cars].[VIN]) Not In (select VIN from
CarsTransferredOffLot)) AND
(((FastCarsEnteringDealership.time_stamp)<[Enter Date:] Or
(FastCarsEnteringDealership.time_stamp) Like [Enter Date:] & "*")));

Are there faster alternatives to using Not In, and if so, what are
they?

Thanks in advance.

 

[Sylvain Lafontaine]

If the table CarsTransferredOffLot is large, you may try replacing your NOT
IN clause with Not Exists and a correlated subquery. See the previous
thread EXISTS from Elizb for some example.

However, your lack of speed problem is probably stemming from other causes,
like missing indexes or an improper design. For example, it may be possible
to set the fact that a car has been sold as one of the Cars table field.
You may hear a lot about Normalizations and perfect design and so and so but
the primary need of a database is to be fast; not to be *pure*.

 

[Van T. Dinh]

I am surprised your SQL actually works. The full reference in the WHERE
Clause:

[FastCarsEnteringDealership].[Cars].[VIN]

is syntactically incorrect. Are you referring to the
[FastCarEnteringDealership] Table or the [Cars] Table.

If you re-type the SQL for the post, then it is better to copy and paste the
actual SQL String to avoid typing mistakes and to ensure your description
matches the SQL.