Slow Query

[Ron]

I have a query as follows that takes about 5 minutes to execute but produces
the correct results:

SELECT DISTINCT [NAME], [ADDRESS]
FROM TABLE1
WHERE NAME NOT IN (SELECT NAME FROM TABLE2);


I have tried replacing it with a join which is much faster but still shows
some rows where TABLE1.NAME is in TABLE2.NAME. I have tried different
operators (<,>) and join types but can't get it to work properly:

SELECT DISTINCT TABLE1.[NAME], TABLE1.[ADDRESS]
FROM TABLE1, TABLE2
WHERE TABLE1.NAME <> TABLE2.NAME;

Any help appreciated,
Ron.

 

[strive4peace]

Hi Ron,

Why do you have names in 2 different places?

instead of storing a Name in your related table, create an
ID field (don['t call it that -- qualify it, like PersonID)
-- it will be MUCH faster and more reliable to link the tables

also, NAME is a reserved word and should not be used for a
user name



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Jerry Whittle]

Are both Name and Address in the same index in TABLE1? That could really help
with the Distinct and the NOT IN if there are more than a few hundred
records. Same goes for Name in Table2..

If IN or NOT IN are slow, I always try EXISTS or NOT EXISTS.

SELECT DISTINCT [NAME], [ADDRESS]
FROM TABLE1
WHERE (((Exists
(SELECT *
FROM TABLE2
WHERE TABLE1.NAME = TABLE2.NAME))
=False));

Or

SELECT DISTINCT [NAME], [ADDRESS]
FROM TABLE1
WHERE NOT EXISTS
(SELECT *
FROM TABLE2
WHERE TABLE1.NAME = TABLE2.NAME) ;

The second is much cleaner; however, don't be surprised if Access rewrites
it to look like the first anyway.

 

[Ron]

Sorry, I changed the names from the real table/query names. The query is
actually calling other queries so I suppose the real form would be like:

SELECT DISTINCT B_NAME, A_NAME
FROM TABLE1
WHERE B_NAME NOT IN
(SELECT DISTINCT B_NAME
FROM TABLE2
WHERE SERIAL_NO In
(SELECT SERIAL_NO FROM TABLE3));


I will try using 'exists' and see how it goes.

Regards,
Ron.

Are both Name and Address in the same index in TABLE1? That could really help
with the Distinct and the NOT IN if there are more than a few hundred
records. Same goes for Name in Table2..

If IN or NOT IN are slow, I always try EXISTS or NOT EXISTS.

SELECT DISTINCT [NAME], [ADDRESS]
FROM TABLE1
WHERE (((Exists
(SELECT *
FROM TABLE2
WHERE TABLE1.NAME = TABLE2.NAME))
=False));

Or

SELECT DISTINCT [NAME], [ADDRESS]
FROM TABLE1
WHERE NOT EXISTS
(SELECT *
FROM TABLE2
WHERE TABLE1.NAME = TABLE2.NAME) ;

The second is much cleaner; however, don't be surprised if Access rewrites
it to look like the first anyway.



--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a query as follows that takes about 5 minutes to execute but produces
the correct results:

SELECT DISTINCT [NAME], [ADDRESS]
FROM TABLE1
WHERE NAME NOT IN (SELECT NAME FROM TABLE2);


I have tried replacing it with a join which is much faster but still shows
some rows where TABLE1.NAME is in TABLE2.NAME. I have tried different
operators (<,>) and join types but can't get it to work properly:

SELECT DISTINCT TABLE1.[NAME], TABLE1.[ADDRESS]
FROM TABLE1, TABLE2
WHERE TABLE1.NAME <> TABLE2.NAME;

Any help appreciated,
Ron.