Can I optimize this further?

[Chatbox]

Can I make the operation time of the Do While loop shorter?
Currently, on my lappy (PentiumM 1.7GHz), the loop takes 6.5 secs (average) to go through 480 records...

    Set fieldObject = rst.Fields(changeField)
    
    Select Case changeOperator
      Case "Add"
        If fieldObject.Type <= 3 And IsNumeric(changeString) Then
          StartTime = Timer
          changeValue = CDec(changeString)
          Do While Not rst.EOF
            fieldObject.value = Round(fieldObject.value + changeValue, 2)
            rst.MoveNext
          Loop
          MsgBox Timer - StartTime
          rst.MoveFirst
        Else
          MsgBox "The selected field or input text is not a numeric data type."
        End If

....and the rest doesn't matter

 

[chappy]

I believe loops execute faster when you minimize calculations using direct object references. Try assigning the fieldobject.value to a local variable before you perform the calculation:

Do While Not rst.EOF
fld_val = fieldobject.value
fld_val = Round(fld_val + changeValue, 2)
fieldobject.value = fld_val
rst.MoveNext
Loop

There are two more lines of code, but the calculation step should be faster because it's processing a local variable instead of a referenced recordset value. Hope that helps.

-Chappy

 

[Chatbox]

Thanks for the input...that's kind of strange, because within the loop, the field is still only being referenced the same number of times. Anyway, I'll give it a try.

Also, I found a problem with it today:
Say I have a recordset that has 3000 records.
And the do while loop actually loops through it from start to finish, which works fine...except...It goes slower and slower (per record) as it reaches later records (say around 1500 or so....)...and it gets even slower after that...and continues to get worse. CPU load was at 100%, Access was using 97%, this was tested on multiple occasions...with the same side effect.

Any insight as to why this is?

 

[Pat Hartman]

You've cropped the code but from what I see, you should be running an action query. It will be faster than a code loop. Although, the only thing I see that might be wrong with the code loop is the CDec() If Decimal is not the data type of the recordset field you are forcing Access to convert that field each time it needs to use it. I also can't see the definiton of FieldObject. Is it defined as a field or just as an object. If it is defined as a generic object, Access needs to figure out what type of object it is each time it is referenced.

 

[Chatbox]

I'm now running an actionquery... (spent the whole day looking for ways to improve it...)
It's now sooooo damn much faster.

 

[RoyVidar]

Yup - action queris is the way to go, and is most often recommended over recordset approaches anytime!

Since you've timed your initial approach (6.5 seconds seem a bit much for that number of records, though), would you mind sharing your timed results on the action query? It may help those why are still struggling with making recordset approaches work faster when an action query would be a better approach.

 

[Chatbox]

Now it only takes 6.348 seconds (averaged over a few times) to go through the same changes (changing one field of each record)....but applied to 120,000 records.

Definitely a lot faster.

So I take it that recordsets are only good for small number of records?

 

[RoyVidar]

Thank you for sharing the results of the timing!

Recordsets are good for, and necessary for a lot of stuff, but for updates, appends, deletes... executing action queries will almost always be faster than performing the same thru recordsets.