If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
'Not In' Query Slow - What are my alternatives?
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. |
#2
|
|||
|
|||
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*. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC "User 2084" wrote in message om... 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. |
#3
|
|||
|
|||
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. -- HTH Van T. Dinh MVP (Access) "User 2084" wrote in message om... 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Make Table Query - very slow | Icmeler | General Discussion | 1 | March 29th, 2005 11:11 AM |
Moving from xBase/Clipper | [email protected] | New Users | 1 | February 3rd, 2005 07:25 PM |
adding 2 fields including null entries | Jesse | Running & Setting Up Queries | 26 | January 18th, 2005 05:31 PM |
AVG Function in a Query | JohnL | Running & Setting Up Queries | 5 | December 18th, 2004 05:52 AM |
Display Parameter from Form on Report | sara | Setting Up & Running Reports | 10 | July 19th, 2004 04:54 PM |