SQLite 批次 INSERT 的蝸牛陷阱
1 |
假日轉檯寫 Coding4Fun 專案,本週的 Scrum Sprint Planning Meeting 我認領的工作是將 13 萬英文單字轉入 SQLite 資料庫(謎:認領?快醒醒,這專案從頭到尾只有你一個人吧?)。
心想這有什麼難,涮涮涮寫好以下程式,沒想到其執行速度之慢,嚇得我屁滾尿流失了魂…
using (var cnSqlite = new SQLiteConnection(csSqlite))
{
cnSqlite.Open();
Stopwatch sw = new Stopwatch();
sw.Start();
var totalCount = list.Count;
var index = 0;
foreach (var voc in list)
{
Console.WriteLine(
$"{index++}/{totalCount}({index * 100.0 / totalCount:n1}%) {voc.Word}");
cnSqlite.Execute(
"INSERT INTO Dictionary VALUES(@Word, @KKSymbol, @Explanation)", (object)voc);
}
sw.Stop();
Console.Write($"Duration={sw.ElapsedMilliseconds:n0}ms");
}
事實上我沒耐心等到 sw.Stop() 觀察總耗時,花了 30 分鐘只 INSERT 完 10% 我就放棄了。換句話說,全部跑完要 5 個小時啊啊啊啊啊~ 這個年代這種速度?我有正在操作古董火砲對抗航母戰鬥群的無力感…
爬文找到文章(Make your SQLite bulk inserts very fast in C# .NET),才知這是 SQLite 的 FAQ。
SQLite 一秒最快能完成 50,000 筆以上的 INSERT,但一秒只能完成幾十筆 Transaction,依原本寫法,由於每筆 INSERT 動作預設自成一個 Transaction,速度要快也難。
解決方法很簡單,只需加個兩行,將整個迴圈包成一個 Transaction 就搞定。猜看看速度改進多少?
using (var cnSqlite = new SQLiteConnection(csSqlite))
{
cnSqlite.Open();
Stopwatch sw = new Stopwatch();
sw.Start();
using (SQLiteTransaction tran = cnSqlite.BeginTransaction())
{
var totalCount = list.Count;
var index = 0;
foreach (var voc in list)
{
Console.WriteLine(
$"{index++}/{totalCount}({index * 100.0 / totalCount:n1}%) {voc.Word}");
cnSqlite.Execute(
"INSERT INTO Dictionary VALUES(@Word, @KKSymbol, @Explanation)", (object)voc);
}
tran.Commit();
}
sw.Stop();
Console.Write($"Duration={sw.ElapsedMilliseconds:n0}ms");
}
實測結果,132,319 筆 14.887 秒塞完,平均 8,888 筆/秒(這數字巧合也太神奇惹)!跟原本 7 筆/秒相比,速度提升 1200 倍!已筆記。
Comments
# by 码农
太棒了, 我在用SQLite时也遇到INSERT非常慢的情况!