SQLite Very Slow ExecuteNonQuery

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.

I doubt that because it is very simple INSERT INTO CMDS from a .sql script — Roman Ratskey, Feb 26 '13 at 21:23

Brian Gideon · Accepted Answer · 2013-02-26T21:31:35.150

5

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;
}

edited Feb 26 '13 at 21:31

answered Feb 26 '13 at 21:25

Brian Gideon

47,849
13
107
150

You have my code above could you edit your answer with what you are pointing to. Because when i added the begin transaction and comit lines to the .sql file i got SQL Logic error – Roman Ratskey Feb 26 '13 at 21:27
i added those lines to the beginning and the ending lines of the .sql file but i find that it does not do anything, otherwise the query is frozen now. – Roman Ratskey Feb 26 '13 at 21:31
@RuneS: Hmm...that's odd because that is exactly how I do it and it works fine. What was the error you got back from sqlite the first time? – Brian Gideon Feb 26 '13 at 21:34
When i try both ways with the begin, end transaction when i click the process button and watch the database file it only makes 2kb and then it is frozen i don't know why. [Although without begin and end transaction, it is adding to the file and it is getting bigger constantly. – Roman Ratskey Feb 26 '13 at 21:36
@RuneS: Try running the .sql file through using the sqlite3.exe command line. If that doesn't work then you know the problem is likely with your script as opposed to C# code. – Brian Gideon Feb 26 '13 at 21:38
Note : i am trying to add from a .sql file that is full of INSERT INTO cmds, also i am reading it using `System.IO.File.ReadAllText(@"file.sql");` and then execute the non query using the text returned from the `File.ReadAllText` – Roman Ratskey Feb 26 '13 at 21:38
Well it actually works now but still too slow... – Roman Ratskey Feb 26 '13 at 21:51
@RuneS: 10K commands should go through pretty quick. What kind of speeds are you seeing now? – Brian Gideon Feb 26 '13 at 21:53
How can i monitor the speed in my code ? i took more than 6 minutes to execute those 10k commands – Roman Ratskey Feb 26 '13 at 21:55
@RuneS: Yeah, 6 minutes sounds excessive. Trying running that .sql file through the command line (sqlite3.exe) and see what you get. – Brian Gideon Feb 26 '13 at 21:57
can you tell me the command to run it in sqlite3.exe i really don't have a background with that – Roman Ratskey Feb 26 '13 at 21:58
Well my problem is not solved yet, i still facing a very slow data adding from the sql file although i have a begin and commit transaction – Roman Ratskey Feb 26 '13 at 22:19
I had the same problem inserting a few thousand rows in a PHP script. It took several minutes. After I wrapped the INSERTs inside a transaction it took no time. Modern SQLite allows multiple value INSERTs, which would be considered a single transaction, but that wasn’t available to me. Thanks. – Manngo Mar 11 '20 at 20:57

SQLite Very Slow ExecuteNonQuery

1 Answers1