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
|
|||
|
|||
Question about query optimization
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 |
#2
|
|||
|
|||
Question about query optimization
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) "Sunil Somani" wrote in message ... 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 |
#3
|
|||
|
|||
Question about query optimization
Sunil Somani wrote:
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. -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#4
|
|||
|
|||
Question about query optimization
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? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Sunil Somani" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|