SQLite Insert very slow?

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 LOOPsqlComm = new SQLiteCommand("end", dbcon);sqlComm.ExecuteNonQuery(); dbcon.close();

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 = WALPRAGMA synchronous = NORMAL

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

Try wrapping all of your inserts (aka, a bulk insert) into a single transaction:

string insertString = "INSERT INTO [TableName] ([ColumnName]) Values (@value)";SQLiteCommand command = new SQLiteCommand();command.Parameters.AddWithValue("@value", value);command.CommandText = insertString;command.Connection = dbConnection;SQLiteTransaction transaction = dbConnection.BeginTransaction();try{    //---INSIDE LOOP    SQLiteCommand sqlComm = new SQLiteCommand(sqlQuery, dbcon);    nRowUpdatedCount = sqlComm.ExecuteNonQuery();     //---END LOOP        transaction.Commit();    return true;}catch (SQLiteException ex){    transaction.Rollback();}

By default, SQLite wraps every inserts in a transaction, which slows down the process:

INSERT is really slow - I can only do few dozen INSERTs per second

Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second.

Transaction speed is limited by disk drive speed because (by default) SQLite actually waits until the data really is safely stored on the disk surface before the transaction is complete. That way, if you suddenly lose power or if your OS crashes, your data is still safe. For details, read about atomic commit in SQLite..

By default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN...COMMIT then all the inserts are grouped into a single transaction. The time needed to commit the transaction is amortized over all the enclosed insert statements and so the time per insert statement is greatly reduced.