Rails N+1 queries and eager loading
Tags: rails, sql, beginners, performance
Published: 2020-12-26 03:27:43

![database search](https://dev-to-uploads.s3.amazonaws.com/i/z2bgvvg2oh9536j7q2tb.png)

##What is the N+1 query problem?##

The N+1 query problem is one of the common performance antipatterns in ORMs. It happens when a query is executed on each result of the previous query, in other words, when an application gets data from the database and then loop through the result of that data.

This works just fine if your application is small enough and there are only a small number of requests and queries, but if you want to keep your application scalable, you have to eliminate the N+1 problem from your application.
***

Here's an example with Rails:

```ruby
# app/models/post.rb
class Post < ApplicationRecord
  belongs_to :user
end
```
```ruby
# app/models/user.rb
class User < ApplicationRecord
  has_many :posts
end
```
Let's print the `title` and `user`'s `username` for all the `posts`. 
```ruby
Post.all.each do |post|
  puts "#{post.title} was written by #{post.user.username}"
end
```
ActiveRecord executed the queries below:
```sql
Post Load (0.4ms)  SELECT "posts".* FROM "posts"
  User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  User Load (0.3ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 4], ["LIMIT", 1]]
  User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 4], ["LIMIT", 1]]
  User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 4], ["LIMIT", 1]]
  User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 4], ["LIMIT", 1]]
```
It first retrieved all the `Post` objects and then `user` for each `post`. There are 6 `posts` in the database in this example, so 7 queries in total were executed.

What if there are 10,000 posts in the database? The number of database connections is limited and you might hit query timeout.

##Solve the N+1 problem with "eager loading"##

As opposed to lazy loading, eager loading is the process whereby a query loads a resource as soon as the code is executed. It also loads related entities as part of the query.

In Rails, you can use `includes` method for eager loading.

The [`includes`](https://apidock.com/rails/ActiveRecord/QueryMethods/includes) method specifies relationships to be included in the result set.

Let's rewrite the example using the `includes` method:

```ruby
Post.includes(:user).each do |post|
  puts "#{post.title} was written by #{post.user.username}"
end
```

```sql
Post Load (103.7ms)  SELECT "posts".* FROM "posts"
  User Load (32.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (?, ?, ?, ?, ?)  [["id", 2], ["id", 4]]
```

Now we got the same output by only 2 queries.

What happened is `Post.includes(:user)` told ActiveRecord to retrieve the corresponding `user` records from the database immediately after the initial request for all `posts`. Since the records of `users` were already in the memory, `post.user.username` could be retrieved by only one query. Now, even if we have 10,000 posts in our database, we can execute the example code above by just 2 queries! This is a huge difference.

##Find the N+1 query problem with the Bullet Gem##
Even if you review your code carefully trying to eliminate N+1 queries from your application, errors can slip into production code. The [Bullet Gem](https://github.com/flyerhzm/bullet) is a great tool to automate the process.

> The Bullet gem is designed to help you increase your application's performance by reducing the number of queries it makes.
>It will watch your queries while you develop your application and notify you when you should add eager loading (N+1 queries), when you're using eager loading that isn't necessary and when you should use counter cache.
>- Bullet Gem README https://github.com/flyerhzm/bullet

***
As your application gets bigger, you need to pay more attention to its speed and performance. Eager loading in Rails is really easy to implement but makes a big difference.