なんだかよくわからない先入観で遅いだろうと決め付けていたが、 http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison によると、 transaction でまとめて実行するとずいぶん早い。
Test 1: 1000 INSERTs
CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));
INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty three');
INSERT INTO t1 VALUES(2,75560,'seventy five thousand five hundred sixty');
... 995 lines omitted
INSERT INTO t1 VALUES(998,66289,'sixty six thousand two hundred eighty nine');
INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred twenty two');
INSERT INTO t1 VALUES(1000,94142,'ninety four thousand one hundred forty two');
SQLite 3.3.3 (sync): 3.823
SQLite 3.3.3 (nosync): 1.668
SQLite 2.8.17 (sync): 4.245
SQLite 2.8.17 (nosync): 1.743
PostgreSQL 8.1.2: 4.922
MySQL 5.0.18 (sync): 2.647
MySQL 5.0.18 (nosync): 0.329
FirebirdSQL 1.5.2: 0.320
Test 2: 25000 INSERTs in a transaction
BEGIN;
CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));
INSERT INTO t2 VALUES(1,298361,'two hundred ninety eight thousand three hundred sixty one');
... 24997 lines omitted
INSERT INTO t2 VALUES(24999,447847,'four hundred forty seven thousand eight hundred forty seven');
INSERT INTO t2 VALUES(25000,473330,'four hundred seventy three thousand three hundred thirty');
COMMIT;
SQLite 3.3.3 (sync): 0.764
SQLite 3.3.3 (nosync): 0.748
SQLite 2.8.17 (sync): 0.698
SQLite 2.8.17 (nosync): 0.663
PostgreSQL 8.1.2: 16.454
MySQL 5.0.18 (sync): 7.833
MySQL 5.0.18 (nosync): 7.038
FirebirdSQL 1.5.2: 4.280
transaction ごとに fsync() を実行するようなので、何度も fsync() を呼ばすに一度で済ませられるからだろうか。
Test 1 では IO 待ちが完全にネックになっている模様。
実際に試してみた結果
$ time ./a.out real 0m10.812s user 0m0.068s sys 0m2.164s $ time ./a.out transaction COMMIT real 0m0.169s user 0m0.044s sys 0m0.032s
確かに早くなってる
ソース
コンパイルは gcc -lsqlite3 sample.c
#include <stdio.h>
#include <sqlite3.h>
#define COUNT 1000
int main(int argc, char *argv[]) {
sqlite3 *db;
char *errmsg;
int rc, i;
rc = sqlite3_open("dat.sqlite3", &db);
if (rc) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
rc = sqlite3_exec(db, "create table if not exists sample(id)", NULL, 0, &errmsg);
if (argv[1] != NULL)
sqlite3_exec(db, "BEGIN", NULL, NULL, &errmsg);
for (i = 0; i < COUNT; i++) {
char statement[100];
sprintf(statement, "insert into sample values(%d)", i);
rc = sqlite3_exec(db, statement, NULL, 0, &errmsg);
}
if (argv[1] != NULL) {
sqlite3_exec(db, "COMMIT", NULL, NULL, &errmsg);
printf("COMMIT");
}
rc = sqlite3_exec(db, "drop table sample", NULL, 0, &errmsg);
sqlite3_close(db);
}
エラー処理はほとんどしてない。
突っ込み待ってます＞＜