When inserting large amount of data to DB in Rails, large number of insert statements take a lot of time. We can reduce this time dramatically using mysqlimport, which is mysql tool to insert bulk data into the DB quickly, quoting mysql words about mysqlimport: "reads rows from a text file into a table at a very high speed". To use mysqlimport in updating records also, not only in inserting new records, we used a common trick for this problem: Creating a temporary table, inserting updated records(with ids) in this table, join the temporary table with target table on id column and update target table columns accordingly, then drop the temporary table. You can find this idea illustrated for exampel here: http://dba.stackexchange.com/questions/11811/mysql-csv-update-not-insert-into-existing-table


So here is a helper class to use it within Rails application.
 
class SqlWriter

  ID_STR = 'id'
  CREATED_AT_STR = 'created_at'
  UPDATED_AT_STR = 'updated_at'
  NULL_STR = '\N'
  COMMA_STR = ','
  attr_accessor :insert_sql_file, :update_sql_file
  # klass is the class of the records we will deal with
  # sql_dir_path is the directory which will contain the sql data file(text file).
  def initialize(klass, sql_dir_path)
    @klass = klass
    @temp_table_name = "temp_#{klass.table_name}_#{Time.now.to_s(:db).gsub(/-| |:/,'_')}_#{SecureRandom.hex[0..10]}"
    @insert_sql_file = File.new("#{sql_dir_path}/#{klass.table_name}.txt", 'w')
    @update_sql_file = File.new("#{sql_dir_path}/#{@temp_table_name}.txt", 'w')
    @current_time_in_db_format = Time.now.to_s(:db)
    @insert_fields = klass.new.attributes.except(ID_STR).keys
    @update_fields = klass.new.attributes.keys
    @records_need_update = false
  end

  def write_record_to_sql_file(record)
    row_data = get_sql_row(record)
    if record.new_record?
      @insert_sql_file.write("#{row_data}\n")
    else
      @update_sql_file.write("#{row_data}\n")
    end
  end

  def insert_records_to_database
    @insert_sql_file.close
    @update_sql_file.close
    config   = Rails.configuration.database_configuration
    database = config[Rails.env]["database"]
    username = config[Rails.env]["username"]
    password = config[Rails.env]["password"]
    host = config[Rails.env]["host"]
    insert_columns_orders = @insert_fields.join(',')
    `mysqlimport -u #{username} -p#{password} -h #{host} --columns='#{insert_columns_orders}' --local --fields-terminated-by=',' #{database} #{Shellwords.escape(@insert_sql_file.path)}`
    if @records_need_update
      ActiveRecord::Base.connection.execute("CREATE TABLE #{@temp_table_name} LIKE #{@klass.table_name};")
      update_columns_orders = @update_fields.join(',')
      `mysqlimport -u #{username} -p#{password} -h #{host} --columns='#{update_columns_orders}' --local --fields-terminated-by=',' #{database} #{Shellwords.escape(@update_sql_file.path)}`
      set_fields = @insert_fields.map{|field| "#{@klass.table_name}.#{field}=#{@temp_table_name}.#{field}"}.join(',')
      ActiveRecord::Base.connection.execute("UPDATE #{@klass.table_name} INNER JOIN #{@temp_table_name} ON #{@klass.table_name}.id = #{@temp_table_name}.id SET #{set_fields}")
      ActiveRecord::Base.connection.execute("DROP TABLE #{@temp_table_name}")
    end
    File.delete(@update_sql_file)
  end

  private
    def get_sql_row(record)
      if record.new_record?
        result = record.attributes.except(ID_STR).values
        fields = @insert_fields
      else
        result =  record.attributes.values
        fields = @update_fields
        @records_need_update = true
      end
      result.each_with_index do |item, index|
        if item.class == Date || item.class == Time
          result[index] = item.to_s(:db)
        elsif item == true || item == false
          result[index] = item ? 1 : 0
        elsif item == nil
          if fields[index] == CREATED_AT_STR || fields[index] == UPDATED_AT_STR
            result[index] = @current_time_in_db_format
          else
            result[index] = NULL_STR
          end
        end
      end
      result.join(COMMA_STR)
    end
end

For example, assume that we are inserting large number of records of User model:
 
sql_file_dir = "path/to/some/dir"
sql_writer = BulkDataWriter.new(User, sql_file_dir)
alot_of_data.each do |data|
  #......
  user = User.new(user_attributes)
  sql_writer.write_record_to_sql_file(user)
end
sql_writer.insert_records_to_database 
And you will have you data inserted to DB!