Re: Performance comparison on Update vs. UpdateBatch - record by record
From: Mark J. McGinty (mmcginty_at_spamfromyou.com)
Date: 10/31/04
- Next message: jerry chapman: "Convert RDS to ADO"
- Previous message: Val Mazur: "Re: MDAC 2.8 vs MDAC 2.7"
- In reply to: Michael D. Long: "Re: Performance comparison on Update vs. UpdateBatch - record by record"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 30 Oct 2004 18:31:31 -0700
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.long-nospam@comcast.net> wrote in message
news:e5TcovqvEHA.1984@TK2MSFTNGP14.phx.gbl...
> 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" <jhan@infinityinfo.com> wrote in message
> news:uOdehZPvEHA.1300@TK2MSFTNGP14.phx.gbl...
>>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
>
>
- Next message: jerry chapman: "Convert RDS to ADO"
- Previous message: Val Mazur: "Re: MDAC 2.8 vs MDAC 2.7"
- In reply to: Michael D. Long: "Re: Performance comparison on Update vs. UpdateBatch - record by record"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
