I’m not sure if the world needed another post about N+1 queries in Ruby on Rails. To make up for the cliche topic, I’ll describe less common solutions to this problem. Read on if you want to learn how to reduce the number of cascading N+1 SQL queries without using
includes or additional table join operations.
N+1 queries 101
N+1 queries are the top performance killer for Rails applications. ActiveRecord makes it just too easy to misuse your database structure. Consider the following code:
With the above implementation, you’ll see the following SQL queries in your logs:
User Load SQL query is generated for every single loaded post. Since ActiveRecord needs additional data from another table to render the page, it will implicitly run those queries. If you’re loading dozens of objects, a single request could generate hundreds of SQL queries overwhelming your database and killing the performance.
To fix it, you’ll have to change your controller’s code to use so-called eager loading:
As a result, you’ll see the following SQL logs:
You’ll notice that regardless of the number of objects, only two queries are now generated. One fetches all the required posts, the other its users.
There are more methods available for preloading the ActiveRecord objects. You can check out this blog post for more details on the differences between
Track down N+1 issues in production
Now that we know what N+1 queries are let’s discuss how to identify those worth fixing. Ideally, you should track every single unnecessary query and optimize it. For rarely used endpoints, it’s not always worth your time and effort.
I usually start the performance-related research with ScoutAPM. They offer a handy overview of N+1 issues that have an actual impact on the app’s performance:
Make sure to enable ScoutAPM auto instrumentation by setting the following
With this setting in place, you can track the origin of your N+1 queries down to the single code line that’s responsible.
Alternatively, you can use the popular Bullet gem. One downside is that it could overwhelm you with data about all the endpoints affected by the N+1 issue. You’ll have to cherry-pick the bottlenecks that are actually worth fixing.
Now that we’ve covered the basics, let’s discuss the promised non-standard solutions to the N+1 problem.
Cache and reuse duplicate queries
Spotting reusable query results is invaluable in optimizing the performance of your app. Consider the app with the following structure:
Posts have many
Comments that belong to
Users. Let’s assume that we have a page where we want to display users’ data and posts with comments.
The above code is already eager loading all the required data and will generate four queries. One to load posts, one for comments, and two to load users’ data.
Assuming that users’ data loaded by two queries is identical, we could rewrite the above example in the following way:
You render comments’ user data from an instance variable
@users_cache generated from data fetched by a single query. This approach will cut an unnecessary
Users query while still avoiding the N+1 issue.
Make sure always to measure the results of similar refactorings in production environment. In this particular example, we’ve removed an additional query. However, it might turn out that constructing the
@cached_users hash could be slower than fetching the duplicate data using SQL.
I understand that the above example might seem a bit convoluted. However, in mature Rails apps, it’s not uncommon to fetch different nested models with interdependent relations to render a more complex HTML view.
You can check out this blog post for more info on how to speed up ActiveRecord performance by caching subqueries.
Use primitive values instead of objects
Let’s consider the following scope method:
Can you spot what’s wrong?
The problem is that you cannot use this method without executing an additional query. You’re passing a full-blown ActiveRecord
Post object that must instantiate another
User object from its relation.
To minimize the number of unnecessary queries, you could rewrite this method like that:
Now you’re passing primitive values. Fewer queries and less memory are needed to execute this method. Designing your public APIs on low-level objects will make them simpler to use and potentially more performant.
Add shortcut relationships
The Law of Demeter is not always strictly followed in legacy Rails apps. It’s not uncommon to encounter code like that:
Executing this method will generate three additional SQL queries to fetch and instantiate required objects. These kinds of methods are the worse. Rendering even a dozen of objects could potentially overflow your database with hundreds of queries.
A potential solution to this issue is to add a shortcut to your database structure to account for data access requirements. If your
Post model necessarily needs to access data from
Account and additional queries are killing the performance, you can consider creating a direct connection between models. It’s kind of a dirty trick, but it could help you speed things up significantly for bottleneck endpoints.
With simplified relationships, the above example could now look like that:
Watch out, since overusing this approach can quickly lead to your database structure becoming an unmaintainable mess.
Duplicate relationship data
An even more extreme variant of the above technique is duplicating data and putting it directly in a model that needs it. The upside is that it allows you to eliminate any additional queries completely. In this approach
Post model itself is a caching layer for the
Account model attribute.
However, it comes with a separate range of challenges for syncing data between models. You’ll have to update the
Post object whenever his
Account changes. I’d only resort to this technique for some extreme bottleneck endpoints for which reducing an additional query will make a difference.
includes to your database call is usually the most straightforward and best choice for common N+1 issues. However, for some more rare and complex cases, these other techniques could be worth considering.