CODEWITHSUNDEEP
databaseruby-on-rails-3activerecord
Sandro L
Sandro L

Reputation: 1150

How to save an array of objects to the database really fast in Rails3?

I have an really big array of objects read from a CSV file and want to save it to the database. This is what I am doing:

# Read CSV
... each do |values|
  new_value = Model.new
  ... # fill properties
  @new_values.push new_value # put it into the array
end

# now save them to the database
@new_values.each do |new_value| 
    new_value.save :validate => false
    @added_counter += 1
end

But this is really slow because it will make a statement for each element in the array. How can this be done in a fast and correct way?

Upvotes: 0

Views: 2607

Answers (2)

Sandro L
Sandro L

Reputation: 1150

This is a solution based on the comments of mu is too short (Thanks!). I use a handmade SQL-Statement and execute it.

  insert_statement = "INSERT INTO table_name (custom_property_from_parameter"
  # the properties from the model are fetched by a method, to keep it a little bit generic, loop trough them and give their names to the statement
  Model.parameter_names.each do |parameter|
    insert_statement += "," + parameter
  end
  insert_statement += ") VALUES "
  @new_values.each do |new_value|
    insert_statement += "(" + params[:custom_property]
    Model.parameter_names.each do |parameter|
      # Rails' nil has to be converted into NULL for SQL
      if nil == new_value.send(parameter)
        insert_statement += ",NULL"
      else
        insert_statement += "," + new_value.send(parameter).to_s
      end
    end
    insert_statement += "),"
    @added_counter += 1
  end
  # Remove the last , with ;
  insert_statement = insert_statement[0..-2]
  insert_statement += ";"
  # now execute the statement
  ActiveRecord::Base.connection.execute insert_statement

This solution takes approximately a third of the time. But it seems a little bit like a hack to me.

Upvotes: 0

nathanvda
nathanvda

Reputation: 50057

Maybe activerecord-import could be useful for you.

It allows to do something like:

books = []
10.times do |i| 
  books << Book.new(:name => "book #{i}")
end
Book.import books

Upvotes: 3

Related Questions