DEV Community

M Bellucci
M Bellucci

Posted on

Join vs includes vs eager load vs preload

#rails #ruby

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

Top comments (1)

Collapse
 
masroorhussainv profile image
Masroor Hussain • • Edited

Hi Bellucci, thanks for sharing this useful article. I found two of the things in the article to be worth pointing out.

  1. I think its more appropriate to mention this fact about preload that it will not always fire 2 queries, the actual query count depends on the number of associations specified in its aruguments. Each association will get loaded in a separate query of its own.

For example:

User.preload(:alerts) will do 2 separate queries, but if we add another association to be loaded, the query count increases.
User.preload(:alerts, :posts) will fire 3 queries.

2.eager_load can also fire more than 1 query. Its not clear to me why but I've verified it in a project by using Rails console. Also there's this stackoverflow post mentioning this behavior.

Read next

epigene profile image

Index tables from production console

Augusts Bautra -

baweaver profile image

Let's Read - Eloquent Ruby - Ch 16

Brandon Weaver -

alexandrecalaca profile image

How to temporarily skip the pending migration check in Rails

Alexandre Calaça -

railsdesigner profile image

Connected and Disconnected Target Callbacks with Stimulus

Rails Designer -