SQLite にデータを入れる際にインデックスを作ってからインポートするか，インポートしてからインデックスをつくるか
仕事で1000万件ほどのデータをSQLite にインポートしていたのですがなかなか処理が終りません…
CSV ファイルなどに大量にあるデータをまとめて SQLite のDB にインポートするとします． インポート先のテーブルにはいくつかインデックスをつけたいとします．
こういう場合に，インデックスをつけたテーブルをつくってそこにインポートするのと， テーブルだけつくってデータをインポートしてから改めてインデックスをつくるのとではどちらが速いでしょうか？
普通に考えればインポートしてからまとめてインデックスを作成した方が速くなりそうだと想像はつくのですが，実際のところどうなのでしょう？ 速くなるとしたら何％くらい違うのでしょうか？
ということで実測してみました．
(511) cat create-table.sql CREATE TABLE Logs( ID INTEGER PRIMARY KEY , LogTime DATETIME , LogLevel TEXT , Message TEXT );
(512) cat create-index.sql CREATE INDEX Logs_ID_IDX ON Logs(ID); CREATE INDEX Logs_LogTime_IDX ON Logs(LogTime);
(513) cat bench_create_index_and_insert.sh
DBFILE=Logs.sqlite
DATA_FILE=$1
sqlite3 ${DBFILE} < create-table.sql
sqlite3 ${DBFILE} < create-index.sql
sqlite3 -separator " " ${DBFILE} ".import ${DATA_FILE} Logs"(514) cat bench_insert_and_create_index.sh
DBFILE=Logs.sqlite
DATA_FILE=$1
sqlite3 ${DBFILE} < create-table.sql
sqlite3 -separator " " ${DBFILE} ".import ${DATA_FILE} Logs"
sqlite3 ${DBFILE} < create-index.sql
- A:インデックス作成→インポート
- B:インポート→インデックス作成
| データ数 | A(秒) | B(秒) | B/A(%) |
|---|---|---|---|
| 100000 | 3.673 | 2.504 | 68.173 |
| 200000 | 9.724 | 6.598 | 67.853 |
| 300000 | 14.682 | 11.693 | 79.642 |
| 400000 | 22.097 | 16.972 | 76.807 |
| 500000 | 29.678 | 23.148 | 77.997 |
| 600000 | 36.227 | 28.596 | 78.936 |
| 700000 | 42.295 | 34.468 | 81.494 |
| 800000 | 51.668 | 40.596 | 78.571 |
| 900000 | 58.526 | 47.404 | 80.996 |
| 1000000 | 63.243 | 53.073 | 83.919 |
予想通りですが全体的にインポートしてからインデックス作成をする方が速いようですね． 10万件のインポートだとその差は約 30% ．しかしデータが増えるにつれて差が減ってきて 100万件になると20%程度の差になります．
1000万件ほどになると大きな差はないのかもしれませんね… ということで処理が終わるのをのんびり待つことにします…
以下テスト環境について．
- MacBook 13 inch
- CPU : Intel Core 2 Duo 2.4GHz
- Memory : 4GB 1067 MHz DDR3
(503) uname -a Darwin himane.local 10.2.0 Darwin Kernel Version 10.2.0: Tue Nov 3 10:37:10 PST 2009; root:xnu-1486.2.11~1/RELEASE_I386 i386 sawai@himane[22:41:43] ~ (504) sqlite3 -version 3.6.22
