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

約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ベタ書きとかしたくないんですが、背に腹は代えられないです。
どなたか他に良い書き方を知っていましたら、ご教示頂けるとありがたい。

Also published on Medium.