Improve INSERT-per-second performance of SQLite Ask Question

Optimizing SQLite is tricky. Bulk-insert performance of a C application can vary from 85 inserts per second to over 96,000 inserts per second!

Background: We are using SQLite as part of a desktop application. We have large amounts of configuration data stored in XML files that are parsed and loaded into an SQLite database for further processing when the application is initialized. SQLite is ideal for this situation because it's fast, it requires no specialized configuration, and the database is stored on disk as a single file.

Rationale: Initially I was disappointed with the performance I was seeing. It turns-out that the performance of SQLite can vary significantly (both for bulk-inserts and selects) depending on how the database is configured and how you're using the API. It was not a trivial matter to figure out what all of the options and techniques were, so I thought it prudent to create this community wiki entry to share the results with Stack Overflow readers in order to save others the trouble of the same investigations.

The Experiment: Rather than simply talking about performance tips in the general sense (i.e. "Use a transaction!"), I thought it best to write some C code and actually measure the impact of various options. We're going to start with some simple data:

• A 28 MB TAB-delimited text file (approximately 865,000 records) of the complete transit schedule for the city of Toronto
• My test machine is a 3.60 GHz P4 running Windows XP.
• The code is compiled with Visual C++ 2005 as "Release" with "Full Optimization" (/Ox) and Favor Fast Code (/Ot).
• I'm using the SQLite "Amalgamation", compiled directly into my test application. The SQLite version I happen to have is a bit older (3.6.7), but I suspect these results will be comparable to the latest release (please leave a comment if you think otherwise).

Let's write some code!

The Code: A simple C program that reads the text file line-by-line, splits the string into values and then inserts the data into an SQLite database. In this "baseline" version of the code, the database is created, but we won't actually insert data:

/*************************************************************
    Baseline code to experiment with SQLite performance.

    Input data is a 28 MB TAB-delimited text file of the
    complete Toronto Transit System schedule/route info
    from http://www.toronto.ca/open/datasets/ttc-routes/

**************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include <string.h>
#include "sqlite3.h"

#define INPUTDATA "C:\\TTC_schedule_scheduleitem_10-27-2009.txt"
#define DATABASE "c:\\TTC_schedule_scheduleitem_10-27-2009.sqlite"
#define TABLE "CREATE TABLE IF NOT EXISTS TTC (id INTEGER PRIMARY KEY, Route_ID TEXT, Branch_Code TEXT, Version INTEGER, Stop INTEGER, Vehicle_Index INTEGER, Day Integer, Time TEXT)"
#define BUFFER_SIZE 256

int main(int argc, char **argv) {

    sqlite3 * db;
    sqlite3_stmt * stmt;
    char * sErrMsg = 0;
    char * tail = 0;
    int nRetCode;
    int n = 0;

    clock_t cStartClock;

    FILE * pFile;
    char sInputBuf [BUFFER_SIZE] = "\0";

    char * sRT = 0;  /* Route */
    char * sBR = 0;  /* Branch */
    char * sVR = 0;  /* Version */
    char * sST = 0;  /* Stop Number */
    char * sVI = 0;  /* Vehicle */
    char * sDT = 0;  /* Date */
    char * sTM = 0;  /* Time */

    char sSQL [BUFFER_SIZE] = "\0";

    /*********************************************/
    /* Open the Database and create the Schema */
    sqlite3_open(DATABASE, &db);
    sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);

    /*********************************************/
    /* Open input file and import into Database*/
    cStartClock = clock();

    pFile = fopen (INPUTDATA,"r");
    while (!feof(pFile)) {

        fgets (sInputBuf, BUFFER_SIZE, pFile);

        sRT = strtok (sInputBuf, "\t");     /* Get Route */
        sBR = strtok (NULL, "\t");            /* Get Branch */
        sVR = strtok (NULL, "\t");            /* Get Version */
        sST = strtok (NULL, "\t");            /* Get Stop Number */
        sVI = strtok (NULL, "\t");            /* Get Vehicle */
        sDT = strtok (NULL, "\t");            /* Get Date */
        sTM = strtok (NULL, "\t");            /* Get Time */

        /* ACTUAL INSERT WILL GO HERE */

        n++;
    }
    fclose (pFile);

    printf("Imported %d records in %4.2f seconds\n", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);

    sqlite3_close(db);
    return 0;
}

---

The "Control"

Running the code as-is doesn't actually perform any database operations, but it will give us an idea of how fast the raw C file I/O and string processing operations are.

