Does ActiveRecord perform inserts/deletes in bulk when inside a transaction?

Question

What I need:

1. ensuring atomic updates (no record can gets processed 2 times)
2. bulk deletion for all 1000 rows selected

---

@queue = Queue.where("col = 1").limit(1000)
ids = []
@queue.each do |row|
    Queue.do_something(row)
    ids << row.id
end

Queue.delete_all("id in (#{ids.join(',')}) ")

IS THE SAME AS

Queue.transaction do
    @queue.each do |row|
        Queue.do_something(row)
        Queue.delete(row.id)
    end
end

Answer 1

For inserts:

ActiveRecord does not perform a bulk insert when using a transaction. However it does speed things up a bit since it is using a single transaction to execute all INSERT statements as opposed to one transaction per INSERT statement otherwise.

So:

Queue.transaction do 
  @queue.each do |row|
    # an INSERT is done here
  end
end

is going to be faster than:

@queue.each do |row|
  # an INSERT is done here
end

For more info on how to really do bulk inserts, check out this article.

For deletes:

The ActiveRecord delete_all call is one single SQL DELETE statement, so I guess you could consider this as a bulk delete (no need to use a transaction here since it's already encapsulated in one transaction by ActiveRecord). This is not the case when calling delete on each record, which will result in multiple SQL DELETE statements, thus multiple transactions initiated and committed and overall slower performance.

Answer 2

I suggest you take a look at ActiveRecord Import: https://github.com/zdennis/activerecord-import.

I'm using this tool to insert between 10,000 and 100,000 rows of data.

books = []
10.times do |i| 
  books << Book.new(:name => "book #{i}")
end
Book.import books

If you're using MySQL, it also supports ON DUPLICATE KEY UPDATE so you can intelligently insert new / update old rows. https://github.com/zdennis/activerecord-import/wiki/MySQL:-On-Duplicate-Key-Update-Support