Run a rails query in batches

Question

I have a table A(:name, :address, :phone) consisting of 500,000 entries. I want to run this query :

johns = A.where(:name => "John")

This query should return 150,000 results. But running this query gives me this result : Killed.

How should I rewrite this query so that the query runs on batches of 1000 in the database?

Answer 1

You need to use find_each with the option batch_size.

A.where(:name => "John").find_each(batch_size: 1000) do |a|
  # your code
end

Answer 2

An alternative to using find_each is to use find_in_batches.

There's a distinct difference - find_each will give your block each item and will loop through your batch item by item. find_in_batches will deliver your batch of items in an array to your block.

I've assumed your A model is actually called Address. You could do something like this:

Address.where(name: "John").find_in_batches(batch_size: 1000) do |addresses|

  # Your code that you might want to run BEFORE processing each batch ...

  addresses.each do |address|
    # Your code that you want to run for each address
  end

  # Your code that you might want to run AFTER processing each batch ...

end

As you can see, this gives you a little more flexibility around how you handle the processing of your batches. However, if your needs are simple, just stick with find_each.

Answer 3

.in_batches

The issue with find_each or find_in_batches is that you have consumed query results.

The cleanest solution is using in_batches, because it yield the actual query (without consuming it):

User.find_in_batches do |users|
  users.select(:id) # error
end

User.in_batches do |users|
  users.select(:id)                   # works as expected
  users.pluck("complext pluck query") # works as expected
end

Answer 4

 A.where(:name => "John").find_each(batch_size: 1000) do |a|
    # your code
 end