SQLite Paste Very Slow?

Question

I recently read about SQLite and thought I'd give it a try. When I insert one record, it works fine. But when I insert a hundred, it takes five seconds, and as the number of records increases, time also happens. What could be wrong? I am using 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

Try to wrap all your inserts (aka, volume insert) in one 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 all inserts in a transaction , which slows down the process:

INSERT is really slow - I can only do a few tens of INSERT per second

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

Transaction speed is limited by disk speed because (by default) SQLite actually waits until the data is actually stored on the surface of the disk until the transaction completes. Thus, if you suddenly lose power or if your OS crashes, your data will still be safe. For more information, 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 inserts are grouped into a single transaction. The time required to commit the transaction is amortized over all attached insert statements, and therefore the time for the insert statement is significantly reduced.

Answer 3

I read throughout that creating transactions is a solution for slow SQLite writing, but it may take a long and painful rewrite of your code and transferring the entire SQLite transaction into a transaction.

I found a much simpler, safer and very efficient method: I enable (disabled by default) SQLite 3.7.0 optimization: Write-Ahead-Log (WAL) . The documentation says that it works on all Unix (like Linux and OSX) and Windows systems.

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

 PRAGMA journal_mode = WAL PRAGMA synchronous = NORMAL 

My code now runs about 600% faster: my test suite now works after 38 seconds instead of 4 minutes :)

Answer 4

See "Optimizing SQL Queries" in the ADO.NET SQLite.NET.chm Help File. Code from this page:

 using (SQLiteTransaction mytransaction = myconnection.BeginTransaction()) { using (SQLiteCommand mycommand = new SQLiteCommand(myconnection)) { SQLiteParameter myparam = new SQLiteParameter(); int n; mycommand.CommandText = "INSERT INTO [MyTable] ([MyId]) VALUES(?)"; mycommand.Parameters.Add(myparam); for (n = 0; n < 100000; n ++) { myparam.Value = n + 1; mycommand.ExecuteNonQuery(); } } mytransaction.Commit(); }