Re: [sqlite] question about performance
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
Re: [sqlite] question about performance
On Wed, 8 Feb 2006, Xavier Noria wrote: >On Feb 8, 2006, at 17:10, Doug Nebeker wrote: > >> When you don't wrap everything in a transaction, each statement becomes >> it's own transaction. And the database file is opened, updated, and >> closed on each transaction. So your first case had roughly 50 times >> the amount of file I/O and transaction startup/commit overhead as the >> second case. > >I see. Let me ask a few more questions to help me get the picture. > >A transaction is stored in memory until committed? As a rule of thumb >can I imagine that one transaction equals roughly to a single open/ >edit/close? Is the file opened and locked right when the transaction >begins? It is held in memory until it is committed, or the page cache is splilled. At that point, the database file is locked for writing, a rollback journal created, old page values written to the rollback journal and new page values written to the database file. It is this creation of files that must be done synchronously for durability, and is done for each statement level transaction in the original example (and once for the large transaction.) So no, the file is not opened and locked right away, unless you explicitly tell SQLite to using "BEGIN EXCLUSIVE;". If you're interested in the details, check out: http://www.sqlite.org/lockingv3.html http://www.sqlite.org/lang_transaction.html > >-- fxn > > > -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] question about performance
On Feb 8, 2006, at 17:10, Doug Nebeker wrote: When you don't wrap everything in a transaction, each statement becomes it's own transaction. And the database file is opened, updated, and closed on each transaction. So your first case had roughly 50 times the amount of file I/O and transaction startup/commit overhead as the second case. I see. Let me ask a few more questions to help me get the picture. A transaction is stored in memory until committed? As a rule of thumb can I imagine that one transaction equals roughly to a single open/ edit/close? Is the file opened and locked right when the transaction begins? -- fxn
Re: [sqlite] question about performance
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]>
RE: [sqlite] question about performance
When you don't wrap everything in a transaction, each statement becomes it's own transaction. And the database file is opened, updated, and closed on each transaction. So your first case had roughly 50 times the amount of file I/O and transaction startup/commit overhead as the second case. -Original Message- From: Xavier Noria [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 08, 2006 10:04 AM To: sqlite-users@sqlite.org Subject: [sqlite] question about performance 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? -- fxn To find out more about Reuters visit www.about.reuters.com Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of Reuters Ltd.
Re: [sqlite] question about performance
Xavier Noria said: > 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? When you didn't wrap it in a transaction, you really had about 50 little transactions. That gets expensive in a hurry. So when you're doing bulk inserts remember to make transactions. When I had to insert a 800k records into a database I found that is changed insertion time from hours to a few minutes. Clay Dowling -- Simple Content Management http://www.ceamus.com
[sqlite] question about performance
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? -- fxn
