DEV Community

Kaziu
Kaziu

Posted on • Updated on

๐Ÿคจ 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

๐Ÿ’Ž 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

โญ 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


Top comments (0)