SQLite 批次 INSERT 的蝸牛陷阱

假日轉檯寫 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 倍!已筆記。

歡迎推文分享:
Published 16 July 2017 06:42 PM 由 Jeffrey
Filed under: ,
Views: 11,079



意見

# 码农 said on 20 July, 2017 04:17 AM

太棒了, 我在用SQLite时也遇到INSERT非常慢的情况!

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<July 2017>
SunMonTueWedThuFriSat
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345
 
RSS
創用 CC 授權條款
【廣告】
twMVC
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication