N+1 Query

What is it?

• performance issues as it results in multiple database queries

• Usually solved by eager loading

Example

Let's say you have a collection of Car objects (database rows), and each Car has a collection of Wheel objects (also rows). In other words, Car → Wheel is a 1-to-many relationship.

Now, let's say you need to iterate through all the cars, and for each one, print out a list of the wheels. The naive O/R implementation would do the following:

SELECT * FROM Cars;

And then for each Car:

SELECT * FROM Wheel WHERE CarId = ?

In other words, you have one select for the Cars, and then N additional selects, where N is the total number of cars.

Alternatively, one could get all wheels and perform the lookups in memory:

SELECT * FROM Wheel;

This reduces the number of round-trips to the database from N+1 to 2. Most ORM tools give you several ways to prevent N+1 selects.

Reference: Java Persistence with Hibernate, chapter 13.

How to prevent it?

Eager Load

• Load everything asked

• Ex: the example above uses eager loading by querying every wheels into the memory first

Lazy Load

• Load partially and required

• Ex: the browser only loads the web page when users scroll down

• Not efficient to DB because too many queries, but good to use when not enough memory on the client side

Over-eager Load

• Anticipate what users need and preload it

Solve it in Rails

Prerequisites

Solution

1. includes:
Example:

@users = User.includes(:posts)
# Only two queries
# SELECT * FROM users
# SELECT * FROM posts WHERE posts.id IN (users.post_ids)

 
💡
includes also supports filtering using where.

2. joins:
Example:

@users = User.joins(:posts).where(posts: { published: true })
# One query but it is inner join
# SELECT * FROM users INNER JOIN posts ON posts.id = users.post_ids WHERE posts.published IS true

3. preload and eager_load: In addition to includes, ActiveRecord provides preload and eager_load methods that offer different strategies for eager loading.
Example:

@users = User.preload(:posts)
# Only two queries
# SELECT * FROM users
# SELECT * FROM posts WHERE posts.id IN (users.post_ids)

Source