Xavier Noria <[EMAIL PROTECTED]> wrote: > I have a simple schema and a sql loader that fills a table with > initial values: > > delete from foo; > insert into foo ...; > insert into foo ...; > ... about 50 inserts ... > > To my surprise, the execution of these inserts took a few seconds > (SQLite is 3.3.3). However, if I wrapped the entire loader in a > transaction: > > begin transaction; > delete from foo; > insert into foo ...; > insert into foo ...; > ... about 50 inserts ... > commit transaction; > > then it was immediate. Why? >
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. -- D. Richard Hipp <[EMAIL PROTECTED]>
