Access bogging down while processing big file

[denbro442]

I'm using Access 2000 to hold up to 500,000 rows of data and then run a
vba macro to comb through the data to do some dynamic calculations
(can't do them with access macros). My problem is the larger the file
gets the vba macro exponentially runs slower as it gets into the
dataset. Is there a memory and/or cache I need to clear during the
run? What if I grabbed a predetermined number of rows, set a bookmark,
processed them and then go back to the bookmark and repeat until EOF?
I'm not sure what is bogging it down.

 

[Albert D.Kallal]

A few things:

If you can open the table WITHOUT a order, then reading of records will be
MUCH MUCH faster (this is because a bucket, or frame is read of 2000 bytes.
The number of records that FITS into that bucket is NOW in memory. If you
use a index order, then to retrieve ONE record, you must pull a WHOLE bucket
TO GET THAT one record. So, if you can read the file in-order, you will get
VERY good performance (since each bucket read will contain many records).


So, open the table directly...not with sql ...

dim rstData as dao.RecordSet

set rstData = currentdb.OpenRecordSet("tblcustomers")

do while rstData.Eof = false

.....
rstData.MoveNext
loop
rstData.Close
set rstData = nothing

the above code shell shows how you can read data as disk order.

However, if out of the 500,000 records, you can retrieve a small data set,
say only a few thousands records, then likely you should

eg:

dim rstData as dao.RecordSet

set rstData = currentdb.OpenRecordSet("select name, city from tblcustomers
where City = 'Edmonton' ")

do while rstData.Eof = false

etc....

The next thing to be aware of is that you CAN NOT create a reocrdsets inside
of your processing loop. This is death to performance. You likely can read,
and process 10,000 to 50,000+ records in the time it takes to OPEN ONE
reocrdset. So, if you got any reocrdset creating in the loop, get rid of it.

Further, if you need to pull records from a related table, use relational
joins, not dlookup, or some type of findfirst on a recordset.

Further, if you are using a findfirst, you will want to replace that with
the dao "seek", as it is 1000's of times faster. And, if your design uses
linked tables, then use the following code snip so you can continue to use
seek...

http://www.mvps.org/access/tables/tbl0006.htm

 

[denbro442]

Albert,

This is awesome! I will put these enhancements in and am sure that is
the difference. Thanks for taking the time to respond!

I was also looking into using begintrans and committrans in chunks as
I'm cranking through the data.

I think there is some room for improvement here...

....open db, do some validation, then proceed to the base calcs...

min_sls_cnt = 0
wos_max_cnt = 0
wos_throt_cnt = 0

Do Until rst.EOF
wos_max = 0
For i = 1 To 52
wos_sls = 0
For wos_sum = 1 To wos
wos_sls = wos_sls + rst.Fields(wos_sum + i).Value
Next wos_sum
If wos_sls > wos_max Then
wos_max = wos_sls
End If
Next i
If wos_max * throttle < min_max Then
wos_max = min_max
wos_max_cnt = wos_max_cnt + 1
Else
wos_max = wos_max * throttle
wos_throt_cnt = wos_throt_cnt + 1
End If

With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Style ID") = Left(rst.Fields(0), 9)
.Fields("Store Number") = Left(rst.Fields(1), 4)
.Fields("Size Code") = Mid(rst.Fields(0), 11, 4)
.Fields("Parameter Value") = Round(wos_max, 0)
.Fields("Parameter ID") = "SMX"
' add more fields if necessary...
.Update ' stores the new record
End With
cur_rec = rst.AbsolutePosition
....finish loop

Thanks,
Pete

 

[Albert D.Kallal]

It is not clear, where (or why) you are appending reocrds to the same
reocrdset (rst) as your main loop.

If the above occurs for each record, then we likely got some normalizing
problems with your design. Regardless, you could test and see if opening a
2nd reocrdset that you use to append only helps here, as you want to keep
the pointer in the first reocredset exactly where it is..and not jump it
around. (I don't know if this will help...but I would try if you are
appending for each record processed to the same table). Further, you might
open the first table/reocrdset as a forward only reocrdset..and perhaps even
as read only also. (you would have to test this to see if it helps...).