In theory, configuring eager loading to avoid N+1 issues is straightforward. Chaining
includes method is usually enough to ensure that all the relations data is fetched efficiently. However, this rule breaks for some easy-to-overlook edge cases. In this blog post, I’ll describe how to eager load ActiveRecord relations using custom SQL ORDER BY sorting.
How to use eager loading with ORDER BY in SQL?
Based on my experience conducting Rails performance audits, N+1 issues are a primary cause of sub-optimal response times in Ruby on Rails apps. ActiveRecord makes it just too easy to sneak in an N+1 bug in production. It’s easy to miss it in development because you usually work with small datasets.
Let’s analyze a sample scenario of displaying the products of a user:
In theory, this implementation should correctly eager-load all the product objects and prevent N+1 queries.
But let’s consider the following view layer implementation:
The addition of
by_rating scope effectively breaks the eager loading. Adding a
where call would have the same effect. It is caused by chaining methods that alter the relation SQL and invalidate the previously eager loaded collection. It’s even worse than not using eager loading at all.
includes still triggers additional queries, but results are discarded.
A naive approach to avoiding N+1 issues in similar situations could be to work with methods that do not alter the relation SQL. It means that instead of adding
by_rating scope, you’d need to use a method that does the sorting in Ruby:
Similarly, instead of
where, you could use the
select method. It filters collection in Ruby instead of an SQL
Using custom relation to prevent N+1 queries
However, there are downsides to using Ruby instead of SQL to transform relation objects. For example, sorting in Ruby can be slower than SQL
ORDER BY or even impossible for larger data sizes. And using
select to filter the collection means that you’ll discard already initialized ActiveRecord objects, so memory is wasted.
A better way to work with relations that need their SQL customized is to use a dedicated relation method. Let’s see it in action:
This approach eliminates the additional N+1 queries while preserving custom order without a need for sorting in Ruby.
For non-trivial views, it could be challenging to prevent N+1 issues from emerging accidentally. For example, even custom relations could still trigger N+1 queries if their SQL is further customized. One way to avoid it could be to explicitly call
to_a on relations collections before using them. As an effect,
ActiveRecord::Relation is transformed into an
Array of objects, making it impossible to chain SQL calls.
But it’s arguably not the cleanest solution.
Unfortunatelly, even using the recently added
strict_loading on a relation would not detect similar mistakes:
You should continuously monitor the number of queries executed by your application. I always recommend using rack-mini-profiler and regularly inspecting SQL queries triggered by each view. This 10-year-old Railscast is still up to date, so the tool did stand the test of time. Getting rid of sneaky N+1 issues could be the lowest-hanging fruit in optimizing your Rails app’s performance.