Articles.

Reads.


Understanding and fixing N+1 query in Ruby on Rails


Regarding backend performance, there is a performance issue that everybody has heard about at least once: N+1 query.

An N+1 query problem happens when your code executes N additional query statements to fetch the same data that could have been retrieved when executing the primary query. When it comes to understanding and fixing N+1 queries in the context of Ruby on Rails, it refers to the process of identifying and resolving a common performance issue in database queries

Let's start with an analogy first

Imagine you're preparing a meal and forget the buy all the ingredients. Now you have to go back to the groceries store to get them and everytime you forget one you have to make repeated trips ther (multiple feching). this is similar to the N+1 query problem in programming. When using an ORM (Object-Relational Mapping) framework, your code may inadvertently cause the ORM to execute N additional queries after the initial query, resulting in inefficient database access.

This issue can be addressed by using techniques such as eager loading, which allows related data to be loaded in advance, reducing the number of database queries. Additionally, caching and optimizing database indexes can also help improve query performance. Tools like ActiveRecord's includes method can preload associations and minimize the number of database queries. you can also use tools like Bullet gem to detect and address N+1 query issues.

Let's say we have a blog application with two models: Post and Comment. Each post has multiple comments associated with it. Now, suppose we want to display a list of posts along with the number of comments for each post.

Examples of N+1 Query

Bad Implementation:

bad implementation

In this example, for each post, a separate query is made to fetch the comments associated with that post. This leads to N+1 queries, where N is the number of posts. As the number of posts grows, the number of database queries also grows, leading to performance issues.

Correct Implementation:

bad implementation

In the good implementation, we use the includes method to eager load the comments associated with each post. This results in a single query to fetch all the posts and their associated comments, improving performance by reducing the number of database queries.

By using the includes method, we can optimize the database access and avoid N+1 query issues in our Ruby on Rails application.