Slow batch / bulk insert in SQLite

Question

I am trying to import data from a csv file into a sqlite table. My test data is only about 8 MB (50,000 lines) and takes about 15 seconds. However, production data is almost 400 MB, and forever (at least 30 minutes +, I gave up).

After much research, I found it necessary to insert in one transaction (which led me to a 15 second import, great advice! :)) So this is not a problem. (AFAIK)

I also use "ExecuteNonQuery () for the parameterized INSERT statement" according to Robert Simpson's post - and numerous variations.

I just used TextReader.ReadLine() and String.Split('\t') , then I read about ReadLine() somewhere, being slow due to the amount of read on disk, so I looked into the bufferedStream reading and came across this csv reader . But still no noticeable changes in performance.

So, I commented on the guts of my insert cycle, and reading happens right away, so I'm sure the problem is with my insert. I tried many options for creating parameterized queries + single transaction, but all with close identical results.

Here is the regular version of my code. Thanks in advance, it drives me crazy! Am I going to try importing into a dataset and pasting this? ....

 using (TextReader tr = File.OpenText(cFile)) { using (SQLiteConnection cnn = new SQLiteConnection(connectionString)) { string line; string insertCommand = "INSERT INTO ImportTable VALUES (@P0,@P1,@P2,@P3,@P4)"; cnn.Open(); SQLiteCommand cmd = new SQLiteCommand("begin", cnn); cmd.ExecuteNonQuery(); cmd.CommandText = insertCommand; while ((line = tr.ReadLine()) != null) { string[] items = line.Split('\t'); cmd.Parameters.AddWithValue("@P0", items[0]); cmd.Parameters.AddWithValue("@P1", items[1]); cmd.Parameters.AddWithValue("@P2", items[2]); cmd.Parameters.AddWithValue("@P3", items[3]); cmd.Parameters.AddWithValue("@P4", items[4]); cmd.ExecuteNonQuery(); } cmd.CommandText = "end"; cmd.ExecuteNonQuery(); } } 

Update: I just tried using an insert with parameters (only some values ​​are hardcoded), less than 5 seconds ... still not as fast as the articles I saw ...

In addition, I am running Core2 Duo (3Ghz) with 2G Ram, XP.

Answer 1

So, I think I solved the problem - or at least found a solution.

Since I have exhausted all my code parameters (and it does not seem like someone had an answer / problem with my code), I decided that the problem could be in the database itself ...

I created my database and tables in the SQLite Manager Firefox plugin.

So, I recreated everything from the command line, and BOOM! My import fell just a few seconds!

I knew that the problem is that it cannot handle 64-bit integers (but just uses TEXT data types). Perhaps there is a problem with SQLite Manager using a different SQLite engine for the .Net version? I dont know.

My next step could be to create db + tables from my application, and not to prepare them beforehand ... But I am satisfied with the performance now, so this is not a priority.