Speed up ActiveRecord insert for a given loop

Question

The Setup

rails new xyz
cd xyz
rails g scaffold Donor name
rails g scaffold Recipient name
rails g scaffold Donation amount:integer donor:references recipient:references
rails g scaffold Search query
rails g model SearchResult search:references donation:references

Rails 5.2, Ruby 2.5.1 and Postgresql.

The Problem

We are talking about a big data set of a couple of million entries, and would like to optimize the code below which creates tens of thousands of entries in SearchResult. It takes more than 10 seconds to insert it. Is there a way to optimize the following code so that it is faster?

search = Search.new(query: "Smith")    
Donation.joins(:donor).
         where("donors.name like ?", "%#{search.query}%").each do |donation|
  search.search_results.build(donation: donation)
end
Donation.joins(:recipient).
         where("recipients.name like ?", "%#{search.query}%").each do |donation|
  search.search_results.build(donation: donation)
end
search.save

I'm not a big fan of using RAW SQL in Rails, but if there is a way of solving this in pure SQL which would be faster, than that could be possibility too.

Answer 1

As @matthewd pointed, building associated records and saving the parent actually works There probably is an issue with the code you propose. Indeed, the build method of active record will not persist the search results as you wish as you can see here : http://guides.rubyonrails.org/association_basics.html#methods-added-by-has-many-collection-build-attributes

An correct alternate way to persist it would be :

search = Search.new(query: "Smith")    
Donation.joins(:donor).
         where("donors.name like ?", "%#{search.query}%").each do |donation|
  search.search_results.create(donation: donation)
end
Donation.joins(:recipient).
         where("recipients.name like ?", "%#{search.query}%").each do |donation|
  search.search_results.create(donation: donation)
end
search.safe

Of course, as you noted, it is not efficient at all and there are 2 ways to solve this. With a cool gem called https://github.com/zdennis/activerecord-import or by hand

By hand

This is not the recommended way but I put it here for your information. This is the SQL query you could use :

query = <<-SQL
INSERT INTO search_results (search_id, donation_id)
SELECT :search_id, id
FROM donations
INNER JOIN donor AS donor.id = donation.donor_id
WHERE donors.name LIKE :query
SQL

You could use ActiveRecord::Base.connection.execute method to launch that but that also means you need to sanitize the query yourself. I could go further on that path but let's dive to another solution which I think is safer and easier to maintain.

With active record import

https://github.com/zdennis/activerecord-import

You could use this code

search = Search.create(query: 'Smith')
results = Donation.joins(:donor)
                  .where('donors.name like ?', "%#{search.query}%")
                  .find_each.map do |donation|
  search.search_results.new(donation: donation)
end
results += Donation.joins(:recipient)
                   .where('recipients.name like ?', "%#{search.query}%")
                   .find_each.map do |donation|
  search.search_results.new(donation: donation)
end
SearchResult.import results

Notice several important things :

• I used create at the beginning, so that the search is persisted and the search result reference it correctly
• I used find_each instead of each which finds record by batch and is usually more efficient when iterating on a large number of records, you can specify a batch size as an option of the method.
• I built an array with non persisted object of all the search results, please note that if there are a lot of donations concerned, this would use memory
• There is no uniq filter on the results, I did not know if it was an intended behaviour but please note that you are probably saving duplicates results.

Hope this will be useful !