sqlite - Performance Considerations

Not logged in

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

• Contents
• Diff
• Text

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