Help us understand the problem. What is going on with this article?

Sqlite3 70万行のレコードInsertで

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

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

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

https://www.sqlite.org/limits.html#max_length

テーブルを分割しようとか正規化しようとか、そういう面倒くさいのは止めたい。初心者だし趣味で使うデータベースだし、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

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

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

https://www.sqlite.org/threadsafe.html

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

https://www.sqlite.org/limits.html#max_length

他引数
-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文を複数個つかうだけで速くなった(まあ、そうよね...)

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした