Understanding N+1 Query Problems and Their Impact on API Speed

Have you ever noticed your endpoint response takes too long to respond?

There could be many reasons for that, but one of the most common is spending a lot of time on database calls.

A frequent cause of database problems in business logic is the N+1 query problem, which happens behind the scenes without us realizing it.

So, what exactly is the N+1 query problem?

The N+1 query problem is a performance issue where the database makes N+1 queries to fetch the required data instead of making a single query.

The N+1 query problem occurs when an application makes one query to retrieve a set of records (N) and then iteratively makes additional queries for each of these records (1 for each, thus N+1).

Sometimes the ORM we use to load the data is the main reason for creating an N+1 query problem.

We use two approaches to load data from ORM: eager loading and lazy loading.

Loading data from the database using ORM in lazy loading mode can cause an N+1 query problem.

However, ORM with eager loading mode triggers a single query to fetch the data rather than N+1 queries.

Most ORMs have lazy loading, also known as "on-demand loading," as the default behavior.

Let us understand this with an example. We have a post table and a comments table. We need to fetch all posts with their comments. We can achieve this in two ways: eager and lazy loading.

Lazy Loading:

Run one query to fetch the posts.

Then N additional queries to fetch the comments for each post using looping.

Example of Lazy Loading in Laravel:

$posts = Post::all();

foreach ($posts as $post) {
    $comments = $post->comments;
}

Lazy loading is easier to use because you don't have to specify which related data to load in advance. However, it can cause performance issues, especially when accessing related data for multiple records in a loop, leading to the N+1 query problem.

Eager Loading:

Loads the comments for all the retrieved posts in a single query using joins, rather than running one query to fetch the posts and then N additional queries to fetch the comments for each post.

Example of Eager Loading in Laravel:

$posts = Post::with('comments')->get();

Eager loading is helpful when you know you will need related data for a set of records and want to avoid the N+1 query problem.

When to use which?

Eager Loading:

Use eager loading when you need to fetch multiple related entities at once. For example, if you need to display users and their details on the same page, eager loading is the best choice. It makes a single query to the database and loads the related entities.

Lazy Loading:

Use lazy loading when you only need to show users on a page, and you will display user details only when a user is clicked. Lazy loading makes multiple queries to load related entities as needed when you bind or iterate over them.

Conclusion

To sum up, I would say, that first we need to analyze how, and where the N+1 query problem occurs on your endpoint, and the next thing is to solve it after analyzing your use case.