VB.Net write large amounts of data to SQLite-DB

Question

I have a large amount of rows(from 10.000 to 200.000 so far, might become more) that I want to save in an SQLite Database as the data is only going to be used locally. The rows consist of single strings that have delimiters in them. Depending on where I pull the data from, the rows fill between 2 and 10 fields. So I have a varying number of strings per row and need a method that can take that varying amount. A table will always be specifically created for the data pulled. I currently have a method that is actually working but is insaaaanely slow.

Basically I construct a SQL command string based on the amount of variables I have to pass. I do this by wrapping a simple insert command around the variable (in which I define the fields) and then passing every single line. Worked fine for 30 rows, doesn't really work out for 20k rows.

Could someone set me on track on where to continue?

Sample Code:

For Each row As string In t
        line = Replace(row, "|", "','")
        cmd.CommandText = cmd_string + line + "')"
        cmd.ExecuteNonQuery()
    Next

Sample constructed Command:

"INSERT INTO MYTABLE(COLUMN1, COLUMN2) VALUES ('IT ','DE')"

I assume this method is utter crap :D Any advice is much appreciated.

/Regards

Answer 1

Executing thousands of inserts one after another is indeed insanely slow. It will help you tremendously to wrap all the inserts into a transaction.

Using t As SQLiteTransaction = sqlcon.BeginTransaction 'sqlcon being the SQLiteConnection
    For Each row As string In t
        line = Replace(row, "|", "','")
        cmd.CommandText = cmd_string + line + "')"
        cmd.ExecuteNonQuery()
    Next                      
    t.Commit()
End Using      

You basically collect all the inserts you want to do and when you are done they are all executed in one large swoosh. This speeds things up a lot.

Here is a tutorial on transactions:

http://www.tutorialspoint.com/sqlite/sqlite_transactions.htm