Efficient INSERTS


#1

Hi,

I’ve got the following models:

Company
Package

with a habtm relationship.

In my controller, I have a ‘dispatch’ action which does the following

@companies.each do |company|
@packages.each do |package|
company.packages.push_with_attributes(package, :sent_on => Time.now)
end
end

my table ‘companies_packages’ therefore lists which company was sent
which
package at what time.

If I’m sending 20 packages to 20 companies though, I generate 400 INSERT
statements. Is there a rails way of doing this more efficiently?

i.e. generating an SQL statement like

INSERT INTO companies_packages (company_id, package_id, ‘sent_on’)
VALUES
(1, 101, 2005-12-15),
(2, 102, 2005-12-15),
(3, 103, 2005-12-15),
(4, 104, 2005-12-15),
(5, 105, 2005-12-15),
(6, 106, 2005-12-15),
(7, 107, 2005-12-15),
(8, 108, 2005-12-15),
(9, 109, 2005-12-15),
(10, 110, 2005-12-15);

Thanks in advance

Adam


#2

On Dec 15, 2005, at 9:42 AM, Adam G. wrote:

If I’m sending 20 packages to 20 companies though, I generate 400
(4, 104, 2005-12-15),
(5, 105, 2005-12-15),
(6, 106, 2005-12-15),
(7, 107, 2005-12-15),
(8, 108, 2005-12-15),
(9, 109, 2005-12-15),
(10, 110, 2005-12-15);

Thanks in advance

Adam

I’ve never seen a rails shortcut for that sort of thing. It’s
delving in to the realm of optimization and customization where you
get to tweak SQL queries however you please :wink:

Duane J.
(canadaduane)


#3

On Thursday 15 December 2005 20:16, Duane J. wrote:

In my controller, I have a ‘dispatch’ action which does the following

(3, 103, 2005-12-15),
Adam
http://lists.rubyonrails.org/mailman/listinfo/rails
One thing that might help (I’m sure it will with PostgreSQL) is to wrap
your
loops in a transaction

ActiveRecord::Base.transaction do

@companies.each do |company|
@packages.each do |package|
company.packages.push_with_attributes(package, :sent_on =>
Time.now)
end
end
end

Otherwise, each insert will be placed in its own transaction.

Luca