Looping through a Large recordset.

[Dean W]

I have to loop through 4.2Million records updating as I go.
In a Access 2003 .mdb file using DAO.
The first 500,000 records process in 20min. as it continues to run it slows
to a crawl processing 120,000 records per hour. Does anyone know of any
fixes for this? or known issues?

 

[Allen Browne]

The slowdown could relate to physical RAM to trying to manage the indexes.

Consider:
a) Is it possible to do this with an Update query? Would that be more
efficient?

b) Destroy any indexes you are not using, make the changes, and create the
indexes again later.

c) Break it down into (say) 8 recordsets, each 1/8th of the original. See if
they all run fast this way.

d) See if using transactions makes it faster or slower.

e) There's a danger of exceeding the 2GB file size. You may want to create a
new table (in another MDB), and write the updated records there. This would
also have the side-benefit of giving you a complete rollback (i.e. the
original is unchanged) if anything goes wrong.

 

[ChrisO]

I guess, with the information supplied, it’s crystal ball time.

Do you have anything in the 4.2 million record loop that looks like this: -

SomeString = SomeString & SomeOtherValue

You may need to clear SomeString at the top of the iteration else SomeString
will get very large and start going to disk.

Failing that I think we would need to see the code.