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:
- PostgreSQL performance considerations
- PostgreSQL’s documentation on aggregate functions
- PostgreSQL’s available aggregate functions
For some more information about Ruby’s Enumerable class and
Enumerable#inject in particular, check out:
