Rails/Active Record .save! efficiency question

Question

New to rails/ruby (using rails 3 and ruby 1.9.2), and am trying to get rid of some unnecessary queries being executed.

When I'm running an each do:

apples.to_a.each do |apple|
  new_apple = apple.clone
  new_apple.save!
end

and I check the sql LOG, I see three select statements followed by one insert statement. The select statements seem completely unnecessary. For example, they're something like:

SELECT Fruit.* from Fruits where Fruit.ID = 5 LIMIT 1;
SELECT Color.* from Colors where Color.ID = 6 LIMIT 1;
SELECT TreeType.* from TreeTypes where TreeType.ID = 7 LIMIT 1;
INSERT into Apples (Fruit_id, color_id, treetype_id) values (6, 7, 8) RETURNING "id";

Seemingly, this wouldnt' take much time, but when I've got 70k inserts to run, I'm betting those three selects for each insert will take up a decent amount of time.

So I'm wondering the following:

Is this typical of ActiveRecord/Rails .save! method, or did the previous developer add some sort of custom code?
Would those three select statements, being executed for each item, cause a noticeable amount of extra time?
If it is built into rails/active record, would it be easily bypassed, if that would make it run more efficiently?

tadman tadman · Accepted Answer · 2011-08-12T20:06:12

You must be validating your associations on save for such a thing to occur, something like this:

class Apple < ActiveRecord::Base
  validates :fruit,
    :presence => true
end

In order to validate that the relationship, the record must be loaded, and this needs to happen for each validation individually, for each record in turn. That's the standard behavior of save!

You could save without validations if you feel like living dangerously:

apples.to_a.each do |apple|
  new_apple = apple.clone
  new_apple.save(:validate => false)
end

The better approach is to manipulate the records directly in SQL by doing a mass insert if your RDBMS supports it. For instance, MySQL will let you insert thousands of rows with one INSERT call. You can usually do this by making use of the Apple.connection access layer which allows you to make arbitrary SQL calls with things like execute

Rails/Active Record .save! efficiency question

2 Answers