Sqlite3 70万行のレコードInsertで

70万行Insertって一瞬じゃない？

2時間経っても終わらないんです。

多分、列数が250もあるから。sqlite3_prepare_v2は列数に対しての計算量がO（N²）（Nは列の数）となるらしく、ボトルネックとなっているっぽい。それにSQL文のファイルサイズは1.5GBもある。

テーブルを分割しようとか正規化しようとか、そういう面倒くさいのは止めたい。初心者だし趣味で使うデータベースだし、SELECTを高速でやりたい(JOINとかしたくない)

というわけで無理やりInsertした（している)経緯をまとめました。

sqlファイルをInsert、意味不明なエラーに遭遇す

SQLファイルを作成(はじめの日付文字列以外は全部実数値REAL)

INSERT INTO `TABLE` VALUES 
('2010-01-04 09:01:00',
 2800,2809,2800,2809,12099,12099,
 2800,2809,2795,2800,15726,15726,
 2800,2809,2791,2795,17041,17041,
.....

sqlite3のコマンドラインからファイルをInsert

$ sqlite3 ~/prices.db < TABLE.sql
Out of memory

はいエラー。

こちらのサイトではパス名に日本語名が混じっているのがダメとのこと
http://blog.6vox.com/2014/04/java-sqlite3-mac-out-of-memory.html

日本語入っていないので関係ない。結局わからずじまい。
解決方法は 最新ソースをコンパイル でOKでした。
(これまで使ってたのはUbuntu16.4にデフォルトで入ってたSqlite3)

https://www.sqlite.org/download.html
こちらから最新(20191024)の3.30.1.をDL
sqlite-amalgamation-3300100.zip

解凍してからgccでコンパイル

$ gcc -DSQLITE_THREADSAFE=0 shell.c sqlite3.c -ldl -o sqlite3 -g3 -DSQLITE_MAX_LENGTH=2147483647

行数がえげつないソースファイルが２つだけしかない。好感の持てる造りで助かります。

オプション引数
-DSQLITE_THREADSAFE=0
スレッドセーフでアクセスしない。1人で使うので同時アクセスは発生しない前提。もし1に設定するならばMutexでDBをロックしてくれて、複数人同時利用ができるみたい。

-DSQLITE_MAX_LENGTH=2147483647
文字列TEXTかBLOBの長さの最大長。標準では1GB
書いてて思ったけど、これ今回のケースではいらない。

他引数
-ldl 共有ライブラリをロードする(/usr/local/libのsoファイルなど)
-o sqlite3 出力ファイル名
-g3 gdbでデバッグしたい時のおまじない。

他のオプションを試してみたいならば
https://www.sqlite.org/howtocompile.html
を参照してください。

出来上がった実行ファイルsqlite3で再度Insertを試す

厄介なエラー

$ sqlite3 ~/prices.db < TABLE.sql
Error: string or blob too big 

SQL文の文字数オーバーとのこと。調べてみるとデフォルトで1000000文字が上限だったらしい。以外に少ないのね。
https://www.sqlite.org/limits.html#max_sql_length

1つのINSERT文で1.5GBのレコードすべてを入れようとしていたので足りるわけがない。しかもコンパイルオプションとかで対応できないみたい。Configファイルは見当たらない。じゃあ、ここまできたら一貫して泥臭くやりましょう。gdbでエラーとなっている箇所を捜索

sqlite3.c

SQLITE_PRIVATE int sqlite3RunParser(Parse *pParse, const char *zSql, char **pzErrMsg){
...
while( 1 ){
     n = sqlite3GetToken((u8*)zSql, &tokenType);
     mxSqlLen -= n;
     if( mxSqlLen<0 ){
       pParse->rc = SQLITE_TOOBIG;
       break;  // ここで抜けてエラーとなっていた
     }
....
}

mxSqlLenを設定している箇所を修正

   mxSqlLen = db->aLimit[SQLITE_LIMIT_SQL_LENGTH];
   ---> mxSqlLen = 2147483647; に変えてみた。

SQL文の最大長。ためしに2GBに変更してみた。で実行してみると、ちゃんとInsertできています。が。

遅い

そうだろうね！

あとで気づいたこと

・Insert文が1GBを超えるとsqliteは急激に遅くなった
・Insert文を複数個つかうだけで速くなった(まあ、そうよね...)