🤨 What is N+1 problem?

💎 What is it?

Simply put, a lot of SQL query executes in loop

💎 For example

There are two tables in database

▼ country table

id	name
1	Japan
2	Poland

▼ food table

id	country_id	name
1	1	sushi
2	1	ramen
3	1	udon
4	2	pierogi
5	2	kotlet
6	2	ziemniaki

Get country name

(I'm going to use ruby code)

# SELECT * FROM country;
Country.all.each do |country|
  put country.name
    # Japan
    # Poland
end

Call another table in loop

# SELECT * FROM country;
Country.all.each do |country|
  put country.name
    # Japan
    # Poland

  # SELECT * FROM food WHERE country_id=1;
  # SELECT * FROM food WHERE country_id=2;
  # 😫 😭 if country table had 200 records, 200 query would be executed!!! OMG
  country.foods.each do |food|
    put food.name
      # sushi
      # ramen
      # udon
      # pierogi
      # kotlet
      # ziemniaki
  end
end

💎 How to solve it?

• JOIN
• Eager Load

JOIN

Integrate tables !

# SELECT * FROM country INNER JOIN food ON country.id=food.country_id
# 😀 😀 only 1 query !
Country.join(:food).all.each do |country|
  country.foods.each do |food|
  end
end

Eager Load

Not integrate tables completely like JOIN, but executes SELECT at first !

# SELECT * FROM country;
# SELECT * FROM food WHERE country_id IN(1,2)
# 😀 😀 only 2 queries !
Country.includes(:food).all.each do |country|
  country.foods.each do |food|
  end
end

⭐ if you want to use eager load in Ruby, just use includes

💎 Disadvantage

Memory leak because Join/Eager load need to execute huge SELECT at first

---

How to resolve N+1 problem on Rails

Kaziu ・ Apr 22 '22

#rails #sql #performance #ruby