在 Rails 中创建大量 HABTM 关联的最快方法是什么? [英] What is the fastest way to create mass HABTM associations in Rails?

问题描述

我有两个表，在 Rails 中有一个 HABTM 关系.类似于以下内容:

<前>class Foo < ActiveRecord::Basehas_and_belongs_to_many :bars结尾类 Bar < ActiveRecord::Basehas_and_belongs_to_many :foos结束

现在我有一个新的 Foo 对象，并且想要为它批量分配数千条，我已经预加载了:

<前><代码>@foo = Foo.create@bars = Bar.find_all_by_some_attribute(:a)

最快的方法是什么?我试过了:

<前><代码>@foo.bars = @[e-mail] << @bars

而且两者都运行得很慢，每个bar的条目如下所示:

<块引用>

bars_foos 列(1.1 毫秒)显示FIELDS FROM bars_foos SQL (0.6ms)插入 bars_foos (bar_id,foo_id) VALUES (100, 117200)

我查看了 ar-extensions，但是 import 函数在没有模型 (Model.import) 的情况下似乎无法工作，模型 (Model.import) 阻止了它用于连接表.

我需要编写 SQL，还是 Rails 有更好的方法?

解决方案

我认为性能方面的最佳选择是使用 SQL，并为每个查询批量插入多行.如果您可以构建执行以下操作的 INSERT 语句:

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

您应该能够在单个查询中插入数千行.我没有尝试您的 mass_habtm 方法，但您似乎可以这样做:

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

此外，如果您通过some_attribute"搜索 Bar，请确保您在数据库中对该字段进行了索引.

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

class Foo < ActiveRecord::Base
  has_and_belongs_to_many :bars
end

class Bar < 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 << @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?

解决方案

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.

这篇关于在 Rails 中创建大量 HABTM 关联的最快方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！