[C] SQLiteのINSERT /秒のパフォーマンスを改善しますか?


Answers

それらの挿入にSQLITE_STATIC代わりにSQLITE_TRANSIENTを使用してみてください。

SQLITE_TRANSIENTはSQLiteに文字列データをコピーさせてから返す。

SQLITE_STATICは、クエリが実行されるまで(このループでは常にそうである)、指定したメモリアドレスが有効であることをSQLITE_STATICします。 これにより、ループごとにいくつかの割り当て、コピー、および割り当て解除操作が省かれます。 おそらく大きな改善です。

Question

SQLiteの最適化は難しいです。 Cアプリケーションのバルク挿入パフォーマンスは、毎秒85インサートから毎秒96000インサートまで変化する可能性があります。

背景:デスクトップアプリケーションの一部としてSQLiteを使用しています。 我々は、XMLファイルに格納された大量の構成データを解析し、S​​QLiteデータベースにロードして、アプリケーションの初期化時の処理をさらに進めます。 SQLiteは高速で、特別な設定が不要で、データベースが単一のファイルとしてディスクに格納されるため、このような状況に最適です。

理論的根拠: 当初、私が見ていたパフォーマンスには失望しました。 データベースがどのように設定され、どのようにAPIを使用しているかによって、SQLiteのパフォーマンスは大幅に変化することが判明しています(一括挿入と選択の両方)。 すべてのオプションとテクニックが何であるか把握するのは些細なことではなかったので、このコミュニティwikiエントリを作成して、スタックオーバーフローリーダーと結果を共有して同じ調査の問題を他の人に救うことが賢明だと思った。

実験:単に一般的な意味でのパフォーマンスのヒント( 「トランザクションを使用する」など )ではなく、Cコードを記述し、さまざまなオプションの影響を実際に測定することをお勧めします。 簡単なデータから始めます:

  • トロント市全体の交通計画のうち、28 MBのTAB区切りのテキストファイル(約865,000レコード)
  • 私のテストマシンは、Windows XPを実行している3.60 GHz P4です。
  • コードは、 Visual C ++ 2005で "完全最適化"(/ Ox)および優先ファーストコード(/ Ot)を使用して "リリース"としてコンパイルされます。
  • 私はSQLite "Amalgamation"を使って、テストアプリケーションに直接コンパイルしています。 SQLiteのバージョンは少し古いです(3.6.7)が、これらの結果は最新のリリースに匹敵するものと思われます。

コードを書いてみましょう!

コード:行ごとにテキストファイルを読み込み、文字列を値に分割してSQLiteデータベースに挿入する単純なCプログラムです。 コードのこの「ベースライン」バージョンでは、データベースが作成されますが、実際にはデータは挿入されません。

