Query running too slooooow...

[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!

 

[MacDermott]

Be sure you have an index on each field which is being compared.

HTH
- Turtle

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.

 

[Dirk Goldgar]

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!

If this is a followup to that question I answered earlier about
phone-number matches (thread subject "Macro Help"), the query I
suggested is not going to be very efficient because you're having to
match on an expression that concatenates two fields. I suggested that
approach because I had no information about the structure of your tables
and indexes, but I had no idea you had so much data to work with. If
you'd like to post the structure of your tables, including which fields
have indexes and which are primary keys, we may be able to come up with
a more efficient solution. Such a solution may involve preliminary
steps such as creating additional indexes.

Given the table size of 3.2 million records, you could also be running
into problems with the maximum size of an Access database file. Is this
an .mdb or an .adp (Access Data Project) that you are working with? Are
these tables all stored in the local database, or are they linked
tables? How big is the compacted database file at the start of this
process?