When first learning Rails, people tend to lean pretty heavily on Ruby’s Enumerable methods to form the queries they need. This is okay when you’re just starting and arguably if you’re just trying to get a small project out the door. But, eventually you’ll find yourself in a situation where response time matters and getting good at optimizing queries becomes really important.
I plan to write 4 posts on this topic but we can start by looking at how we can optimize Rails’ belongs_to association queries.
Commonly, in Rails projects you’ll have a domain model as such:
Customers belonging to a free tier
The client wants a way to e-mail all of the customers belonging to a free tier (to send them an invite to upgrade their account, for example). Easily enough, this is achieved with the following:
So, we’re done, let’s push to production? Well not so fast. Let’s analyze this query a little bit in Rails console to see what it’s really doing to our database.
Looks like we have a classic N+1 query — for every Customer, we’re making a separate request to the Tiers table. Now imagine how inefficient this would be for a mid-sized project with thousands or millions of Customers.
What’s worse is that we don’t even need actual data from the Tiers table as our final result should just be a list of Customers. With each query, our database is sending back every one of these Tiers to the application where it is then unnecessarily built out into a full-fledged ActiveRecord object.
What SQL query do we really want anyway?
If you’re familiar with SQL at all, sometimes it’s easier to write up the optimized query that you want and to back your way into an ActiveRecord query (or not if it’s a really complex query). In our case the SQL query we want is:
The above query should give us everything we need in one database call. The main players are the JOIN and WHERE statements. Fortunately, ActiveRecord has convenience methods for these SQL statements. So, lets break this down one by one starting with the join clause.
That was easy. If we check this out on the console we should more or less get something like:
Great, so the next step is to simply implement the WHERE clause:
And, if we plug this into Rails console we’ll see that we’re right back to the SQL query we were aiming for.
Refactor with `#merge`
At this point it would probably be OK if we commit this code and push it up. But, we can make it just a little bit better by separating concerns between what a Customer and Tier should be doing. Let’s start by ensuring that a Tier is solely responsible for knowing whether or not it is free or paid:
Now, in the Customer model we can simply tell Tier what to do and not ask it about itself. The last step would be to use `#merge` to combine the results of `Tier.freemium` with our Customers table. Our complete solution is now:
And as a last check, we can try this out in the console one more time to ensure we’re getting the same SQL query from before — which it does!
Stay tuned – this article is part 1 of 4 in a series on Optimizing ActiveRecord:
Part 1: Querying “belongs_to” associations
Part 2: Querying “one-to-many” associations
Part 3: Using raw SQL for custom joins
Part 4: Using aggregations in ActiveRecord