SQLite insert performance - SQLiteのINSERTの秒単位のパフォーマンスの向上

SQLite performance tuning / c / performance / sqlite / optimization

SQLiteの最適化は難しい。Cアプリケーションのバルクインサートのパフォーマンスは、毎秒85回のインサートから毎秒96,000回以上のインサートまで様々です。

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

/ ************************************************* ************ SQLiteのパフォーマンスを試すためのベースラインコード。入力データは、http://www.toronto.ca/open/datasets/ttc-routes/**********からの完全なトロント交通システムのスケジュール/ルート情報の28MBTAB区切りのテキストファイルです。 ************************************************** ** /
#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;  / *ルート* /
    char * sBR = 0;  /* ブランチ */
    char * sVR = 0;  /* バージョン */
    char * sST = 0;  / *ストップ番号* /
    char * sVI = 0;  / *車両* /
    char * sDT = 0;  / *日付* /
    char * sTM = 0;  / *時間* /

    char sSQL [BUFFER_SIZE] = "\0";

    /*********************************************/
    / *データベースを開き、スキーマを作成します* /
    sqlite3_open(DATABASE, &db);
    sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);

    /*********************************************/
    / *入力ファイルを開いてデータベースにインポートします* /
    cStartClock = clock();

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

        fgets (sInputBuf, BUFFER_SIZE, pFile);

        sRT = strtok (sInputBuf, "\t");     / *ルートを取得* /
        sBR = strtok (NULL, "\t");            / *ブランチを取得します* /
        sVR = strtok (NULL, "\t");            / *バージョンを取得* /
        sST = strtok (NULL, "\t");            / *ストップ番号を取得* /
        sVI = strtok (NULL, "\t");            / *車両を入手する* /
        sDT = strtok (NULL, "\t");            / *日付を取得* /
        sTM = strtok (NULL, "\t");            /* 時間をもらう */

        / *実際の挿入はここにあります* /

        n++;
    }
    fclose (pFile);

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

    sqlite3_close(db);
    return 0;
}

ファイルから読み取った値を使って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);

デフォルトでは、SQLite はすべての INSERT/UPDATE 文を 1 つのトランザクション内で評価します。大量の挿入を行う場合は、操作をトランザクションにまとめることをお勧めします。

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);
/ *入力ファイルを開いてデータベースにインポートします* /
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");   / *ルートを取得* /
    sBR = strtok (NULL, "\t");        / *ブランチを取得します* /
    sVR = strtok (NULL, "\t");        / *バージョンを取得* /
    sST = strtok (NULL, "\t");        / *ストップ番号を取得* /
    sVI = strtok (NULL, "\t");        / *車両を入手する* /
    sDT = strtok (NULL, "\t");        / *日付を取得* /
    sTM = strtok (NULL, "\t");        /* 時間をもらう */

    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;
/ *データベースを開き、スキーマを作成します* /
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);
/ *データベースを開き、スキーマを作成します* /
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);

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

/ *データベースを開き、スキーマを作成します* /
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);

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

#define DATABASE ":memory:"
pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {

    fgets (sInputBuf, BUFFER_SIZE, pFile);

    sqlite3_bind_text(stmt, 1, strtok (sInputBuf, "\t"), -1, SQLITE_TRANSIENT); / *ルートを取得* /
    sqlite3_bind_text(stmt, 2, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    / *ブランチを取得します* /
    sqlite3_bind_text(stmt, 3, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    / *バージョンを取得* /
    sqlite3_bind_text(stmt, 4, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    / *ストップ番号を取得* /
    sqlite3_bind_text(stmt, 5, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    / *車両を入手する* /
    sqlite3_bind_text(stmt, 6, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    / *日付を取得* /
    sqlite3_bind_text(stmt, 7, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* 時間をもらう */

    sqlite3_step(stmt);        / * SQLステートメントを実行します* /
    sqlite3_clear_bindings(stmt);    / *バインディングをクリアします* /
    sqlite3_reset(stmt);        / * VDBEをリセットします* /

    n++;
}
fclose (pFile);

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

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

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

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

21 revs, 12 users 42%



Answer #1

バルクデータをデータベースに挿入するには、ContentProviderを使用します。以下の方法は、バルクデータをデータベースに挿入するために使用されます。これにより、SQLiteのINSERT per secondのパフォーマンスが向上します。

private SQLiteDatabase database;
database = dbHelper.getWritableDatabase();

public int bulkInsert(@NonNull Uri uri, @NonNull ContentValues[] values) {

database.beginTransaction();

for (ContentValues value : values)
 db.insert("TABLE_NAME", null, value);

database.setTransactionSuccessful();
database.endTransaction();

}

bulkInsertメソッドを呼び出します。

App.getAppContext().getContentResolver().bulkInsert(contentUriTable,
            contentValuesArray);