Question about query optimization

[Sunil Somani]

Hi All,

i have one query in ms access Database 2002.
which is like:
select distinct ( Field1) from table1 where Field1 not in ( select Field1
from table2) ;

Table1 has more than 70000 records I guess even more..
So when i open ms access database and run this query it run fine and around
30-40 minutes it returns with result,
But after this If I try to run the same query again , no result at all no
matter how long I wait.
And again If I close the database reopen and run i get the results.

Could you please advise how to resolve such strange issue.

Please help.

Thank you,

Best regards,
Sunil Somani

 

[Sylvain Lafontaine]

Usually, a no result with an IN clause when something is expected is
provoqued by the presence of a Null value in the IN list, so you should try
with:

select distinct ( Field1) from table1 where Field1 not in ( select Field1
from table2 WHERE Field1 Is Not Null) ;

30-40 minutes is an awful amount of time for a query based on only 70000
records; possibly something is not right with your network or the server.
Are you running this on your local machine or from a server?

Try with the following variation to see if it can help to make the query
going a little faster:

select distinct ( Field1) from table1 Left Join Table2 on Table1.Field1 =
Table2.Field1
WHERE Table2.Field1 Is Not Null

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Bob Barrows]

Hi All,

i have one query in ms access Database 2002.
which is like:
select distinct ( Field1) from table1 where Field1 not in ( select
Field1 from table2) ;

Table1 has more than 70000 records I guess even more..
So when i open ms access database and run this query it run fine and
around 30-40 minutes it returns with result,

Errr ... I would not consider that running "fine".

But after this If I try to run the same query again , no result at
all no matter how long I wait.
And again If I close the database reopen and run i get the results.

Could you please advise how to resolve such strange issue.

First of all, stop using "where ... not in (select ... )". Either use an
outer join or "where not exists(select ... )" instead. Like this:

select distinct ( Field1) from table1 where not exists
(select * from table2 where field1 = table1.field1)

or

select distinct t1.field1
from table1 as t1 left join table2 as t2
on t1.field1=t2.field1
where t2.field1 is null

Either will outperform the "not in (..) " criterion.

Next, make sure you have an index on Field1 in both tables.

 

[Jerry Whittle]

I agree with Bob about trying something like a NOT EXISTS if the NOT IN isn't
working well.

However the fact that it works the first time but not the second is a little
strange. How large is the database file size?
Are the tables in the same database file as the query or linked?
Have you tried a compact and repair?
Are you getting any error messages?