Saving multiple objects in a single call in rails

Question

I have a method in rails that is doing something like this:

a = Foo.new("bar")
a.save

b = Foo.new("baz")
b.save

...
x = Foo.new("123", :parent_id => a.id)
x.save

...
z = Foo.new("zxy", :parent_id => b.id)
z.save

The problem is this takes longer and longer the more entities I add. I suspect this is because it has to hit the database for every record. Since they are nested, I know I can't save the children before the parents are saved, but I would like to save all of the parents at once, and then all of the children. It would be nice to do something like:

a = Foo.new("bar")
b = Foo.new("baz")
...
saveall(a,b,...)

x = Foo.new("123", :parent_id => a.id)
...
z = Foo.new("zxy", :parent_id => b.id)
saveall(x,...,z)

That would do it all in only two database hits. Is there an easy way to do this in rails, or am I stuck doing it one at a time?

Harish Shetty · Answer 1 · 2018-08-15T22:50:07.793

109

Since you need to perform multiple inserts, database will be hit multiple times. The delay in your case is because each save is done in different DB transactions. You can reduce the latency by enclosing all your operations in one transaction.

class Foo
  belongs_to  :parent,   :class_name => "Foo"
  has_many    :children, :class_name => "Foo", :foreign_key=> "parent_id"
end

Your save method might look like this:

# build the parent and the children
a = Foo.new(:name => "bar")
a.children.build(:name => "123")

b = Foo.new("baz")
b.children.build(:name => "zxy")

#save parents and their children in one transaction
Foo.transaction do
  a.save!
  b.save!
end

The save call on the parent object saves the child objects.

edited Aug 15 '18 at 22:50

answered Mar 24 '10 at 18:05

Harish Shetty

64,083
21
152
198

4

Just what I was looking for. Speeds up my seeds a lot. Thanks :-) – Renra Jul 01 '14 at 17:42
`Since you need to perform multiple inserts, database will be hit multiple times` NOT true in Rails6, see the answer below(`insert_all`) https://stackoverflow.com/a/59995295/74089 – BinaryButterfly May 13 '22 at 16:39

Roadmaster · Answer 2 · 2010-03-24T16:24:35.230

74

You might try using Foo.create instead of Foo.new. Create "Creates an object (or multiple objects) and saves it to the database, if validations pass. The resulting object is returned whether the object was saved successfully to the database or not."

You can create multiple objects like this:

# Create an Array of new objects
  parents = Foo.create([{ :first_name => 'Jamie' }, { :first_name => 'Jeremy' }])

Then, for each parent, you can also use create to add to its association:

parents.each do |parent|
  parent.children.create (:child_name => 'abc')
end

I recommend reading both the ActiveRecord documentation and the Rails Guides on ActiveRecord query interface and ActiveRecord associations. The latter contains a guide of all the methods a class gains when you declare an association.

edited Mar 24 '10 at 16:24

answered Mar 24 '10 at 16:18

Roadmaster

5,297
1
23
21

87

Unfortunately, ActiveRecord will generate one INSERT query per created model. The OP wants a single INSERT call, which ActiveRecord won't do. – François Beausoleil Mar 24 '10 at 19:48
Yes, I was hoping to get it all in one insert call, but if activerecord isn't that smart, I guess it's not very easy. – captncraig Apr 13 '10 at 00:41
@FrançoisBeausoleil would you mind looking at question http://stackoverflow.com/questions/15386450/debugging-why-a-create-method-is-not-saving-to-the-model, would this be why I cannot insert multiple records at the same time? – Richlewis Mar 13 '13 at 17:07
3

It's true you can't get AR to generate one INSERT or UPDATE, but with `ActiveRecord::Base.transaction { records.each(&:save) }` or similar you can at least put all the INSERTs or UPDATEs into a single transaction. – yuval Sep 01 '16 at 01:01
I am outside my edit window, but re: my own comment above: Beware of the table locking during the large transaction. – yuval Sep 01 '16 at 01:20
1

