DEV Community

Kaziu
Kaziu

Posted on

3 2

How to resolve N+1 problem on Rails

πŸ”‘ 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.
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]]
Enter fullscreen mode Exit fullscreen mode

πŸ’Ž 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)
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]]
Enter fullscreen mode Exit fullscreen mode

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

πŸ’Ž includes

Image description

if you use where, join, references method at least one, executes as eager_load, otherwise preload

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

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"

Image of Datadog

The Future of AI, LLMs, and Observability on Google Cloud

Datadog sat down with Google’s Director of AI to discuss the current and future states of AI, ML, and LLMs on Google Cloud. Discover 7 key insights for technical leaders, covering everything from upskilling teams to observability best practices

Learn More

Top comments (1)

Image of Datadog

The Essential Toolkit for Front-end Developers

Take a user-centric approach to front-end monitoring that evolves alongside increasingly complex frameworks and single-page applications.

Get The Kit

πŸ‘‹ Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay