asked on

Why is this loop slowing down as number of records are processed?

This following code took 13 minutes!
The Label1 counter was fast in the beginning but as the loop progessed it slowed down to a crawl.
Why would this happen and how can i correct it?

lstgVC is a mySQL local database
lstgSrc is msAccess database

thanks
phil

Do Until lstgVC.EOF
inCnt = inCnt + 1
Label1.Caption = inCnt & " of " & recCnt
Label1.Refresh
DoEvents
lstgSrc.Open "SELECT id FROM Table1" _
& "WHERE id=" & lstgVC!ID, _
cnSrc, adOpenKeyset, adLockOptimistic
If lstgSrc.RecordCount = 0 Then
lstgVC!Status = "U"
lstgVC!DeleteFlag = 1
'lstgVC.Update
lstgVC.CancelUpdate <---------------------note that I'm not even doing an update
outCnt = outCnt + 1
txtStatus.Text = txtStatus.Text & vbCrLf & str
txtStatus.Refresh
End If
lstgSrc.Close
Set lstgSrc = Nothing
lstgVC.MoveNext
Loop

___XXX_X_XXX___

because of this line:

txtStatus.Text = txtStatus.Text & vbCrLf & str

Remove it and try again

___XXX_X_XXX___

If no such slowing occurs, replace it with :

txtStatus.SelStart=Len(txtStatus.Text)
txtStatus.SelText=vbCrLf & str

spoowiz

ASKER

hi,
that's not it. i even commented it out and still slows down.
btw, lstgVC selection count is only 2900 records. By about 900 records into it starts slowing down. by 1200 records into it, it REALLY slow, maybe 3 to 5 records per second.
phil

___XXX_X_XXX___

Ok , comment everythink between Do...Loop and keep only:

lstgSrc.Open "SELECT id FROM Table1" _
& "WHERE id=" & lstgVC!ID, _
cnSrc, adOpenKeyset, adLockOptimistic

Is problem occurs again ?

spoowiz

ASKER

Good idea.
I took out basically everything but lstgSrc.open/close and it behaves the same.
I then took out the lstgSrc.open/close and takes no time to finish 2800 records.
So it must be the lstgSrc.Open stmt causing it.
I wonder why.

spoowiz

ASKER

Another thing... the msAccess database table I'm opening (lstgSrc) is a linked table from msAccess to a .dbf (dbase) file.

dfiala13

do you have an index on the ID column of your Access table?

spoowiz

ASKER

yes. the dbase file has an index on the ID field and opening it in design mode from msAccess shows that it it is indexed.

___XXX_X_XXX___

Try change this:
lstgSrc.Open "SELECT id FROM Table1" _
& "WHERE id=" & lstgVC!ID, _
cnSrc, adOpenKeyset, adLockOptimistic

With this:
Dim rstRecordset As Recordset
.....
.....
Set rstRecordset=cnSrc.Execute("SELECT id FROM Table1 WHERE id=" & lstgVC!ID)

Keep other lines commented. Again same problem ?

spoowiz

ASKER

same.

ASKER CERTIFIED SOLUTION

___XXX_X_XXX___

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

spoowiz

ASKER

I re-wrote by inserting all the ID's from the dbase file to another msAccess TEMP table I just created and ran it. It finished in 13 seconds. Many repetetive calls to the dbase file seems to be the problem. Well, hopefully I need to do this in another place. The workaround is to use the temp file. Thanks.

___XXX_X_XXX___

Good luck !