[解決済み] SQLiteのINSERT/per-secondのパフォーマンスを向上させる
質問
SQLiteを最適化するのは難しいです。Cアプリケーションのバルクインサートのパフォーマンスは、1秒間に85回のインサートから、1秒間に96,000回以上のインサートまで、さまざまです。
背景 デスクトップアプリケーションの一部としてSQLiteを使用しています。XML ファイルに保存された大量の設定データを解析し、SQLite データベースにロードして、アプリケーションの初期化時にさらに処理します。SQLite は高速で、特別な設定を必要とせず、データベースが単一のファイルとしてディスクに保存されるため、このような状況に最適です。
根拠は？ 当初、私は自分の見ているパフォーマンスに失望していました。 SQLiteの性能は、データベースの設定やAPIの使い方によって（一括挿入も選択も）大きく変わることがわかりました。そのため、このコミュニティWikiエントリを作成して、Stack Overflowの読者と結果を共有し、他の人が同じ調査をする手間を省くことが賢明だと思いました。
実験の様子 一般的な意味でのパフォーマンスのヒントについて単純に話すのではなく（つまり "トランザクションを使用する！" というように、C言語のコードを書き、その中で 実際に測定する を選択することができます。まずは簡単なデータから。
- の28MBのTAB区切りテキストファイル（約865,000レコード）。 トロント市の完全な輸送スケジュール
- テストマシンは3.60GHzのP4で、Windows XPが動作しています。
- でコンパイルしています。 ビジュアル C++ 2005 as "Release" with "Full Optimization" (/Ox) and Favor Fast Code (/Ot)です。
- 私は、SQLite "Amalgamation" を、テストアプリケーションに直接コンパイルして使っています。私が持っているSQLiteのバージョンは少し古いのですが（3.6.7）、これらの結果は最新のリリースと同等だと思います（そうでないと思われる方はコメントを残してください）。
コードを書いてみよう
コードです。 テキストファイルを一行ずつ読んで、文字列を値に分割し、そのデータをSQLiteデータベースに挿入する簡単なC言語プログラムです。このコードのquot;baseline"バージョンでは、データベースは作成されますが、実際にデータを挿入することはありません。
/*************************************************************
Baseline code to experiment with SQLite performance.
Input data is a 28 MB TAB-delimited text file of the
complete Toronto Transit System schedule/route info
from http://www.toronto.ca/open/datasets/ttc-routes/
**************************************************************/
#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; /* Route */
char * sBR = 0; /* Branch */
char * sVR = 0; /* Version */
char * sST = 0; /* Stop Number */
char * sVI = 0; /* Vehicle */
char * sDT = 0; /* Date */
char * sTM = 0; /* Time */
char sSQL [BUFFER_SIZE] = "\0";
/*********************************************/
/* Open the Database and create the Schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
/*********************************************/
/* Open input file and import into Database*/
cStartClock = clock();
pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {
fgets (sInputBuf, BUFFER_SIZE, pFile);
sRT = strtok (sInputBuf, "\t"); /* Get Route */
sBR = strtok (NULL, "\t"); /* Get Branch */
sVR = strtok (NULL, "\t"); /* Get Version */
sST = strtok (NULL, "\t"); /* Get Stop Number */
sVI = strtok (NULL, "\t"); /* Get Vehicle */
sDT = strtok (NULL, "\t"); /* Get Date */
sTM = strtok (NULL, "\t"); /* Get Time */
/* ACTUAL INSERT WILL GO HERE */
n++;
}
fclose (pFile);
printf("Imported %d records in %4.2f seconds\n", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);
sqlite3_close(db);
return 0;
}
コントロール"
このコードをそのまま実行しても、実際にはデータベース操作は行われませんが、生のCファイルI/Oや文字列処理操作がどの程度の速度で行われるかを知ることができます。
<ブロッククオート864913レコードを0.94で取り込みました。 秒
素晴らしい! 実際に挿入を行わないのであれば、1秒間に92万回の挿入を行うことができます :-)
最悪のケース。
ファイルから読み込んだ値を使って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);
これは、SQLが挿入ごとにVDBEコードにコンパイルされ、挿入ごとに独自のトランザクションで発生するため、遅くなります。 どれくらい遅い？
<ブロッククオート864913レコードを9933.61でインポート 秒
やべえええええええええええええええええええええええええ 2時間45分！？それって、たった 1秒間に85回の挿入。
トランザクションを使用する
デフォルトでは、SQLite は一意のトランザクション内ですべての INSERT / UPDATE ステートメントを評価します。大量の挿入を行う場合は、トランザクションの中で操作をラップすることが推奨されます。
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);
864913レコードを38.03秒でインポート 秒
それでいい。すべての挿入を1つのトランザクションにまとめるだけで パフォーマンスが向上し 1秒間に23,000回のインサートが可能です。
プリペアドステートメントを使用する
トランザクションを使用することで大幅に改善されましたが、挿入のたびにSQL文を再コンパイルしても、同じSQLを何度も使用するのであれば意味がありません。そこで
sqlite3_prepare_v2
を使用して SQL 文を一度コンパイルし、パラメータをその文にバインドします。
sqlite3_bind_text
:
/* Open input file and import into the database */
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"); /* Get Route */
sBR = strtok (NULL, "\t"); /* Get Branch */
sVR = strtok (NULL, "\t"); /* Get Version */
sST = strtok (NULL, "\t"); /* Get Stop Number */
sVI = strtok (NULL, "\t"); /* Get Vehicle */
sDT = strtok (NULL, "\t"); /* Get Date */
sTM = strtok (NULL, "\t"); /* Get Time */
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;
864913レコードを16.27でインポートしました。 秒
いいねー もうちょっとだけコードがあります。
sqlite3_clear_bindings
と
sqlite3_reset
)に比べて、2倍以上のパフォーマンスを発揮しています。
1秒間に53,000回の挿入を実現。
PRAGMA synchronous = OFF
デフォルトでは、SQLite は OS レベルの書き込みコマンドを発行した後、一時停止します。これはデータがディスクに書き込まれることを保証します。設定により
synchronous = OFF
この場合、SQLite は単にデータを OS に渡して書き込ませ、その後続行するように指示します。データがプラッターに書き込まれる前にコンピュータが壊滅的なクラッシュ（または電源障害）に見舞われた場合、データベースファイルが破損する可能性があるのです。
/* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);
864913件のレコードを12.41秒でインポート 秒
改善幅は小さくなりましたが、最大で 1秒間に69,600回の挿入を実現。
PRAGMA journal_mode = MEMORY (ジャーナルモード = メモリ)
を評価することにより、ロールバックジャーナルをメモリに格納することを検討します。
PRAGMA journal_mode = MEMORY
. トランザクションは高速になりますが、トランザクション中に電源が切れたりプログラムがクラッシュしたりすると、データベースが破損した状態で、トランザクションが一部完了した状態で放置される可能性があります。
/* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);
864913件のレコードを13.50秒でインポート 秒
での最適化より少し遅い。 64,000回/秒の挿入回数。
PRAGMA synchronous = OFF と PRAGMA journal_mode = MEMORY (ジャーナルモード = メモリ)
前の2つの最適化を組み合わせてみましょう。クラッシュした場合の）リスクは少し高くなりますが、私たちはデータをインポートしているだけです（銀行を経営しているわけではありません）。
/* Open the database and create the schema */
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);
864913レコードを12.00でインポートしました。 秒
ファンタスティック! 私たちができることは 1秒間に72,000回のインサートが可能です。
インメモリデータベースの利用
念のため、これまでの最適化の上に、データベースのファイル名を再定義して、完全にRAM上で作業するようにしましょう。
#define DATABASE ":memory:"
864913レコードを10.94秒でインポート 秒
データベースをRAMに保存するのは超実用的ではありませんが、以下のようなパフォーマンスが可能なのは印象的です。 1秒間に79,000回のインサートが可能です。
Cコードのリファクタリング
特に SQLite の改善というわけではありませんが、私は余分な
char*
の代入演算は
while
のループになります。の出力を渡すために、このコードをすばやくリファクタリングしてみましょう。
strtok()
を直接
sqlite3_bind_text()
そして、コンパイラにスピードアップを図ってもらうのです。
pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {
fgets (sInputBuf, BUFFER_SIZE, pFile);
sqlite3_bind_text(stmt, 1, strtok (sInputBuf, "\t"), -1, SQLITE_TRANSIENT); /* Get Route */
sqlite3_bind_text(stmt, 2, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Branch */
sqlite3_bind_text(stmt, 3, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Version */
sqlite3_bind_text(stmt, 4, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Stop Number */
sqlite3_bind_text(stmt, 5, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Vehicle */
sqlite3_bind_text(stmt, 6, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Date */
sqlite3_bind_text(stmt, 7, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Time */
sqlite3_step(stmt); /* Execute the SQL Statement */
sqlite3_clear_bindings(stmt); /* Clear bindings */
sqlite3_reset(stmt); /* Reset VDBE */
n++;
}
fclose (pFile);
注：本物のデータベースファイルを使うことに戻りました。インメモリデータベースは高速ですが、必ずしも実用的ではありません。
<ブロッククオート864913レコードを8.94でインポートしました。 秒
パラメータバインディングで使用している文字列処理コードを少しリファクタリングすることで、以下のようなことが可能になりました。 96,700回/秒の挿入回数。 これはもう、安心ですね。 十分な速さ . 他の変数（ページサイズやインデックス作成など）を調整し始めたら、これが私たちのベンチマークになるでしょう。
まとめ（ここまで）
まだ、一緒にいてくれるかな! この道を歩み始めた理由は、バルクインサートのパフォーマンスがSQLiteによって大きく異なるため、操作を高速化するためにどのような変更が必要なのかが必ずしも明らかでないからです。同じコンパイラ（とコンパイラオプション）、同じバージョンの SQLite、同じデータを使って、コードと SQLite の使い方を最適化した結果、次のようになりました。 最悪の場合、1秒間に85回の挿入が可能だったのが、1秒間に96,000回以上挿入できるようになりました。
CREATE INDEXしてからINSERTするのと、INSERTしてからCREATE INDEXするのでは、どちらがいいのでしょうか？
計測を開始する前に
SELECT
のパフォーマンスは、インデックスを作成することが分かっています。以下の回答で、一括挿入を行う場合、データが挿入された後にインデックスを作成する方が高速であることが示唆されています（最初にインデックスを作成してからデータを挿入するのとは対照的です）。試してみましょう。
インデックスを作成してからデータを挿入する
sqlite3_exec(db, "CREATE INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
...
864913レコードを18.13でインポート 秒
データを挿入し、インデックスを作成する
...
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "CREATE INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
864913件のレコードを13.66秒でインポート 秒
予想通り、1つの列にインデックスが付けられると一括挿入は遅くなりますが、データを挿入した後にインデックスが作成されると違いが出てきます。インデックスを作成しない場合のベースラインは、1秒あたり96,000回の挿入です。 先にインデックスを作成してからデータを挿入すると、1秒間に47,700回の挿入が可能になり、先にデータを挿入してからインデックスを作成すると、1秒間に63,300回の挿入が可能になります。
他のシナリオを試してみたいという提案も喜んでお受けしますよ...。また、近いうちにSELECTクエリ用に同様のデータをコンパイルする予定です。
解決方法は？
いくつかのヒントがあります。
- 挿入/更新はトランザクションに入れる。
-
古いバージョンの SQLite の場合 - あまり偏執的でないジャーナルモードを検討してください (
pragma journal_mode). そこにはNORMALがあり、さらにOFFOSがクラッシュしたときにデータベースが壊れることをあまり心配しないのであれば、挿入速度を大幅に向上させることができます。アプリケーションがクラッシュしても、データは問題ないはずです。なお、新しいバージョンではOFF/MEMORYの設定は、アプリケーションレベルのクラッシュに対して安全ではありません。 -
ページサイズを弄ることでも違いが出てきます(
PRAGMA page_size). ページサイズを大きくすると、より大きなページがメモリに保持されるため、読み込みや書き込みが少し速くなる可能性があります。ただし、データベースにより多くのメモリが使用されることに注意してください。 -
インデックスがある場合は
CREATE INDEXを実行した後、すべての挿入を行います。これは、インデックスを作成してから挿入を行うよりもはるかに高速です。 - SQLiteに同時アクセスする場合、かなり注意が必要です。書き込みが行われるとデータベース全体がロックされ、複数の読者が可能ですが、書き込みはロックアウトされます。これは新しいSQLiteのバージョンでWALが追加されたことにより、多少改善されました。
-
省スペースを活用する...データベースが小さいほど高速になる。例えば、キーと値のペアがある場合、キーを
INTEGER PRIMARY KEY可能であれば、テーブルの一意な行番号カラムを置き換えることができます。 - 複数のスレッドを使用している場合は 共有ページキャッシュ これにより、読み込まれたページをスレッド間で共有できるようになり、高価な I/O 呼び出しを避けることができます。
-
を使用しないでください。
!feof(file)!
関連
-
[解決済み] flexible array member not at end of structエラーの原因は何ですか？
-
[解決済み] 0.1fを0にすると、なぜ10倍もパフォーマンスが落ちるのですか？
-
[解決済み] ATTACHで開いたSQLiteデータベースファイルのテーブルを一覧表示するにはどうすればよいですか？
-
[解決済み] SQLiteでテーブルが存在するかどうかを確認するにはどうすればよいですか？
-
[解決済み] Swift Betaのパフォーマンス：配列のソート
-
[解決済み] SQLite - UPSERT *not* INSERT or REPLACE
-
[解決済み] なぜsizeof(x++)はxをインクリメントしないのですか？
-
[解決済み] なぜC言語では構造体を頻繁にtypedefする必要があるのですか？
-
[解決済み] フリーは、どのように無料化を知っているのですか？
-
[解決済み] Intel CPU の _mm_popcnt_u64 で、32 ビットのループカウンターを 64 ビットに置き換えると、パフォーマンスが著しく低下します。
最新
-
nginxです。[emerg] 0.0.0.0:80 への bind() に失敗しました (98: アドレスは既に使用中です)
-
htmlページでギリシャ文字を使うには
-
ピュアhtml+cssでの要素読み込み効果
-
純粋なhtml + cssで五輪を実現するサンプルコード
-
ナビゲーションバー・ドロップダウンメニューのHTML+CSSサンプルコード
-
タイピング効果を実現するピュアhtml+css
-
htmlの選択ボックスのプレースホルダー作成に関する質問
-
html css3 伸縮しない 画像表示効果
-
トップナビゲーションバーメニュー作成用HTML+CSS
-
html+css 実装 サイバーパンク風ボタン
おすすめ
-
_CRT_SECURE_NO_WARNINGS エラーメッセージ、解決方法
-
[C] レポートエラー 代入の左オペランドとしてlvalueが必要
-
C 言語のポインタ配列のポインタ型、ポインタに値を割り当てるために配列名を使用、コンパイル時の警告：互換性のないポインタ型からの初期化
-
[解決済み] Valgrind が初期化されていないバイトについて警告する
-
[解決済み] c または c++ 用のシンプルな 2 次元クロスプラットフォームグラフィックスライブラリ？[クローズド］
-
[解決済み] C - Setデータ構造を実装するには？
-
[解決済み] C言語では「?」演算子は何をするのですか？
-
[解決済み] 難読化Cコードコンテスト2006。sykes2.cの解説をお願いします。
-
[解決済み] printfは、フォーマット文字列の中に改行がないと、呼び出し後にフラッシュしないのはなぜですか？
-
[解決済み] フリーは、どのように無料化を知っているのですか？