SQLite Very Slow ExecuteNonQuery [duplicate]

Question

Well i am using SQLite but when i start to execute non queries probably 10k+ commands [.sql file]. I find that it is very slow that it could take up to 10min+ to end adding the info 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 just make it take seconds to finish.

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 either do this with the transaction support built into the .NET Data Provider or since you are reading a .sql file you could make the first line begin transaction and the last line commit transaction. Either way should work.

If you want to do it in the .sql file then it might look like this.

begin transaction;

insert into ...;
update ...;
delete ...;

commit transaction;

Or if you do it in code it would 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;
}