On Feb 8, 2006, at 17:24, [EMAIL PROTECTED] wrote:
If you do not do a BEGIN...COMMIT around your inserts,
then each insert has an implied BEGIN...COMMIT around itself.
That means you are doing 50 COMMITs.
A COMMIT is slow because it is "Durable" (The "D" in ACID).
That means that the operation will not complete until all the
information is safely written to the surface of the disk and
can survive a power failure. Making sure everything is on
the disk surface, and not just in OS cache buffers, normally
requires 2 or 3 complete rotations of the disk platter.
Depending on your disk drive, the platter probably spins
about 120 times per second. So a COMMIT requires about
1/40th to 1/60th of a second to complete. The CPU is mostly
idle during this time - the time is spent waiting on the disk
platter to rotate under the write head again. But it is still
time.
By wrapping the inserts into a single transaction, you only
do a single COMMIT at the end, instead of 50 individual COMMITs.
The final commit is a lot bigger, but it still only requires
2 or 3 disk platter rotations, so it does not require any
extra wall-clock time. 50 times less waiting makes things
run a lot faster.
Really really interesting, I had no idea there were so many fine
details behind a transaction.
Thank you all for your responses!
-- fxn
