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

Can you edit your question to include a few input lines as example that shows the variations to cover? Also, what do you mean by "A table will always be specifically created for the data pulled."? — diegohb
– diegohb, Commented Apr 16, 2015 at 20:08

Jens · Accepted Answer · 2015-04-16 20:11:13Z

2

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

answered Apr 16, 2015 at 20:11

Jens

6,4003 gold badges29 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related