SQLite 批次 INSERT 的蝸牛陷阱

2017-07-16 06:42 PM

假日轉檯寫 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