Actually, the OP wants to hit the database less, to speed up the DB access, and ActiveRecord actually let's you do that, by batching up all the calls in one transaction. (See Harish's answer, which ought to be the accepted answer.) What ActiveRecord won't let you do is make the DB create one INSERT query per transaction, but that doesn't matter that much, since the latency comes from doing the network access to the DB, and not inside the DB itself when it does the INSERT queries. – Magne Dec 16 '16 at 14:25
Or you could try https://github.com/zdennis/activerecord-import like Nguyen Chen Cong suggested, which will actually create one single INSERT query for the DB too. – Magne Dec 16 '16 at 14:33

Marian13 · Accepted Answer · 2021-03-31T21:39:35.053

43

insert_all (Rails 6+)

Rails 6 introduced a new method insert_all, which inserts multiple records into the database in a single SQL INSERT statement.

Also, this method does not instantiate any models and does not call Active Record callbacks or validations.

So,

Foo.insert_all([
  { first_name: 'Jamie' },
  { first_name: 'Jeremy' }
])

it is significantly more efficient than

Foo.create([
  { first_name: 'Jamie' },
  { first_name: 'Jeremy' }
])

if all you want to do is to insert new records.

edited Mar 31 '21 at 21:39

answered Jan 30 '20 at 22:13

Marian13

7,740
2
47
51

2

I can't wait until we update our app. So many cool things in Rails 6. – Dan Jun 02 '20 at 20:34
4

one thing to note is: insert_all skips the AR callbacks & validations: https://edgeguides.rubyonrails.org/active_record_callbacks.html#skipping-callbacks – sujay Sep 02 '20 at 00:16
2

if you want to validate entity, check `insert_all!` out. – Jin Lim Dec 20 '21 at 17:48
@JinLim Not what the ! does. – Nobody Mar 10 '23 at 16:29

score 11 · Answer 4 · edited May 23 '17 at 11:47

11

One of the two answers found somewhere else: by Beerlington. Those two are your best bet for performance

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.

OR

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.


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

Cheers

edited May 23 '17 at 11:47

Community

1
1

answered Mar 12 '12 at 05:07

Canopus

1,527
1
14
14

That works great for inserting, but what about for updating multiple records in one transaction? – Avishai Sep 23 '13 at 19:20
2

For updating you should use upsert: https://github.com/seamusabshere/upsert. cheers – Canopus Sep 27 '13 at 17:31
Very poor idea with sql query. You should use ActiveRecord and transaction. – Kerozu Jun 15 '14 at 22:17
It's not a bad idea. If you are doing ONE insert, it will either succeed or fail, no need for transaction, I guess. Or you can always wrap that ONE insert in a transaction block. – Fernando Fabreti Aug 21 '14 at 11:11
this is bad rails practice – Blair Anderson Nov 18 '16 at 17:38

score 0 · Answer 5 · answered Jan 25 '19 at 18:44

0

you need to use this gem "FastInserter" -> https://github.com/joinhandshake/fast_inserter

and inserting a large number and thousands of records is fast because this gem skips active record, and only uses a single sql raw query

answered Jan 25 '19 at 18:44

val caro

7
1
2

1

Although the link to the gem may be useful, please provide some code that the Asker could use instead of their current code (see question). – trincot Jan 25 '19 at 19:09
here: https://stackoverflow.com/questions/19081129/how-would-i-save-multiple-records-at-once-in-rails/54370929#54370929 – val caro Jan 27 '19 at 07:09
1

Answers need to have the essential information *embedded*. Please edit your answer and add the link there, and also add the essential parts of it inside the answer, so that it is self-contained. – trincot Jan 27 '19 at 10:03

Fernando Fabreti · Answer 6 · 2015-04-27T20:55:25.930

-3

You don't need a gem to hit DB fast and only once!

Jackrg has worked it out for us: https://gist.github.com/jackrg/76ade1724bd816292e4e

edited Apr 27 '15 at 20:55

answered Aug 21 '14 at 11:13

Fernando Fabreti

4,277
3
32
33

Any solution like this for Mongodb? – Breno Mar 19 '16 at 20:17

Saving multiple objects in a single call in rails

6 Answers6

insert_all (Rails 6+)

OR

Linked