SQLite insert performance

问题:

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.

回答1:

This is the most common bottleneck in my experience:

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

Except for that, SQLite is really fast. :)

回答2:

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)

回答3:

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