/*************************************************************
    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;
}

制御"

コードをそのまま実行すると、実際にはデータベース操作は実行されませんが、生のCファイルI / Oと文字列処理操作がどのくらい速いかがわかります。

0.94秒で864913レコードをインポート

すばらしいです! 実際に挿入していなければ、毎秒920,000の挿入を行うことができます:-)

「最悪のケース - シナリオ」

ファイルから読み取った値を使用してSQL文字列を生成し、sqlite3_execを使用してそのSQL操作を呼び出します。

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レコードをインポートしました

Yikes! 2時間45分! これは毎秒わずか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);

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

それは良いです。 単一のトランザクションですべてのインサートを単純にラッピングすると、パフォーマンスは1秒あたり23,000インサートに向上しました

Prepared Statementの使用

トランザクションを使用することは大幅に改善されましたが、すべての挿入に対してSQLステートメントを再コンパイルすることは、同じSQLオーバー・アンド・オーバーを使用する場合は意味がありません。 sqlite3_prepare_v2を使用してSQL文を一度コンパイルし、 sqlite3_prepare_v2を使用してパラメータをその文にバインドします。

/* 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;

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

ニース! もう少し多くのコードがあります( sqlite3_clear_bindingssqlite3_resetを呼び出すことを忘れないでください)が、私たちのパフォーマンスは2倍以上になり、 毎秒53,000のインサートになりました

PRAGMA同期= OFF

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

/* 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);

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

改良点は小さくなりましたが、 1秒あたり最大69,600インサートです

PRAGMA journal_mode = MEMORY

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レコードをインポートしました

これまでの最適化よりもわずかに遅く、 毎秒64,000インサートです。

PRAGMA同期= 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秒で864913レコードをインポートしました

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

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

キックオフのために、これまでのすべての最適化をベースにして、データベースファイル名を再定義して、RAMで全面的に作業しましょう。

#define DATABASE ":memory:"

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

私たちのデータベースをRAMに保存するのは非常に実用的ではありませんが、 毎秒79,000のインサートを実行できることは印象的です

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

特にSQLiteの改善ではありませんが、私はwhileループでの余分なchar*代入操作が嫌いです。 strtok()出力をsqlite3_bind_text()直接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);

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

894913レコードを8.94秒でインポートしました

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

要約(これまでのところ)

あなたはまだ私と一緒にいることを願っています! このロードを開始したのは、SQLiteでは一括挿入のパフォーマンスが非常にばらつきがあり、操作を高速化するために何を変更する必要があるのか​​が必ずしも明らかではないということです。 同じコンパイラ(およびコンパイラオプション)を使用して、SQLiteの同じバージョンと同じデータを使用して、コードとSQ​​Liteの使用方法を最適化して、 毎秒85インサートの最悪のシナリオから、毎秒96,000インサートに至りました。

CREATE INDEXを実行してからINSERTを実行し、次に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);
...

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

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

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

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

予想通り、1つの列が索引付けされると一括挿入は遅くなりますが、データが挿入された後に索引が作成されると差が生じます。 ノー・インデックス・ベースラインは毎秒96,000インサートです。 最初にインデックスを作成してからデータを挿入すると、毎秒47,700個の挿入が行われます。データを挿入すると、インデックスが作成され、1秒間に63,300個の挿入が行われます。

私は喜んで試してみるために他のシナリオの提案をしています...そしてすぐにSELECTクエリのために同様のデータをコンパイルします。




あなたが読んでいるだけの場合は、多少速く(古くなったデータを読むかもしれませんが)、バージョンは複数のスレッドからの複数の接続(スレッドごとの接続)から読み込むことです。

まず、表の項目を見つけます。

 SELECT COUNT(*) FROM table

ページを読み込み(LIMIT / OFFSET)

  SELECT * FROM table ORDER BY _ROWID_ LIMIT <limit> OFFSET <offset>

ここで、はスレッドごとに次のように計算されます。

int limit = (count + n_threads - 1)/n_threads;

スレッドごとに:

int offset = thread_index * limit

私たちの小さな(200MB)データベースでは、これは50-75%のスピードアップ(Windows 7では64ビットの3.8.0.2)でした。 私たちのテーブルは大きく正規化されていません(1000-1500カラム、およそ100,000以上の行)。

スレッドが多すぎたり少なすぎたりしない場合は、ベンチマークしてプロファイルする必要があります。

私たちのためにも、SHAREDCACHEはパフォーマンスを遅くしていましたので、私は手動でPRIVATECACHEを入れました(なぜなら、







このチュートリアルを読んだ後、私は自分のプログラムにそれを実装しようとしました。

私はアドレスを含む4-5のファイルを持っています。 各ファイルには約3,000万レコードがあります。 私はあなたが示唆しているのと同じ設定を使用していますが、私のINSERTの数は、1秒あたり〜10.000件です。

あなたの提案が失敗する場所がここにあります。 すべてのレコードに対して単一のトランザクションを使用し、エラーのない単一の挿入/失敗します。 各レコードを別のテーブルの複数の挿入に分割しているとします。 レコードが壊れているとどうなりますか?

ON CONFLICTコマンドは適用されません。レコードに10個の要素があり、各要素を別の表に挿入する必要がある場合、要素5にCONSTRAINTエラーが発生した場合は、前の4つの挿入もすべて移動する必要があります。

ロールバックがどこに来るかここにあります。 ロールバックの唯一の問題は、すべてのインサートを失い、上から開始することです。 これをどうやって解決できますか?

私のソリューションは、 複数のトランザクションを使用することでした。 私は10.000レコードごとにトランザクションを開始して終了します(その番号がなぜ私がテストした最速のものなのか聞かないでください)。 私は10.000の大きさの配列を作成し、そこに成功したレコードを挿入します。 エラーが発生すると、私はロールバックを行い、トランザクションを開始し、配列からレコードを挿入し、コミットして、壊れたレコードの後に​​新しいトランザクションを開始します。

このソリューションは、悪い/重複したレコードを含むファイルを扱う際に私が抱える問題を回避するのに役立ちました(私はほぼ4%の不良レコードを持っていました)。

私が作成したアルゴリズムは、プロセスを2時間短縮するのに役立ちました。 ファイル1時間30分の最終的な読み込み処理はまだ遅いが、最初に取った4時間と比較されていない。 私はインサートを10.000 / sから〜14.000 / sに加速させた

誰かがそれをスピードアップする方法に関する他のアイデアを持っているなら、私は提案に開放されています。

更新

上記の答えに加えて、使用しているハードドライブに応じて、1秒あたりの挿入数を覚えておく必要があります。 私は異なるハードドライブを持つ3つの異なるPCでそれをテストし、時間の大きな違いを得ました。 PC1(1時間30分)、PC2(6時間)PC3(14時間)です。

2週間の調査と複数のリソースのチェックが終わると、ハードドライブ、RAM、キャッシュ、ハードドライブの一部の設定がI / Oレートに影響を与えることがわかりました。 目的の出力ドライブのプロパティをクリックすると、一般タブに2つのオプションが表示されます。 Opt1:このドライブを圧縮する、Opt2:このドライブのファイルにコンテンツのインデックスを付けることを許可します。

これらの2つのオプションを無効にすると、3台のPCすべてがほぼ同じ時間(1時間20分〜40分)かかる。 挿入が遅い場合は、ハードドライブがこれらのオプションで設定されているかどうかを確認してください。 それは解決策を見つけることを試みる多くの時間と頭痛を節約します




バルクインサート

この記事との質問からインスピレーションを受けてくれました。 - SQLiteデータベースに一度に複数の行を挿入することは可能ですか? - 私は最初のGitリポジトリを投稿しました:

https://github.com/rdpoor/CreateOrUpdate

ActiveRecordの配列をMySQL 、SQLite、またはPostgreSQLデータベースに一括してロードします。 既存のレコードを無視したり、上書きしたり、エラーを発生させたりするオプションが含まれています。 私の初歩的なベンチマークは、シーケンシャル書き込み(YMMV)に比べて10倍の速度向上を示しています。

私は大規模なデータセットを頻繁にインポートする必要があるプロダクションコードでこれを使用しています。