What is the fastest way to create mass HABTM associations in Rails?

Question

I have two tables, with a HABTM relationship in Rails. Something like the following:

class Foo &lt ActiveRecord::Base
  has_and_belongs_to_many :bars
end

class Bar &lt ActiveRecord::Base
  has_and_belongs_to_many :foos
end

Now I have a new Foo object, and want to mass-assign thousands of bars to it, which I've pre-loaded:

@foo = Foo.create
@bars = Bar.find_all_by_some_attribute(:a)

What's the fastest way to do this? I've tried:

@foo.bars = @bars
@foo.bars &lt&lt @bars

And both run really slow, with an entry like the following for each bar:

bars_foos Columns (1.1ms) SHOW FIELDS FROM bars_foos SQL (0.6ms) INSERT INTO bars_foos (bar_id, foo_id) VALUES (100, 117200)

I looked at ar-extensions, but the import function doesn't seem to work without a model (Model.import) which precludes its use for a join table.

Do I need to write the SQL, or does Rails have a prettier way?

Answer 1

I think your best bet performance-wise is going to be to use SQL, and bulk insert multiple rows per query. If you can build an INSERT statement that does something like:

INSERT INTO foos_bars (foo_id,bar_id) VALUES (1,1),(1,2),(1,3)....

You should be able to insert thousands of rows in a single query. I didn't try your mass_habtm method, but it seems like you could to something like:

bars = Bar.find_all_by_some_attribute(:a)
foo = Foo.create
values = bars.map {|bar| "(#{foo.id},#{bar.id})"}.join(",")
connection.execute("INSERT INTO foos_bars (foo_id, bar_id) VALUES #{values}")

Also, if you are searching Bar by "some_attribute", make sure you have that field indexed in your database.

Answer 2

You still might have a look at activerecord-import. It's right that it doesn't work without a model, but you could create a Model just for the import.

class FooBar < ActiveRecord::Base; end

FooBar.import [:foo_id, :bar_id], [[1,2], [1,3]]

You can wrap this in a transaction to ensure the HABTM gets fully populated, as in here:

ActiveRecord::Base.transaction do
  imported_foo = Foo.import( foo_names, foo_values )
  imported_bar = Bar.import( bar_names, bar_values )
  FooBar.import( [:foo_id, :bar_id], imported_foo.ids.zip(imported_bar.ids)
end

Answer 3

This was faster than the equivalent native rails code by a factor of 7:

class &lt&lt Foo
  def mass_habtm(attr_array)
    attr_str = attr_array.map{|a| %Q{'#{a}'} }.uniq.join(",")
    self.connection.execute(%Q{insert into foos_bars (foo_id,bar_id) 
                     select distinct foos.id,bars.id from foos,bars 
                     where foos.id = #{self.id} 
                     and bars.some_attribute in (#{attr_str})})
  end
end

It seems to me that this is a straightforward enough operation that it should be supported efficiently in Rails, I would love to hear if anyone has a cleaner way.

I'm running 2.2.2, maybe it's implemented more efficiently in 3.x? and found the same on 3.0.2.