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
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
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
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.
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: