Know Better About N+1 Queries Problem
Tags: query
Published: 2023-11-29 16:41:42

## Overview

In the engineering process we often facing the case to query all the data based on it's parent and the data will be used for some reason. For example, let say there is `users` table that has correlation one-to-many with the `transactions` table, you need to get all the users and it's transactions based on the `user_ids` that given from argument. The simple logic that we will do is to get all the user with id include in `user_ids` then get all the transactions one by one

```
def load_data(user_ids)
	result = []
	users = User.where(id: user_ids)
	users.each do |user|
		result << { user: user, transactions: user.transactions }
	end
	
	result
end
```

it's really simple logic, but is it good enough? is it bad? is it our service can endure the high throughput? is there any way to make it more efficient?

## Look Inside the Query

Let say we have this model
```
class User < ApplicationRecord
  has_many :transactions
end
```

```
class Transaction < ApplicationRecord
  belongs_to :user
end
```

we're gonna simulate the query in the rails console (run: `rails console`)

![load_data method](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z3s669swxvh01ezrqb7l.png)

we see in the image, the `load_data` method called 4 queries to the database. The first one is query all the users based on the user_ids, and the 3 others are queries to fetch the transactions for each user. 
```
SELECT "users".* FROM "users" WHERE "users"."id" IN (?, ?, ?)  [["id", 1], ["id", 2], ["id", 3]]
SELECT "transactions".* FROM "transactions" WHERE "transactions"."user_id" = ? /* loading for inspect */ LIMIT ?  [["user_id", 1], ["LIMIT", 11]]
SELECT "transactions".* FROM "transactions" WHERE "transactions"."user_id" = ? /* loading for inspect */ LIMIT ?  [["user_id", 2], ["LIMIT", 11]]
SELECT "transactions".* FROM "transactions" WHERE "transactions"."user_id" = ? /* loading for inspect */ LIMIT ?  [["user_id", 3], ["LIMIT", 11]]
```


What happen if the user_ids is so big? will we query to fetch the transactions as much as the user that we have? now we facing the N+1 queries problem

## N+1 query problem?

This is common problem in the database query, it will execute the query one-by-one for all instance instead of 1 or 2 queries. In the example above we fetch all the three users data, then continue with query all the transactions for each user, it count 4 queries (1+3). If the are N users data, first it will fetch all the N users then continue to query all the transactions for each user, so it's called N+1 queries.

The problem in the N+1 queries is each query will take some amount of time, bigger data that we fetch bigger time that we need and we may facing the timeout issue. N+1 query is not good for the performance and we need find the solution

\*we can ignore the N+1 query if the data is small or low throughput

## Eager Load

In ruby, we have Eager load mechanism to load all the data and it's association with single query. One of the method to trigger the eager_load is `.includes`

```
def load_data_with_eager_load(user_ids)
	result = []
	users = User.includes(:transactions).where(id: user_ids)
	users.each do |user|
		result << { user: user, transactions: user.transactions }
	end
	
	result
end
```

the method above will give result

![load_data_with_eager_load method](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dcxfvyxruf9z5yfzw5s3.png)

if we look closely, the `load_data_with_eager_load` method only trigger two query. First query get all the users and the second query get all the transactions for all users

```
SELECT "users".* FROM "users" WHERE "users"."id" IN (?, ?, ?)  [["id", 1], ["id", 2], ["id", 3]]
SELECT "transactions".* FROM "transactions" WHERE "transactions"."user_id" IN (?, ?, ?)  [["user_id", 1], ["user_id", 2], ["user_id", 3]]
```

It reduce the database query significantly, the eager load only cost 2 queries for however much data we have