Page History
- 2007-May-05 11:55 drh
- 2007-May-05 09:41 anonymous
- 2006-May-18 05:34 anonymous
- 2005-Dec-29 00:21 drh
- 2005-Dec-28 21:23 anonymous
- 2005-May-16 00:03 anonymous
- 2005-May-13 12:57 anonymous
- 2005-Feb-17 21:00 anonymous
- 2005-Feb-17 20:57 anonymous
- 2004-Oct-06 10:52 anonymous
- 2004-Sep-26 13:45 anonymous
- 2003-Sep-06 01:19 drh
- 2003-Sep-05 09:09 anonymous
- 2003-Sep-05 09:06 anonymous
- 2003-Jul-11 15:42 anonymous
--Carl Youngblood
Transactions and performance
When doing lots of updates/inserts on a table it is a good idea to contain them within a transaction,
begin; insert into table values (..); insert into table values (..); insert into table values (..); .... commit;
This will make SQLite write all the data to the disk in one go, vastly increasing performance.
However, if you are writing to a temporary table, transactions have less effect because disk writes are not flushed to the table after each write.
I did a timed test with inserting 1000 records into a table in various ways to compare performance:
- main database table without transaction - 5 seconds
- main database table with transaction - 0.1 seconds
- temporary table without transaction - 2 seconds
- temporary table with transaction - 0.1 seconds
So, performance is still vastly quicker with a transaction when writing to a temporary, but transactions have the drawback of locking the entire database file for the duration of the transaction, even though only a temporary file is being written to, so, in multithreaded applications, it may be worth putting up with the lower performance to avoid this database locking behaviour.
--Paul Smith
D. Richard Hipp on 2003-09-05:
I tried a similar experiment on RedHat 7.3 using SQLite 2.8.6 and got numbers like this:
- main database without transaction - 6.204 seconds
- main database with transaction - 0.063 seconds (100x faster!)
- temp table without transaction - 0.286 seconds
- temp table with transaction - 0.067 seconds
So writing to the main database is about 100x slower without a transaction. Writing to a temporary table is about 4x slower. I had expected writing to a temporary table to be the same speed regardless of whether or not it is within a transaction. The 4x slowdown is puzzling. I will be looking into this....
E. Russel Harvey on 2004-09-25: Does a transaction caused file locking prevent not only writing but also reading from other access, which may be from a thread of the same process that SQLLite is running?