Imported 864913 records in 0.94 seconds

Great! We can do 920,000 inserts per second, provided we don't actually do any inserts :-)

---

The "Worst-Case-Scenario"

We're going to generate the SQL string using the values read from the file and invoke that SQL operation using sqlite3_exec:

sprintf(sSQL, "INSERT INTO TTC VALUES (NULL, '%s', '%s', '%s', '%s', '%s', '%s', '%s')", sRT, sBR, sVR, sST, sVI, sDT, sTM);
sqlite3_exec(db, sSQL, NULL, NULL, &sErrMsg);

これは、挿入ごとに SQL が VDBE コードにコンパイルされ、挿入ごとに独自のトランザクションで実行されるため、遅くなります。どのくらい遅いのでしょうか?

9933.61 秒で 864913 件のレコードをインポートしました

やれやれ！2時間45分！1秒あたりわずか85回の挿入です。

トランザクションの使用

デフォルトでは、SQLite はすべての INSERT / UPDATE ステートメントを一意のトランザクション内で評価します。大量の挿入を実行する場合は、操作をトランザクションでラップすることをお勧めします。

sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);

pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {

    ...

}
fclose (pFile);

sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);

864913 件のレコードを 38.03 秒でインポートしました

それは良いことです。すべての挿入を 1 つのトランザクションにラップするだけで、パフォーマンスが1 秒あたり 23,000 挿入に向上しました。

準備されたステートメントの使用

トランザクションの使用は大きな改善でしたが、同じ SQL を何度も使用する場合、挿入ごとに SQL ステートメントを再コンパイルするのは意味がありません。 を使用して SQL ステートメントをsqlite3_prepare_v2一度コンパイルし、 を使ってそのステートメントにパラメータをバインドしてみましょうsqlite3_bind_text。

/* Open input file and import into the database */
cStartClock = clock();

sprintf(sSQL, "INSERT INTO TTC VALUES (NULL, @RT, @BR, @VR, @ST, @VI, @DT, @TM)");
sqlite3_prepare_v2(db,  sSQL, BUFFER_SIZE, &stmt, &tail);

sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);

pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {

    fgets (sInputBuf, BUFFER_SIZE, pFile);

    sRT = strtok (sInputBuf, "\t");   /* Get Route */
    sBR = strtok (NULL, "\t");        /* Get Branch */
    sVR = strtok (NULL, "\t");        /* Get Version */
    sST = strtok (NULL, "\t");        /* Get Stop Number */
    sVI = strtok (NULL, "\t");        /* Get Vehicle */
    sDT = strtok (NULL, "\t");        /* Get Date */
    sTM = strtok (NULL, "\t");        /* Get Time */

    sqlite3_bind_text(stmt, 1, sRT, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, sBR, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 3, sVR, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 4, sST, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 5, sVI, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 6, sDT, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 7, sTM, -1, SQLITE_TRANSIENT);

    sqlite3_step(stmt);

    sqlite3_clear_bindings(stmt);
    sqlite3_reset(stmt);

    n++;
}
fclose (pFile);

sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);

printf("Imported %d records in %4.2f seconds\n", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);

sqlite3_finalize(stmt);
sqlite3_close(db);

return 0;

864913 件のレコードを 16.27 秒でインポートしました

素晴らしい! コードは少し増えましたが (sqlite3_clear_bindingsと を呼び出すことを忘れないでくださいsqlite3_reset)、パフォーマンスは 2 倍以上になり、1 秒あたり 53,000 挿入になりました。

プラグマ同期 = OFF

デフォルトでは、SQLite は OS レベルの書き込みコマンドを発行した後、一時停止します。これにより、データがディスクに書き込まれることが保証されます。 を設定するとsynchronous = OFF、SQLite は書き込みのためにデータを OS に渡して続行するように指示されます。データがプラッターに書き込まれる前にコンピューターが壊滅的なクラッシュ (または停電) に見舞われると、データベース ファイルが破損する可能性があります。

/* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);

864913 件のレコードを 12.41 秒でインポートしました

改善は小さくなりましたが、1 秒あたり最大 69,600 回の挿入が可能になりました。

プラグマ journal_mode = メモリ

を評価して、ロールバック ジャーナルをメモリに保存することを検討してくださいPRAGMA journal_mode = MEMORY。トランザクションは高速になりますが、トランザクション中に電源が落ちたりプログラムがクラッシュしたりすると、データベースはトランザクションが部分的に完了した状態で破損した状態のままになる可能性があります。

/* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);

13.50秒で864913件のレコードをインポートしました

1 秒あたり 64,000 挿入で、以前の最適化よりも少し遅くなります。

PRAGMA synchronize = OFFおよびPRAGMA journal_mode = MEMORY

前の 2 つの最適化を組み合わせてみましょう。クラッシュした場合のリスクは少し高くなりますが、データをインポートするだけです (銀行を運営しているわけではありません)。

/* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);

12.00 秒で 864913 件のレコードをインポートしました

素晴らしい! 1 秒あたり 72,000 回の挿入が可能です。

インメモリデータベースの使用

興味本位で、これまでの最適化をすべて踏まえて、データベース ファイル名を再定義し、完全に RAM 内で作業するようにしてみましょう。

#define DATABASE ":memory:"

864913 件のレコードを 10.94 秒でインポートしました

データベースを RAM に保存するのはそれほど実用的ではありませんが、1 秒あたり 79,000 回の挿入を実行できるのは印象的です。

C コードのリファクタリング

char*特に SQLite の改善ではありませんが、ループ内の余分な代入演算は気に入りません。 の出力を に直接while渡すようにコードを簡単にリファクタリングし、コンパイラに処理を高速化させてみましょう。strtok()sqlite3_bind_text()

pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {

    fgets (sInputBuf, BUFFER_SIZE, pFile);

    sqlite3_bind_text(stmt, 1, strtok (sInputBuf, "\t"), -1, SQLITE_TRANSIENT); /* Get Route */
    sqlite3_bind_text(stmt, 2, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Branch */
    sqlite3_bind_text(stmt, 3, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Version */
    sqlite3_bind_text(stmt, 4, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Stop Number */
    sqlite3_bind_text(stmt, 5, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Vehicle */
    sqlite3_bind_text(stmt, 6, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Date */
    sqlite3_bind_text(stmt, 7, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Time */

    sqlite3_step(stmt);        /* Execute the SQL Statement */
    sqlite3_clear_bindings(stmt);    /* Clear bindings */
    sqlite3_reset(stmt);        /* Reset VDBE */

    n++;
}
fclose (pFile);

注: 実際のデータベースファイルの使用に戻ります。インメモリデータベースは高速ですが、必ずしも実用的ではありません。

8.94秒で864913件のレコードをインポートしました

パラメータ バインディングで使用される文字列処理コードを少しリファクタリングすることで、1 秒あたり 96,700 回の挿入を実行できるようになりました。これは十分に高速であると言っても過言ではないでしょう。他の変数 (ページ サイズ、インデックス作成など) を微調整し始めると、これがベンチマークになります。

---

要約（これまでのところ）

皆さんがまだ私の話に付き合ってくださっていることを願っています。私たちがこの方法を採用した理由は、SQLite では一括挿入のパフォーマンスが非常に大きく異なるため、操作を高速化するためにどのような変更を加える必要があるかが必ずしも明らかではないからです。同じコンパイラ (およびコンパイラ オプション)、同じバージョンの SQLite、同じデータを使用して、コードと SQLite の使用を最適化し、最悪のシナリオである 1 秒あたり 85 件の挿入から 1 秒あたり 96,000 件を超える挿入にまで改善しました。

---

CREATE INDEX の後に INSERT を実行する vs. INSERT の後に CREATE INDEX を実行する

パフォーマンスの測定を始める前にSELECT、インデックスを作成する必要があります。以下の回答の 1 つで、一括挿入を行う場合、データを挿入した後にインデックスを作成する方が高速であることが示されています (最初にインデックスを作成してからデータを挿入するよりも高速です)。試してみましょう。

インデックスを作成してデータを挿入する

sqlite3_exec(db, "CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
...

864913 件のレコードを 18.13 秒でインポートしました

データを挿入してインデックスを作成する

...
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);

864913 件のレコードを 13.66 秒でインポートしました

予想どおり、1 つの列にインデックスが付けられている場合、一括挿入は遅くなりますが、データの挿入後にインデックスが作成される場合は違いが生じます。インデックスなしのベースラインは 1 秒あたり 96,000 挿入です。最初にインデックスを作成してからデータを挿入すると、1 秒あたり 47,700 挿入になりますが、最初にデータを挿入してからインデックスを作成すると、1 秒あたり 63,300 挿入になります。

---

他のシナリオを試すための提案を喜んでお受けします。また、SELECT クエリ用の同様のデータをすぐにコンパイルする予定です。