DEV Community

Yago Santos
Yago Santos

Posted on • Updated on

Yet Another Post About N + 1 Queries

The N + 1 query problem is a common performance bottleneck found in queries that are written using an ORM (Object-Relational Mapping) tool, such as ActiveRecord.

The problem arises when N queries have to be executed, for each association, in addition to the first one made to fetch all the associated records.

For instance, if we had the following relationship between User and Appointment models

# app/models/user.rb
class User < ApplicationRecord
 has_many :appointments
end
Enter fullscreen mode Exit fullscreen mode
# app/models/appointment.rb
class Appointment < ApplicationRecord
 belongs_to :user
end
Enter fullscreen mode Exit fullscreen mode

and executed the query below in our code

Appointment.all.limit(5).each do |appointment|
 puts "#{appointment.user.name} has an appointment at #{appointment.date}"
end
Enter fullscreen mode Exit fullscreen mode

this is what we would see in our rails console:

Appointment Load (0.5ms)  SELECT "appointments".* FROM "appointments" LIMIT ?  [["LIMIT", 5]]
 User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
 User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 3], ["LIMIT", 1]]
 User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 5], ["LIMIT", 1]]
 User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 7], ["LIMIT", 1]]
 User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 9], ["LIMIT", 1]]
Enter fullscreen mode Exit fullscreen mode

The query above seems to be harmless, but imagine we had to load 1,000 appointments! We'd need to hit the database 1 time (to grab all appointments) plus 1,000 times (to grab each appointment's user), totalling 1,001 queries. In technical terms, the results are being lazily loaded, meaning that the "N" consecutive queries will be made as they are requested in appointment.user.name.

How to Reduce the Number of Independent Database Queries?

A possible solution for this is to use .includes, which will eager load the query result, meaning that the related associations (parent and children) will be loaded all at once through only a few queries.

For example, if we run the following code in the console

Appointment.includes(:user).limit(5).each do |appointment|
 puts "#{appointment.user.name} has an appointment at #{appointment.date}"
end
Enter fullscreen mode Exit fullscreen mode

this is what we would get back:

Appointment Load (0.2ms)  SELECT "appointments".* FROM "appointments" LIMIT ?  [["LIMIT", 5]]
 User Load (0.3ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)  [["id", 1], ["id", 3], ["id", 5], ["id", 7], ["id", 9]]
Enter fullscreen mode Exit fullscreen mode

As we can see from the result above, only 2 queries are being ran against the database, instead of 6 when the results were being lazily loaded.

Backing it Up with Data!

At this point you may be wondering "Okay, enough talking! Where is the data showing that using .includes actually improves query performance?".

When attempting to lazy load 100 users' appointments, we get the results below in miliseconds:

user     system      total        real
0.101984   0.096377   0.198361 (  0.761556)
Enter fullscreen mode Exit fullscreen mode

On the other hand, when we try eager loading the same 100 records, we get faster execution times:

user     system      total        real
0.026250   0.010747   0.036997 (  0.078877)
Enter fullscreen mode Exit fullscreen mode

If we take in consideration the total time, which is a sum of the time spent executing the code (user) plus the time spent in the kernel (system), eager loading the records is 5.36 times faster!

You can read more about Ruby's benchmark module here.

How to Identify N + 1 Queries in an Application

In order to find all those N + 1 queries that are slowing down in your application, the community recommends using the Bullet gem.

References

Top comments (0)