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?

Syphilology

Answer 1

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.

Unlearned

I can't wait until we update our app. So many cool things in Rails 6. – Jacynth

one thing to note is: insert_all skips the AR callbacks & validations: edgeguides.rubyonrails.org/… – Unreel

if you want to validate entity, check insert_all! out. – Pipette

@JinLim Not what the ! does. – Maitland

Answer 2

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.

Spectacled

Just what I was looking for. Speeds up my seeds a lot. Thanks :-) – Staw

Since you need to perform multiple inserts, database will be hit multiple times NOT true in Rails6, see the answer below(insert_all) https://mcmap.net/q/193476/-saving-multiple-objects-in-a-single-call-in-rails – Chiromancy

Answer 3

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.

Vitiate

Unfortunately, ActiveRecord will generate one INSERT query per created model. The OP wants a single INSERT call, which ActiveRecord won't do. – Squires

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. – Syphilology

@FrançoisBeausoleil would you mind looking at question #15386950, would this be why I cannot insert multiple records at the same time? – Aneurysm

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. – Cruel

I am outside my edit window, but re: my own comment above: Beware of the table locking during the large transaction. – Cruel

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. – Megaspore

Or you could try github.com/zdennis/activerecord-import like Nguyen Chen Cong suggested, which will actually create one single INSERT query for the DB too. – Megaspore

Answer 4

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

Bedesman

That works great for inserting, but what about for updating multiple records in one transaction? – Lugworm

For updating you should use upsert: github.com/seamusabshere/upsert. cheers – Bedesman

Very poor idea with sql query. You should use ActiveRecord and transaction. – Canal

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. – Raki

this is bad rails practice – Pepita

Answer 5

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

Vitellus

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). – Quitt

here: #19081629 – Vitellus

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. – Quitt

Answer 6

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

Raki

Any solution like this for Mongodb? – Nel