Faster bulk inserts in sqlite3?

• wrap all INSERTs in a transaction, even if there's a single user, it's far faster.
• use prepared statements.

You want to use the .import command. For example:

$ cat demotab.txt
44      92
35      94
43      94
195     49
66      28
135     93
135     91
67      84
135     94

$ echo "create table mytable (col1 int, col2 int);" | sqlite3 foo.sqlite
$ echo ".import demotab.txt mytable"  | sqlite3 foo.sqlite

$ sqlite3 foo.sqlite
-- Loading resources from /Users/ramanujan/.sqliterc
SQLite version 3.6.6.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from mytable;
col1    col2
44      92
35      94
43      94
195     49
66      28
135     93
135     91
67      84
135     94

Note that this bulk loading command is not SQL but rather a custom feature of SQLite. As such it has a weird syntax because we're passing it via echo to the interactive command line interpreter, sqlite3.

In PostgreSQL the equivalent is COPY FROM: http://www.postgresql.org/docs/8.1/static/sql-copy.html

In MySQL it is LOAD DATA LOCAL INFILE: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

One last thing: remember to be careful with the value of .separator. That is a very common gotcha when doing bulk inserts.

sqlite> .show .separator
     echo: off
  explain: off
  headers: on
     mode: list
nullvalue: ""
   output: stdout
separator: "\t"
    width:

You should explicitly set the separator to be a space, tab, or comma before doing .import.

You can also try tweaking a few parameters to get extra speed out of it. Specifically you probably want PRAGMA synchronous = OFF;.

I've tested some pragmas proposed in the answers here:

• synchronous = OFF
• journal_mode = WAL
• journal_mode = OFF
• locking_mode = EXCLUSIVE
• synchronous = OFF + locking_mode = EXCLUSIVE + journal_mode = OFF

Here's my numbers for different number of inserts in a transaction:

Increasing the batch size can give you a real performance boost, while turning off journal, synchronization, acquiring exclusive lock will give an insignificant gain. Points around ~110k show how random background load can affect your database performance.

Also, it worth to mention, that journal_mode=WAL is a good alternative to defaults. It gives some gain, but do not reduce reliability.

C# Code.

• Increase PRAGMA cache_size to a much larger number. This will increase the number of pages cached in memory. NOTE: cache_size is a per-connection setting.

• Wrap all inserts into a single transaction rather than one transaction per row.

• Use compiled SQL statements to do the inserts.
• Finally, as already mentioned, if you are willing forgo full ACID compliance, set PRAGMA synchronous = OFF;.