[AccessD] Speed enhancement

On Fri, Aug 29, 2003 at 05:41:23PM -0500, John Bartow wrote:
> An easy question for you speed optimizing gurus out there:
> What should I replace this code (which was meant for a few record updates)
> with to make it run faster (it now needs to be used for 500+ records). Watch
> for line wrap.
> 
> Set rstTemp = db.OpenRecordset("TempMailList")
> Do Until rstTemp.EOF
> lngRecordID = rstTemp!MailListID
> DoCmd.RunSQL "UPDATE MailList SET MailList.StaffID = " & lngNewStaff & ",
> MailList.DeptTypeID = " & lngNewDept & " WHERE (((MailList.MailListID)=" &
> lngRecordID & "));"
> lngCount = lngCount + 1
> rstTemp.MoveNext
> Loop

I've never timed DoCmd.RunSQL, but I've read that it was a kludge and 
runs slow. The fastest solution would be to have the lngNewStaff and 
lngNewDept in the TempMailList table (if it's a table). That would allow 
an update with a single SQL statement. If you made it a query and 
optimized it, it would run even faster. Anything you do with an optimized 
query will run a *lot* faster than the quivalent VBA code.

If you can't get rid of the loop, you might try a query with lngNewStaff, 
lngNewDept, and lngRecordID parameters. I believe that will be optimized 
when it is run and saved, so it should run faster than an SQL string. 
But I think you're not going to see a big increase in speed unless you 
get rid of the loop.

Bob Hall