programming is hard

Wrap a set of inserts in a transaction to speed up inserts in Rails.

We recently put in a tag system that allows users of our database to tag people or organizations and then send out emails or create address labels from those tags. A problem came up when we implemented functionality to �??Tag All�?? listings on a certain page. Usually this meant 50-100 inserts like so:

ids.each do |i|
  Tag.create :user_id = uid, :entity_id = i
end

This is all well and good, except for the fact that Rails wraps each insert in a transaction, which slows things down a bit. The solution was to wrap the entire loop in a transaction, that way the inserts are committed all at once, assuming they all are successful.

Tag.transaction do
  ids.each do |i|
    Tag.create :user_id = uid, :entity_id = i
  end
end

Here are some numbers. In development mode, with ~40 inserts:
- Without wrapping it in a transaction, it takes 3.66 seconds to complete the 40 inserts
- When wrapped in a transaction, it takes 0.534 seconds for the same set of inserts

Another approach:

Tag.transaction do
  ids.each do |i|
    t = Tag.new :user_id = uid, :entity_id = i
    t.save!
  end
end

This rolls back the entire operation if any inserts are invalid. Depending on the situation, this may be more desirable then having those rows silently fail.