問題描述
我有大量的行(從 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
這篇關于VB.Net向SQLite-DB寫入大量數據的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！