Slow Query

R

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.
 
S

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

*
 
J

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.
 
R

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.




Jerry Whittle said:
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.


Ron said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top