SQLite insert performance

Question

I need to write single lines to a file placed on a file server. I am considering utilizing SQLite to ensure that the writing of the line was successful and not just partially completed. However, as insert performance is really essential. My question is, what is the exact process (as it read this, write that, read this and so on) that SQLite goes through when it inserts a row to a table. The table does not have any indexes, nor primary keys, constraints or anything.

Answer 1

This is the most common bottleneck in my experience:

http://www.sqlite.org/faq.html#q19

Except for that, SQLite is really fast. :)

Answer 2

you can use explain for details what happens when you execute a statement: http://www.sqlite.org/lang_explain.html

Answer 3

You should use transactions and thus try to avoid fsync()-ing on every INSERT. Take a look here for some benchmarks.

Also, be sure to properly set the two important pragmas:

• synchronous (NORMAL)
• journal_mode (WAL)