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,
...
Top comments (1)
Hi Bellucci, thanks for sharing this useful article. I found two of the things in the article to be worth pointing out.
preloadthat 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.