How to resolve N+1 problem on Rails
Tags: rails, sql, performance, ruby
Published: 2022-04-22 21:09:34

{% link https://dev.to/kaziusan/what-is-n1-problem-2h1a %}

## 🔑 4 key methods
- joins
- eager_load
- preload
- includes

## 💎 joins
**integrate by INNER JOIN**
- doesn't cache association, so if you don't need data which is created again, you should use it.
- save memory allocated spaces because ActiveRecord object doesn't cache.
```ruby
Skill.joins(:skill_category).limit(5)
↓
SELECT "skills".* 
FROM "skills" 
INNER JOIN "skill_categories" 
ON "skill_categories"."id" = "skills"."skill_category_id" 
LIMIT ?  [["LIMIT", 5]]
```
## 💎 eager_load
**integrate by LEFT OUTER JOIN with cache**
- faster than preload() because it creates only one SQL
- it can use **WHERE** in table which is integrated by JOIN (preload() can't do it)

```ruby
Skill.eager_load(:skill_category).limit(5)
↓
SELECT "skills"."id" AS t0_r0, "skills"."name" AS t0_r1, "skills"."user_id" AS t0_r2, "skills"."skill_category_id" AS t0_r3, "skills"."created_at" AS t0_r4, "skills"."updated_at" AS t0_r5, "skill_categories"."id" AS t1_r0, "skill_categories"."name" AS t1_r1, "skill_categories"."reccomend" AS t1_r2, "skill_categories"."created_at" AS t1_r3, "skill_categories"."updated_at" AS t1_r4 
FROM "skills" 
LEFT OUTER JOIN "skill_categories" 
ON "skill_categories"."id" = "skills"."skill_category_id" 
LIMIT ?  [["LIMIT", 5]]
```

## 💎 preload
**use multiple SQLs with cache**
- recommend to use it when you have big table which you don't wanna JOIN
- * it's impossible to use WHERE because it is not integrated by JOIN

```ruby
Skill.preload(:skill_category).limit(5)
↓
# this one
SELECT "skills".* FROM "skills" LIMIT ?  [["LIMIT", 5]]
# and this one
SELECT "skill_categories".* 
FROM "skill_categories" 
WHERE "skill_categories"."id" 
IN (?, ?, ?, ?, ?)  [[nil, 483], [nil, 583], [nil, 901], [nil, 181], [nil, 147]]
```
## 💎 includes
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ej0m5eff44gf6md4fm6u.png)
 
if you use **where, join, references method** at least one, executes as **eager_load**, otherwise **preload**

```ruby
# just includes
Skill.includes(:skill_category).limit(5)
↓
# this one
SELECT "skills".* FROM "skills" LIMIT ?  [["LIMIT", 5]]
# and this one
SELECT "skill_categories".* 
FROM "skill_categories" 
WHERE "skill_categories"."id" 
IN (?, ?, ?, ?, ?)  [[nil, 483], [nil, 583], [nil, 901], [nil, 181], [nil, 147]]
```
```ruby
# using where()
Skill.includes(:skill_category).where(skill_categories: { name: 'baseball' })
↓
# just one SQL like eager_load 👍
SELECT "skills"."id" AS t0_r0, "skills"."name" AS t0_r1, "skills"."user_id" AS t0_r2, "skills"."skill_category_id" AS t0_r3, "skills"."created_at" AS t0_r4, "skills"."updated_at" AS t0_r5, "skill_categories"."id" AS t1_r0, "skill_categories"."name" AS t1_r1, "skill_categories"."reccomend" AS t1_r2, "skill_categories"."created_at" AS t1_r3, "skill_categories"."updated_at" AS t1_r4 
FROM "skills" 
LEFT OUTER JOIN "skill_categories" 
ON "skill_categories"."id" = "skills"."skill_category_id" 
WHERE "skill_categories"."name" = ?  [["name", "baseball"]]
```

includes method is convenience as you see, but when other developer will see includes method, they need to think it means "preload" or "eager_load"