はじめに
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
#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トランザクションのレコード数を決めると良いかと思います。
もし、記述について誤りがあったり、気になることがあれば、編集リクエストやコメントでフィードバックしていただけると助かります。