Help us understand the problem. What is going on with this article?

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

はじめに

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万レコードぐらいから処理性能が落ち着いてきているように思いえます。

SQLite_Transaction.jpg

最後に

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

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした