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. とりあえず先頭部分のデータだけを先に取れる。(これは速い。1ページ目表示用などに)
  2. 次に件数を取る。(全体の件数を調べる。遅いけど我慢)
  3. 残りを取る。(LIMITE-OFFSETを小刻みに実行すればプログレス処理もできる。遅くなるけど)

となって、人に優しい画面を実現できる。


200万件に8段のVIEWを掛けたときに30秒もブロックされてしまう部分があった。自前で中間ファイルを作ってみたり、Rubyじゃ遅いからとCで書き直してみたりしたけれど、SQLite3を使い倒す方式がコストパフォーマンス良い感じ*1

ATTACH文で複数のDBファイルを簡単に連結できる*2ので、ワークファイル代わりのDBファイルを、作っては捨て作っては捨て、のようなこともできる。活用すれば大事なDBファイルを汚染せずにいろいろ出来るっぽい。


大量データの取り回し、もうちょっと研究の必要あり。

*1:凝りに凝ったアプリケーションを作るのでなければ

*2:テーブル名がユニークならDB名での修飾なしでOKというアバウトさが好き