SQLite3 : 1トランザクションのレコード数と処理時間の関係（測定）

13615 ワード
SQL SQLite3 sqlite SQL テキストリンク

はじめに

SQLite で明示的にトランザクションの指示をしないと、auto commit となり、更新処理が遅くなることは有名な話です。1トランザクションで大量レコードを操作できれば速いのですが、組み込みシステムなどでは、メモリ使用量など気になります。
そこで週末に測定プログラムを作成し、1トランザクションのレコード数と処理時間の関係を確認してみました。

測定プログラム

SQLite 3.31.1 を使って、10万レコードをINSERTで登録する処理時間を計測しています。
最初に、1トランザクション、約100バイトのレコードを10万レコードで登録処理を行って、測定します。その後、1トランザクションのレコード数を1/2に減らして、処理時間を測定します。後半が不利にならないように処理時間の測定毎にデータベースファイルを新規作成します。
以下は測定プログラムです。ここでは見易いようにエラー処理を省略しています。構造化、エラー処理を含めたプログラムは、GitHub の方へ置いています。
https://github.com/kanegoon/sqlite-evaluation/blob/master/src/evaltrans.c

evaltrans.c

#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include <unistd.h>
#include "sqlite3.h"

#define LOOP_COUNTER 100000
#define DB_NAME "eval.db"
#define SQL_BEGIN "BEGIN;"
#define SQL_COMMIT "COMMIT"
#define SQL_CREATE "CREATE TABLE evaltab (no INT, data TEXT, time REAL);"
#define SQL_WAL "PRAGMA JOURNAL_MODE=WAL;"
const char *data =
"01234567890123456789012345678901234567890123456789"
"01234567890123456789012345678901234567890123456789";

int main(int argc, char **argv){
  sqlite3 *db;
  int rc = SQLITE_OK;
  int i, j, k;
  char *zErrMsg = 0;
  char *zSQL = "INSERT INTO evaltab VALUES (?, ?, ?);";
  sqlite3_stmt *pStmt = 0;
  struct timespec tStart, tEnd;

  for(i=LOOP_COUNTER; i>0; i=(int)i/2){
    /* データベースファイルをオープン */
    rc = sqlite3_open(DB_NAME, &db);
    /* テーブルを作成 */
    rc = sqlite3_exec(db, SQL_CREATE, 0, NULL, &zErrMsg);
    /* INSERT文のパース  */
    rc = sqlite3_prepare_v2(db, zSQL, -1, &pStmt, NULL);
    k=0;
    clock_gettime(CLOCK_REALTIME, &tStart); /* 測定の開始 */ 
    for(j=0; j<LOOP_COUNTER; j++){
      if((j%i)==0){
        /* トランザクションの開始 */
        rc = sqlite3_exec(db, "BEGIN", 0, NULL, &zErrMsg);
      }      
      /* バインド変数の処理 */
      rc = sqlite3_bind_int(pStmt, 1, j);
      rc = sqlite3_bind_text(pStmt, 2, data, -1, SQLITE_TRANSIENT);
      rc = sqlite3_bind_double(pStmt, 3, (double)time(NULL));
      /* INSERTの実行 */
      rc = sqlite3_step(pStmt);
      rc = sqlite3_reset(pStmt);
      if(((j+1)%i)==0 || LOOP_COUNTER<(j+1)){
        /* トランザクションの終了 */
        rc = sqlite3_exec(db, "COMMIT;", 0, NULL, &zErrMsg);
        k++;
      }
    }
    clock_gettime(CLOCK_REALTIME, &tEnd);   /* 測定の終了 */
    printf("%7d TRANS, %7d Records : ",k,i);
    if(tEnd.tv_nsec < tStart.tv_nsec){
      printf("%10ld.%09ld (sec)\n",tEnd.tv_sec - tStart.tv_sec - 1
            ,tEnd.tv_nsec + 1000000000 - tStart.tv_nsec);
    }else{
      printf("%10ld.%09ld (sec)\n",tEnd.tv_sec - tStart.tv_sec
            ,tEnd.tv_nsec - tStart.tv_nsec);
    }
    sqlite3_finalize(pStmt);  /* データベースファイルのクローズ */
    sqlite3_close(db);        /* 作成されたデータベースファイルを削除 */
    remove(DB_NAME);
  }
  return(0);
}

測定結果

以下は測定結果です。実行したトランザクション数、1トランザクションの処理レコード数、全処理時間を出力しています。
1トランザクションで、10万レコードを全て処理する方が速いと思われがちですが、実際には1トランザクション 2万5千レコードと同じくらいの処理性能となっています。

$ ./sqliteeval1
      1 TRANS,  100000 Records :          0.239777750 (sec)
      2 TRANS,   50000 Records :          0.231897269 (sec)
      4 TRANS,   25000 Records :          0.236258999 (sec)
      8 TRANS,   12500 Records :          0.241520257 (sec)
     16 TRANS,    6250 Records :          0.275872070 (sec)
     32 TRANS,    3125 Records :          0.352197129 (sec)
     64 TRANS,    1562 Records :          0.434121667 (sec)
    128 TRANS,     781 Records :          0.621113352 (sec)
    256 TRANS,     390 Records :          1.018182370 (sec)
    512 TRANS,     195 Records :          1.737800108 (sec)
   1030 TRANS,      97 Records :          2.837289527 (sec)
   2083 TRANS,      48 Records :          5.392647574 (sec)
   4166 TRANS,      24 Records :         10.314117191 (sec)
   8333 TRANS,      12 Records :         18.134931926 (sec)
  16666 TRANS,       6 Records :         35.963099118 (sec)
  33333 TRANS,       3 Records :         69.390048111 (sec)
 100000 TRANS,       1 Records :        206.416854369 (sec)

散布図

1トランザクション内のレコード数と処理時間を散布図にしました。縦軸は 10万レコード登録の処理時間(秒)です。横軸は 1トランザクションのレコード数です。
グラフから、1トランザクションの1万レコードぐらいから処理性能が落ち着いてきているように思いえます。

最後に

1トランザクションのレコード数と処理時間の関係について試してみました。今回は、1トランザクション、1万レコードぐらいで処理性能が落ち着くような傾向がありました。ページサイズ、レコードサイズ、ジャーナルモードによって結果が変わると思われます。これらの条件とアプリケーションの要求仕様を考慮して、1トランザクションのレコード数を決めると良いかと思います。

もし、記述について誤りがあったり、気になることがあれば、編集リクエストやコメントでフィードバックしていただけると助かります。