In Ruby on Rails, N+1 query problems occur when your application makes an excessive number of database queries due to how associations between models are loaded. This can lead to severe performance degradation, especially as your data scales.
In this post, we’ll cover four effective ways to resolve the N+1 query problem in Rails and make your application faster and more efficient.
1. Use includes for Eager Loading
The most common solution to N+1 problems is to use includes. This method allows you to load associated records in a single query, rather than querying the database each time you access an association.
Without includes (N+1 query problem):
1
2
3
4
5
6
posts = Post.all
posts.each do |post|
post.comments.each do |comment|
puts comment.body
end
end
In this example, for each post, Rails will execute a separate query to fetch the associated comments. This can quickly result in dozens or even hundreds of queries as the number of posts grows.
With includes (solving N+1):
1
2
3
4
5
6
posts = Post.includes(:comments).all
posts.each do |post|
post.comments.each do |comment|
puts comment.body
end
end
By using includes(:comments), Rails will load the posts and their associated comments in a single query, avoiding the N+1 issue.
2. Use joins for Query Efficiency
If you don’t need to load the associated records into memory and only need them for filtering or conditions, using joins is a more efficient solution. While includes will load the associated records, joins uses SQL to combine the data without fetching all the related records.
Example:
1
Post.joins(:comments).where(comments: { approved: true })
In this case, joins creates an SQL INNER JOIN between the posts and comments tables, allowing you to filter posts that have approved comments, without fetching all the comment data into memory.
3. Consider preload or eager_load in Specific Cases
Both preload and > eager_load are variations of eager loading in Rails, but they work slightly differently from includes.
preload: Loads associated records in separate queries, rather than using a join. This can be beneficial when you don’t want to deal with joins but still need to avoid N+1.eager_load: Forces Rails to use aLEFT OUTER JOINto load both the primary and associated records in a single query.
When to use them:
- Use
preloadif you’re dealing with a large dataset and want to avoid complex joins. - Use
eager_loadwhen you want to ensure all associations are loaded in a single query.
Example:
1
2
Post.preload(:comments).all # Separate queries for posts and comments
Post.eager_load(:comments).all # Single query with LEFT OUTER JOIN
4. Select Only the Necessary Data
Another way to optimize your queries is by selecting only the columns you need from the database. By default, Rails loads all columns from the associated tables, but often you don’t need every single column.
You can use select to load only the relevant data:
1
2
3
4
posts = Post.select(:id, :title).includes(:comments).all
posts.each do |post|
puts post.title
end
In this case, instead of loading all the columns from the posts table, we are only selecting the id and title columns, reducing the amount of data being fetched from the database.