VB.Net向SQLite-DB写入大量数据

2021-09-16 00:00:00 sql vb.net sqlite

我有大量的行(从 10.000 到 200.000 到目前为止，可能会更多)我想保存在 SQLite 数据库中，因为数据只会在本地使用.行由单个字符串组成，其中包含分隔符.根据我从哪里提取数据，行会填充 2 到 10 个字段.所以我每行有不同数量的字符串，并且需要一种可以采用不同数量的方法.将始终专门为提取的数据创建一个表.我目前有一种方法确实有效，但速度非常慢.

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.

基本上，我根据必须传递的变量数量构造一个 SQL 命令字符串.我通过在变量周围包装一个简单的插入命令(我在其中定义字段)然后传递每一行来做到这一点.对于 30 行工作正常，但对于 20k 行却无法正常工作.

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?

示例代码:

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

示例构造命令:

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

我认为这种方法完全是废话:D非常感谢任何建议.

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

/问候

推荐答案

一个接一个地执行数千个插入确实非常慢.将所有插入内容包装到事务中将极大地帮助您.

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.

这里是交易教程:

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

相关文章