sqlite - Performance Considerations

Not logged in

• Honeypot
• Browse
• Home
• Login
• Reports
• Search
• Ticket
• Timeline
• Wiki

• Contents
• Diff
• Text

The Default complilation use -O6 which doen't exist with gcc (=-O0) using -O3 -Os improve the performance of sqlite.

Michel Weinachter 2004-10-6

---

Benchmarks already run show that SQLite has outstanding performance, even compared with MySQL, which has been the consistent speed demon of choice for web applications. However, these benchmarks have the caveat that they were run on single connections to the databases in question and no attempt was made to see how well SQLite or the other RDBMSs handled concurrent connections. This is a big deal with web apps. From experience I can testify that MySQL already performs very well with hundreds of concurrent connections. Does anyone have experience with SQLite in this regard?

--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?