Rails のActiveRecord に大量のCSV を取り込もうとすると絶望的に遅いので

投稿者: | 2016/05/06

約30万件、46MBのセミコロン区切りテキストをインポート

CSV ファイルを元にモデルにレコードをセットしたかったのですが、思った以上に処理が遅くてあれこれ試行錯誤したので備忘録的に。
Rails のお作法とかはガン無視のSQLベタ打ち実行なので、ほんまにこれで良いのかはちょっと疑問。

ちなみに取り込み対象のテキストファイルは以下の様な仕様。

  • セミコロン区切り
  • 文字コードはShift_JIS
  • 約30万行
  • 1行文字数151文字
  • ファイルサイズ約46MB

レコードは全件洗い替えです。
あとバックエンドのRDBMSはMySQL5.6 で。*01PostgreSQL とかにもLOAD LOCAL DATA INFILE みたいなステートメントはあるんだろうか?

ActiveRecord でnew してsave だと20分かかる

まずは普通にActiveRecord のnew とsave で。

class MyModel < ActiveRecord::Base

  require 'csv'

  module CONSTANTS
    ORIGINAL_FILE = 'tmp/my_data.txt'
    SQL_RESET_SURROGATE_KEY = 'ALTER TABLE my_table AUTO_INCREMENT = 1;'
  end # module CONSTANTS
  CONSTANTS.freeze
  
  def renew_all
    MyModel.delete_all
    MyModel.connection.execute(CONSTANTS::SQL_RESET_SURROGATE_KEY)
    CSV.foreach(CONSTANTS::ORIGINAL_FILE, encoding: 'Shift_JIS:UTF-8', col_sep: ';') do |row|
      record = MyModel.new
      index = 0
      record.hoge = row[index]
      record.piyo = row[index += 1]
      record.fuga = row[index += 1]
      record.save
    end # CSV.foreach
  end # renew_all

end # class MyModel

これがバカみたいに時間がかかる。
30万行46MB の処理に約20分を要した。

gem mysql2-cs-bind でSQL をprepare 実行だと10分でいける

あまりに酷いので、ActiveRecord を介さずgem ‘mysql2-cs-bind’ を使って、prepare ステートメント実行してみた。

class MyModel < ActiveRecord::Base
  
  require 'csv'
  require 'mysql2-cs-bind'

  module CONSTANTS
    ORIGINAL_FILE = 'tmp/my_data.txt'
    SQL_RESET_SURROGATE_KEY = 'ALTER TABLE my_table AUTO_INCREMENT = 1;'
    SQL_INSERT_NEW_RECORD = 'INSERT INTO my_table (hoge, piyo, fuga) VALUES (?, ?, ?);'
  end # module CONSTANTS
  CONSTANTS.freeze
  
  @client = Mysql2::Client.new(
      :host => 'db_host',
      :username => 'db_user',
      :password => 'db_pass',
      :database => 'db_name'
  )
​
  def renew_all
    MyModel.delete_all
    MyModel.connection.execute(CONSTANTS::SQL_RESET_SURROGATE_KEY)
    CSV.foreach(original_file, encoding: 'Shift_JIS:UTF-8', col_sep: ';') do |row|
      values = Array.new
      index = 0
      values.push( row[index] )
      values.push( row[index += 1] )
      values.push( row[index += 1] )
      
      @client.xquery(CONSTANTS::SQL_INSERT_NEW_RECORD, values)
    end # CSV.foreach
  end # renew_all

end # class MyModel

これで10分。
でも、まだ遅い。

まだ遅い、ならばLOAD LOCAL DATA INFILE だ!

いっそMySQL に直接仕事させちゃおうという事で、LOAD LOCAL DATA INFILE ステートメントを実行。

class MyModel < ActiveRecord::Base
  
  require 'csv'
  require 'mysql2-cs-bind'

  module CONSTANTS
    ORIGINAL_FILE = 'tmp/my_file.txt'
    SQL_RESET_SURROGATE_KEY = 'ALTER TABLE my_table AUTO_INCREMENT = 1;'
    SQL_SET_CHARSET_SJIS = 'SET CHARACTER_SET_DATABASE = sjis;'
    SQL_LOAD_LOCAL_DATA = "LOAD DATA LOCAL INFILE '#{ORIGINAL_FILE}'
      INTO TABLE my_table FIELDS TERMINATED BY ';' (
        hoge,
        piyo,
        fuga
    );"
    SQL_SET_CHARSET_UTF8 = 'SET CHARACTER_SET_DATABASE = utf8;'
  end # module CONSTANTS
  CONSTANTS.freeze
​
  def renew_all
    MyModel.delete_all
    MyModel.connection.execute(CONSTANTS::SQL_RESET_SURROGATE_KEY)
    MyModel.connection.execute(CONSTANTS::SQL_SET_CHARSET_SJIS)
    MyModel.connection.execute(CONSTANTS::SQL_LOAD_LOCAL_DATA)
    MyModel.connection.execute(CONSTANTS::SQL_SET_CHARSET_UTF8)
  end # renew_all

end # class MyModel

なんと44秒!!
これだけの差がついてしまうと、ActiveModel がどうとか言っていられないですよなあ…。
(余談ですが、ActiveRecord のconnection を使わずMySQL直でLOAD LOCAL DATA INFILE を実行したら3秒でしたよ。)

あれー?

そんなわけで、Rails のModel にCSVを取り込む処理を書こうとしていたはずなのに、気が付けばMySQL に直接CSVを取り込ませる処理を書いていたっていうね。

ほんまはSQLベタ書きとかしたくないんですが、背に腹は代えられないです。
どなたか他に良い書き方を知っていましたら、ご教示頂けるとありがたい。

脚注

01 PostgreSQL とかにもLOAD LOCAL DATA INFILE みたいなステートメントはあるんだろうか?

Also published on Medium.

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください