SQLite3で大量データ+複雑なVIEWをCOUNT(*)すると固まる(ほど遅い)
大量データ(100万件以上)を使ったときのメモ。
SQLite3は、データをフェッチする前の「select count(*)」が遅いみたい。
例えば、複雑なVIEWを多段に組んだ結果をテンポラリテーブルに入れるとき、先に件数が欲しいからと
CREATE TEMP TABLE FIND_COUNT AS SELECT COUNT(*) FROM VIEW_X WHERE ...; CREATE TEMP TABLE FIND_RECORD AS SELECT * FROM VIEW_X WHERE ...;
とやると、最初のCOUNT(*)で引っかかってパフォーマンスがまったく出ない。そこで
CREATE TEMP TABLE FIND_RECORD AS SELECT * FROM VIEW_X WHERE ... LIMIT 10000 OFFSET 0; CREATE TEMP TABLE FIND_COUNT AS SELECT COUNT(*) FROM VIEW_X WHERE ...; INSERT INTO FIND_RECORD SELECT * FROM VIEW_X WHERE ... LIMIT -1 OFFSET 10000;
とすると、
- とりあえず先頭部分のデータだけを先に取れる。(これは速い。1ページ目表示用などに)
- 次に件数を取る。(全体の件数を調べる。遅いけど我慢)
- 残りを取る。(LIMITE-OFFSETを小刻みに実行すればプログレス処理もできる。遅くなるけど)
となって、人に優しい画面を実現できる。
200万件に8段のVIEWを掛けたときに30秒もブロックされてしまう部分があった。自前で中間ファイルを作ってみたり、Rubyじゃ遅いからとCで書き直してみたりしたけれど、SQLite3を使い倒す方式がコストパフォーマンス良い感じ*1。
ATTACH文で複数のDBファイルを簡単に連結できる*2ので、ワークファイル代わりのDBファイルを、作っては捨て作っては捨て、のようなこともできる。活用すれば大事なDBファイルを汚染せずにいろいろ出来るっぽい。
大量データの取り回し、もうちょっと研究の必要あり。