Query Running TOO SLOOOOW!

[Guest]

I'm running a query where I'm copying records that match on table A and table B and inserting them into table C.

Now Table A is about 50,000 records but Table B is a monster at 3.2 million records...Table C is blank for moment.

My problem. Well, I've run it twice and it seems my computer slooows down to a crawl. First time I let it go for about 90 mins and it's currently been going for about an hour. I exited out of it first time and made some modifications to data to see if that could speed things up and doesn't seem to be doing it.

I did check my SQL on limited data and that's not problem.

Now my computer is pretty high end with what I think is sufficient memory 512K and I closed out of unecessary programs.

Is there some kind of limitation to the number of records one can query efficiently in ACCESS? (2003)

Thanks!

 

[Michel Walsh]

Hi,


What is the text of your query? NOT IN( ) is poorly optimized in Jet
(and in some other db too, but not in MS SQL Server 2000). it could be
preferable to use a test on a null value returned by an outer join. You
have indexes on table B for the fields involved in the comparison?

Hoping it may help,
Vanderghast, Access MVP

I'm running a query where I'm copying records that match on table A and

table B and inserting them into table C.

Now Table A is about 50,000 records but Table B is a monster at 3.2

million records...Table C is blank for moment.

My problem. Well, I've run it twice and it seems my computer slooows down

to a crawl. First time I let it go for about 90 mins and it's currently been
going for about an hour. I exited out of it first time and made some
modifications to data to see if that could speed things up and doesn't seem
to be doing it.

I did check my SQL on limited data and that's not problem.

Now my computer is pretty high end with what I think is sufficient memory

512K and I closed out of unecessary programs.