Refactoring Ruby Iteration Patterns to the Database

Simon Taranto

Frequently on projects we need to run a calculation through an ActiveRecord association. For example, we might want to get a user’s all time purchases, a company’s total amount of products sold, or in our case, the total amount of loans made to a campaign. This sort of calculation is ripe for using a map and an inject but frequently the solution is more elegant and faster if we can have SQL do the work instead.

Here is what our domain looks like.

class Campaign < ActiveRecord::Base
  has_many :loans
end

class Loan < ActiveRecord::Base
  belongs_to :campaign
end

The goal

We want to be able to ask a campaign how much money it has raised. An initial implementation might look like this.

class Campaign < ActiveRecord::Base
  # ...
  def funds_raised
    loans.map(&:amount).inject(:+)
  end
end

We are using the association to get the campaign’s loans and then summing up the amount of each loan. If we look at our logs we’ll see that the SQL generated is grabbing all of the loans for our campaign and pulling them into memory.

SELECT "loans".* FROM "loans" WHERE "loans"."campaign_id" = <target_campaign_id>

Then in Ruby (at the application layer), we run the map to get the amounts and the inject to get the sum. Done. Not that bad but we can do better.

Using SQL sum

We want to avoid pulling all this data into memory just to get a single number. Let’s have the database do the heavy lifting.

class Campaign < ActiveRecord::Base
  # ...
  def funds_raised
    loans.sum(:amount)
  end
end

With this implementation we have the database do the calculation. We get access to sum from ActiveRecord. Check out the docs for more details.

Looking at the logs we can see the followingSQL.

SELECT SUM("loans"."amount") AS sum_id FROM "loans" WHERE "loans"."campaign_id" = <target_campaign_id>

This SQL is going to give us the same answer but in a single step. Through this refactoring we have code that is easier to read, we use less application memory, and we see a performance boost because we’re pushing the calculation down to the database. Based on benchmarks where each campaign has 1000 loans, the sum approach can be more than 20x faster than using map / inject.

Next time we reach for a map and inject on associated ActiveRecord objects, we’ll check to see if there is a way to have the database do the work instead.

What’s next

If you would like some more background on additional SQL aggregate functions check out the following articles:

For some more information about Ruby’s Enumerable class and Enumerable#inject in particular, check out: