[RESOLVED] Recommendations on how many records to buffer before doing .Update

[JDM1965]

I did a quick Google on this question but couldn't find anything. I have an application which reads through a text file and processes the lines into DB records, which I then store in a recordset object via .AddNew. After I've finished reading the entire file, I then commit the new records to the DB using .Update.

My concern is that said file may in some cases be on the ginormous side, perhaps up to several hundred thousand records. So I'm wondering whether there are any recommended practices, rules of thumb, whatever, as to how many records you should add to the recordset before doing the .Update? (I'm assuming that doing an .Update with every record is a bad idea wrt overall performance of the app.)

[si_the_geek]

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

Unless you have explicitly set it up for batch updating, you are adding one record at a time to the database as you add them to the recordset - because it happens as soon as you .Update, or move to a different record (via .MoveNext or .AddNew etc).

While batch updating does give a speed boost it adds problems, such as increased memory usage, and errors happening for earlier records (rather than just the current one, where you can handle/ignore it).

If the performance is acceptable without batch updating, it probably isn't worth changing to it unless you desire ultimate speed.

[JDM1965]

So since I'm using adLockPessimistic (i.e. not batch), is there any difference in performance, functional results, (or whatever else), between:

For nLoop = 0 to nUmpteenTimes
   rsBlah.AddNew vSomeFields,vSomeValues
Next nLoop
rsBlah.Update

...and...

For nLoop = 0 to nUmpteenTimes
   rsBlah.AddNew vSomeFields,vSomeValues
   rsBlah.Update
Next nLoop

Or -- is the .Update actually superfluous in this case?

[si_the_geek]

I suspect the performance is slightly worse with the implied update (as in the first of those snippets), but I'm not sure.

In terms of functionality they should be the same as long as the code is that simple, but as it gets more complex the functionality can suffer with the implied update - and not just in your program, but in any way the database is used (because your program is likely to lock records/pages for longer).

Another issue is the readability, because the explicit .Update makes what is happening much clearer.

[JDM1965]

Thanks very much. I'll go with the second example (.Update every iteration) and see how it goes. I have a feeling performance won't exactly blaze anyway when I hit 100K+ records, hopefully though it'll be reasonable.