DEV Community

loading...

Join vs includes vs eager load vs preload

M Bellucci
Software Engineer. 5+ years working with Ruby & Rails
・2 min read

Intro

Per rails guides

Eager loading is the mechanism for loading the associated records of the objects returned by Model.find using as few queries as possible.
Enter fullscreen mode Exit fullscreen mode

This is the API that rails give you to avoid N + 1 queries.

  • includes
  • joins
  • preload
  • eager_load

Reference

SQL contains join Load associated record in memory Performs two queries
joins yes (inner join) no no
preload no yes yes
includes yes (left join) yes sometimes
eager load yes (left join) yes no

Joins

  • Only affects the SQL query
  • doesn’t load related data into memory
pry(main)> u = User.joins(:alerts).first
  User Load (1.4ms)  SELECT "users".* FROM "users" INNER JOIN "alerts" ON "alerts"."user_id" = "users"."id" ORDER BY "users"."id" ASC LIMIT $1  [["LIMIT", 1]]
...
pry(main)> u.alerts
  Alert Load (0.6ms)  SELECT "alerts".* FROM "alerts" WHERE "alerts"."user_id" = $1  [["user_id", 2]]
...
Enter fullscreen mode Exit fullscreen mode

Preload

  • Load associated data into memory
  • User.preload(:posts) executes two queries
pry(main)> u = User.preload(:alerts).first
  User Load (0.4ms)  SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT $1  [["LIMIT", 1]]
  Alert Load (0.3ms)  SELECT "alerts".* FROM "alerts" WHERE "alerts"."user_id" = $1  [["user_id", 1]]
=> #<User id: 1, email: ...
pry(main)> u.alerts
=> []

Enter fullscreen mode Exit fullscreen mode

Includes

  • Performs outer left join and store the associated result in memory.
  • Sometimes generate one query and sometimes generates 2 queries.
  • If you want to force to use a single query you can add .references
pry(main)> u = User.includes(:alerts).first
  User Load (0.4ms)  SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT $1  [["LIMIT", 1]]
  Alert Load (0.2ms)  SELECT "alerts".* FROM "alerts" WHERE "alerts"."user_id" = $1  [["user_id", 1]]
=> #<User id: 1, email:...
pry(main)> u.alerts
=> []


pry(main)> users = User.includes(:alerts).references(:alerts)
  SQL (12.7ms)  SELECT "users"."id" AS t0_r0, t1_r1, "alerts"."user_id" AS t1_r2, "alerts"."date" AS t1_r3,... FROM "users" LEFT OUTER JOIN "alerts" ON "alerts"."user_id" = "users"."id"
=> [#<User id: 2, ...
pry(main)> users.first.alerts
=> [#<Alert:0x00007fc279e76128
  id: 1,
...
Enter fullscreen mode Exit fullscreen mode

Eager Load

  • Load data in memory
  • Performs SQL join
  • Forces to use a single query
  • Some times takes more time than two queries
pry(main)> users = User.eager_load(:alerts)
  SQL (14.0ms)  SELECT "users"."id" AS t0_r0, ... FROM "users" LEFT OUTER JOIN "alerts" ON "alerts"."user_id" = "users"."id"
=> [#<User id: 2, ...
pry(main)> users.first.alerts
=> [#<Alert:0x00007fc269b87d78
  id: 1,
  ...
Enter fullscreen mode Exit fullscreen mode

Discussion (0)