業務でcsvを読み込んでそれをDBに保存するというロジックを実装した時にパフォーマンス面、DB設計について色々と思ったので、共有することにした。
状況設定
読み込むcsvは6000行*70列で、1列は1個のテーブルに対応している(<= ここがやばい原因)。このcsvファイル内のデータをインポートしようとした。
ざっくり見積もり
railsのimportメソッドを使うことを前提にしてざっくりまず発行クエリ数などを見積もってみる。
importメソッドは基本的に1テーブルごとに使用するので、これで単純計算70クエリ。しかし現実問題、一回のimportメソッドで6000レコードを突っ込むのはDBに対して半端ない負担になるので200くらいずつに分割。これで1列あたり30クエリなので、合計 70 * 30 = 2100クエリ。まあDBにできるだけ負担をかけないようにsleepするとしても、うーん、まあ悪くはないかも。
現実は甘くない
いざ実装スタート。非同期処理のためにdelayed_jobを使っていたが、あれ、「delayed_jobテーブルには入らない！？」handlerカラムにcsvの中身がそのまま入るので、まあ冷静に考えてそんな量なんか入らないよね。やむなく30レコードずつ分割(ファイルをどっか保存して処理すれば良いんじゃんというツッコミはしないで...)。これで1列あたり200クエリなので、合計 70 * 200 = 14000クエリ(汗)。まあこれはこれで良いとして、いざコードを動かしてみた。すると以下のようなことが発覚した。
ActiveRecordなめんな
いざimport開始と思いきや処理が重いことよ。30レコード突っ込むのに30s。しかも突っ込めば突っ込むほど遅くなる。まあ確かに前処理が多いのは仕方がないが、それにしても遅すぎる。しかもメモリも結構食う。このペースだと6000行のimportで1h30min。恐ろしや。
解決策
結局ActiveRecordは「重い」、「メモリ食う」、「インスタンス生成で時間がかかる」のような問題が生じるので、本当にクエリ数が多くなる場合や、大量のデータを入れる場合には不向き。
ActiveRecord::Base.connection.update(insert_sql)
のように、実際に生のsql文を書いたところ、30レコード突っ込むのに7sくらいしかかからなくなり、しかもメモリも食わない。6000行も30minくらいでimportできるようになる計算。万々歳！
反省
今回のような問題が発生した原因の一つとしてDBの過剰な正規化があげられる。70列70テーブルとかいたが、これらは実質は同種の「データ結果」を記録したものであり、突っ込むデータの型(int, string, datetime, floatなど)は違えど、処理としては全く同じだった。そこで正規化しか考えずにテーブルを切ったところ、それによる弊害を受けてしまったのである。まあ、正規化は確かに今後のデータの永続化おいて非常に重要ではあるが、正規化しすぎてデータが扱いにくくなってしまってもそれはそれで問題。まあ今回このようにデータが扱いにくいのはここだけなので良しとしよう...
まとめ
- テーブルの正規化は良いが、ちゃんとデータの扱いにくさ、実装を考えて。
- 大量データのインポートはActiveRecordに頼らず生sqlで！