C# SQLite 進階教學 快速大量新增

在上一篇 C# SQLite 基礎教學 新增、修改、刪除、查詢 (CRUD) 中有示範基本的資料庫新增資料，但是在大量新增時如果只是直接加個迴圈重複執行程式碼，程式就會花費大量時間在資料庫上新增資料，讓使用者等待很久。
要縮短處理時間感覺要解決起來很複雜，但其實了解之後要解決非常的簡單，下面比較三種常見處理方式的執行所需時間，趕時間的可以直接看最後面的程式碼

測試情境：
SQLite 新增 10,000 筆使用者帳號和密碼

測試資料表：

  
CREATE TABLE users
(
    uid           INTEGER,
    user_name     TEXT NOT NULL,
    user_password TEXT,
    PRIMARY KEY (uid AUTOINCREMENT)
);

測試新增資料語法：

  
INSERT INTO users (user_name, user_password) values ('user1', 'password1');

測試開始：

測試一：直接重複新增

首先先是最簡單的單純加上迴圈執行 10,000次的語法：

  
for (int i = 0; i < 10000; i++)
{
    using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
    {
        connection.Open();
        var command = connection.CreateCommand();
        command.CommandText = @"
            INSERT INTO users (user_name, user_password)
            values ($user_name, $user_password);";

        command.Parameters.AddWithValue("$user_name", "user" + i);
        command.Parameters.AddWithValue("$user_password", "password" + i);
        command.ExecuteNonQuery();
    }
}

耗時 95,884 毫秒

測試二：避免重複連接

最簡單可以改進的地方就是第五行 connection. Open () ，因為重複開啟和關閉連接會造成大量的時間被浪費，所以這次試試看開啟連接後跑完 10,000 次再關閉連接：

  
using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
{
    connection.Open();
    var command = connection.CreateCommand();
    command.CommandText = @"
            INSERT INTO users (user_name, user_password)
            values ($user_name, $user_password);";

    for (int i = 0; i < 10000; i++)
    {
        command.Parameters.AddWithValue("$user_name", "user" + i);
        command.Parameters.AddWithValue("$user_password", "password" + i);
        command.ExecuteNonQuery();
    }
}

耗時 25,591 毫秒！直接差了快四倍！
但是還是太久了，快還要更快，有沒有再更快的方式？

測試三：一次執行完語法

雖然例如 SqlBulkCopy 等都不支援 SQLite，但是後來還是有找到可以快速新增的方式，例如使用 SQLiteTransaction。

簡單來說就是先把所有新增語法組合好再一次執行，程式碼如下：

  using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
{
    connection.Open();

    using (var transaction = connection.BeginTransaction())
    {
        var command = connection.CreateCommand();
        command.CommandText = @"
            INSERT INTO users (user_name, user_password)
            values ($user_name, $user_password);";

        for (int i = 0; i < 10000; i++)
        {
            command.Parameters.AddWithValue("$user_name", "user" + i);
            command.Parameters.AddWithValue("$user_password", "password" + i);

            command.ExecuteNonQuery();
        }

        transaction.Commit();
    }
}

猜猜多久？居然只要 33毫秒！
不是 33秒，是33毫秒！
和第一個 95,884 毫秒差距了 2,905倍！

看來SQL的慢有一半都是因為我太菜
解決完新增下一步就是想辦法提升查詢效能了，還在研究中...

不知道還有沒有更好的方式呢？歡迎留言討論～