SQLite Very Slow ExecuteNonQuery

Question

Well, I use SQLite, but when I run unsolicited queries, it’s probably 10k + [.sql file] files. I find it to be very slow, which may take up to 10 minutes + to complete adding information to the database.

Anyway, this is my ExecuteNonQuery code.

public int ExecuteNonQuery(string sql) { var cnn = new SQLiteConnection(_dbConnection); cnn.Open(); var mycommand = new SQLiteCommand(cnn) {CommandText = sql}; int rowsUpdated = mycommand.ExecuteNonQuery(); cnn.Close(); return rowsUpdated; } 

I hope there is a way to make it take a few seconds.

Answer 1

The thing with SQLite is that you have wrap insert-update-delete commands in a transaction, otherwise it will be painfully slow. You can do this using the transaction support built into the .NET data provider, or since you are reading the .sql file, you can do the first line of the begin transaction and the last line of the commit transaction . In any case, this should work.

If you want to do this in a .sql file, it might look like this.

 begin transaction; insert into ...; update ...; delete ...; commit transaction; 

Or, if you do this in code, it will look like this.

 public int ExecuteNonQuery(string sql) { var cnn = new SQLiteConnection(_dbConnection); cnn.Open(); var transaction = cnn.BeginTransaction(); var mycommand = new SQLiteCommand(cnn) {CommandText = sql}; mycommand.Transaction = transaction; int rowsUpdated = mycommand.ExecuteNonQuery(); transaction.Commit(); cnn.Close(); return rowsUpdated; }