SQLite Insert very slow?

Question

I recently read about SQLite and thought I would give it a try. When I insert one record it performs okay. But when I insert one hundred it takes five seconds, and as the record count increases so does the time. What could be wrong? I am using the SQLite Wrapper (system.data.SQlite):

dbcon = new SQLiteConnection(connectionString); dbcon.Open();  //---INSIDE LOOP   SQLiteCommand sqlComm = new SQLiteCommand(sqlQuery, dbcon);   nRowUpdatedCount = sqlComm.ExecuteNonQuery();   //---END LOOP  dbcon.close(); 

Answer 1

Wrap BEGIN \ END statements around your bulk inserts. Sqlite is optimized for transactions.

dbcon = new SQLiteConnection(connectionString); dbcon.Open();  SQLiteCommand sqlComm; sqlComm = new SQLiteCommand("begin", dbcon); sqlComm.ExecuteNonQuery();  //---INSIDE LOOP   sqlComm = new SQLiteCommand(sqlQuery, dbcon);   nRowUpdatedCount = sqlComm.ExecuteNonQuery();   //---END LOOP sqlComm = new SQLiteCommand("end", dbcon); sqlComm.ExecuteNonQuery();  dbcon.close(); 

Answer 2

I read everywhere that creating transactions is the solution to slow SQLite writes, but it can be long and painful to rewrite your code and wrap all your SQLite writes in transactions.

I found a much simpler, safe and very efficient method: I enable a (disabled by default) SQLite 3.7.0 optimisation : the Write-Ahead-Log (WAL). The documentation says it works in all unix (i.e. Linux and OSX) and Windows systems.

How ? Just run the following commands after initializing your SQLite connection:

PRAGMA journal_mode = WAL PRAGMA synchronous = NORMAL 

My code now runs ~600% faster : my test suite now runs in 38 seconds instead of 4 minutes :)