Join vs includes vs eager load vs preload

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.

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]]
...

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
=> []

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,
...

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,
  ...

Comments

[Masroor Hussain]

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.