Performance comparison on Update vs. UpdateBatch - record by record

[Jiho Han]

I was wondering if there are any significant performance difference between using Update vs. UpdateBatch (adLockOptimistic vs. adLockBatchOptimistic).

I don't mean to batch up changes and call UpdateBatch. I would be calling UpdateBatch on every record change basically.
I know I could simply use Update in this case but that's not the point.

Thanks much.
Jiho

[Anushi (Grapecity)]

Hi Jiho,

As quoted by an MVP...Update updates current record in recordset, in case if
you have changes in
that recordset. UpdateBatch provides updating of multiple records in
database in one shot. In case if
you have edited current record, then UpdateBatch first calls Update and then
send changes from all changed
records to the database. It is usefull feature in case if your recordset is
disconnected from database, you make some chages in recordset, reconnect it
back to database and call UpdateBatch. In that case all changes from
recordset will be applied against database in one shot, which improves
performance of updating. But you should be aware, that not all providers
support UpdateBatch feature and it could have some limitations in each
particular case.
When dealing with batch updates you have to think about the scenario that
one user will change a record before changes made by another user editing
the same record are saved. So you have to take care of detecting and
resolving conflicts.
Additional reference :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcondetectresolveconflicts.asp

HTH,
Anushi

"Jiho Han" <jh...@infinityinfo.com> wrote in message
news:uOdehZPv...@TK2MSFTNGP14.phx.gbl...

[Val Mazur]

Hi Jiho,

I think you cannot compare Update and UpdateBatch for one reason. Update
updates data in a recordset, but UpdateBatch transfers all the updated data
to the database. When you call Updatebatch, it actually calls Update to
apply all the current changes to the recordset.
You could compare performance of the UpdateBatch and SQL statements, like
INSERT/UPDATE/DELETE

--
Val Mazur
Microsoft MVP

"Jiho Han" <jh...@infinityinfo.com> wrote in message
news:uOdehZPv...@TK2MSFTNGP14.phx.gbl...

[Michael D. Long]

Why not test it. Perform a benchmark of 500,000 rows using .Update against
the same data set using .UpdateBatch and look at the difference. Make
certain to start with an identical blank database in each case, and to
ensure that machine resources are in the same state (a reboot of the
machine(s) involved prior to each test is a good idea).

I would put forth the hypothesis that .UpdateBatch incurs more overhead on a
single row update and will be marginally slower in your use proposed
scenario.

--
Michael D. Long

"Jiho Han" <jh...@infinityinfo.com> wrote in message
news:uOdehZPv...@TK2MSFTNGP14.phx.gbl...

[Mark J. McGinty]

My bet would be that Update and UpdateBatch will perform almost identically
when tested using a single connection, but will show some variance as
concurrency issues crop up. BatchOptimistic locking actually incurs
slightly less lock overhead than Optimistic, the former only has to take a
write lock as it's updating. The latter takes an intent lock and then
negotiates a write lock from that... hmm, it's surely provider-specific too,
all of the above assumes SQL Server...

Another big factor would be whether or not the update takes place inside a
transaction, UpdateBatch takes a big hit inside a transaction, that doesn't
seem as severe when just calling Update -- but that is likely recovery mode
specific.

Anyway, I'd expect the significant overhead to come in the form of client
processing of update failures, and that would be very difficult to factor-in
based on a one-connection drag race of the two calls... not that a drag race
is totally without value...

A few judicious PerfMon counters can add value to the results, particularly
the lock-related counters. Methodologies that incur severe numbers of locks
often perform nicely when tested with a small number of connections, but
will typically tend to scale negatively.

One last point, I prefer performing each test at least three times per
method, and alternating them, over rebooting in-between tests. If one
methodology leaks resources or is slow to cut them loose, that's a
significant factor. If you perform alternating tests and the results of
each don't vary widely for each respective methodology, you can assume they
were playing on a level field. If either or both degrade significantly in
subsequent tests, then you need to know why, or at least which is the
culprit. Because watching a production server's performance degrade as it
runs is surely nothing to opt in, inadvertently or otherwise, and rebooting
such a server while it has work to do is a fix that brings no joy.

Hmm, I probably could've constructed such a test in the time this conjecture
took, perhaps I'll do that. Assuming I do, is anyone else interested in
running it/posting results?

-Mark

"Michael D. Long" <michael.d....@comcast.net> wrote in message
news:e5Tcovqv...@TK2MSFTNGP14.phx.gbl...

[Jiho Han]

I would be willing.

By the way, in my own testing - not-even-close scientific -, they didn't have any siginificant difference.
I had about 2000 records to insert. Remember I am calling UpdateBatch on every AddNew, so it's as if I am doing Update anyway.

They both took around ~30 seconds with BatchOptimistic being tad bit faster for some reason.
For me, this is good enough knowing that there won't be a significant difference this way but still a better testing would be an interesting project.

Thanks for all of your responses.

Jiho