Today I will write about a topic that is quite familiar to many people, it’s eager load . Surely, first, when you learn about something, you will ask the question: What is it? What does it do? Why is it that is not something else? Without it, what happens? .vv. And we will answer those questions.
Eager load and its nature.
It can be said that the Eager load was born to solve data query performance in the database. Consider the following paragraph
Suppose we have 1 student class and 1 class relationship as below
1 2 3 4 5 6 7 8 | class Class < ActiveRecord::Base has_many :students end class Student < ActiveRecord::Base belongs_to :class end |
And with the question, list the 10 students with the name of the class the student is attending.
With lazy loading, we will do it as follows
1 2 3 4 5 | students = Students.limit(10) students.each do |student| puts student.class.name end |
And with the above code, it will be as simple as taking out 10 students, then looping each student to get the class name that the student is studying
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT `students`.* FROM `students` LIMIT 10 SELECT `classes`.* FROM `classes` WHERE `classes`.`deleted_at` IS NULL AND `classes`.`id` = 1 LIMIT 1 SELECT `classes`.* FROM `classes` WHERE `classes`.`deleted_at` IS NULL AND `classes`.`id` = 2 LIMIT 1 SELECT `classes`.* FROM `classes` WHERE `classes`.`deleted_at` IS NULL AND `classes`.`id` = 3 LIMIT 1 SELECT `classes`.* FROM `classes` WHERE `classes`.`deleted_at` IS NULL AND `classes`.`id` = 4 LIMIT 1 SELECT `classes`.* FROM `classes` WHERE `classes`.`deleted_at` IS NULL AND `classes`.`id` = 5 LIMIT 1 SELECT `classes`.* FROM `classes` WHERE `classes`.`deleted_at` IS NULL AND `classes`.`id` = 6 LIMIT 1 SELECT `classes`.* FROM `classes` WHERE `classes`.`deleted_at` IS NULL AND `classes`.`id` = 7 LIMIT 1 SELECT `classes`.* FROM `classes` WHERE `classes`.`deleted_at` IS NULL AND `classes`.`id` = 8 LIMIT 1 SELECT `classes`.* FROM `classes` WHERE `classes`.`deleted_at` IS NULL AND `classes`.`id` = 9 LIMIT 1 SELECT `classes`.* FROM `classes` WHERE `classes`.`deleted_at` IS NULL AND `classes`.`id` = 10 LIMIT 1 |
Of course, with the above code, we will have the right results with the question posed. But the problem is, if the question above is not a list of 10 students but 100, 1,000 … all students or students of the school, we will write the same or not. If so, make sure you do not know eager load .
So, what if eager loading is used?
1 2 3 4 5 | students = Student.includes(:class).limit(10) student_names = students.map do |student| students.class.name end |
And look at the result of the code above to execute offline
1 2 3 | SELECT `students`.* FROM `students` LIMIT 10 SELECT `classes`.* FROM `classes` WHERE `classes`.`deleted_at` IS NULL AND `classes`.`id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) |
We can clearly see the difference in the two executables corresponding to the two pieces of code, with the code not using eager load, it will take us 11 times to access the database to retrieve the data, but also With eager load, it only takes 2 queries to execute the same question. Maybe with 10 students, we won’t really see the performance it brings, but if instead of 10 students, we need data for N students, then surely eager loading will help us avoid N jobs. +1 the same query executed in the database
The eager loading implementation
In the code above, we used includes to solve the N + 1 query problem, and we will look through 3 execution methods to see the differences between each type and which method to use. suitable for each case.
includes ()
Use:
1 2 | User.includes(:addresses) |
The query executes in the database:
1 2 3 | SELECT "users".* FROM "users" SELECT "addresses".* FROM "addresses" WHERE "addresses"."user_id" IN (1, 2) |
preload ()
Use:
1 2 | User.preload(:addresses) |
Execution query:
1 2 3 | SELECT "users".* FROM "users" SELECT "addresses".* FROM "addresses" WHERE "addresses"."user_id" IN (1, 2) |
eager_load ()
Use:
1 2 | User.eager_load(:addresses) |
The query executes:
1 2 3 4 | SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "users"."email" AS t0_r2, "users"."created_at" AS t0_r3, "users"."updated_at" AS t0_r4, "addresses"."id" AS t1_r0, "addresses"."user_id" AS t1_r1 FROM "users" LEFT OUTER JOIN "addresses" ON "addresses"."user_id" = "users"."id" |
Looking at the queries that the above 3 methods execute, we can see that:
- includes () and preload () have the same execution method, which is to perform 2 separate queries.
- while eager_load () is combined into one query to execute simultaneously because the nature of eager_load () works based on the Left outer join.
So there is a difference between eager_load () and includes () and preload (), but includes () and preload (), do they really work the same? If so, why must both be born for?
Check out the following example to see what our result will look like:
1 2 3 4 5 | User.includes(:addresses).where("addresses.name = ?", "Ha Noi").references(:addresses) =>> SELECT `users`.`id` AS t0_r0, `users`.`name` AS t0_r1, `users`.`created_at` AS t0_r2, `users`.`updated_at` AS t0_r3, `addresses`.`id` AS t1_r0, `addresses`.`name` AS t1_r1, `employees`.`deleted_at` AS t1_r2 FROM `users` LEFT OUTER JOIN `addresses` ON `addresses`.`user_id` = `users`.`id` WHERE (addresses.name = 'Ha Noi') |
and
1 2 3 4 5 6 7 | User.preload(:addresses).where("addresses.name = ?", "Ha Noi").references("addresses") =>> SELECT `users`.* FROM `users` WHERE (addresses.name = 'Ha Noi') Mysql2::Error: Unknown column 'addresses.name' in 'where clause': SELECT `users`.* FROM `users` WHERE (addresses.name = 'Ha Noi') ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'addresses.name' in 'where clause': SELECT `users`.* FROM `users` WHERE (addresses.name = 'Ha Noi') |
So after adding the condition for the query using includes (), the query is executed in the same way that eager_load () works that is using the Left outer join and only takes 1 query. And preload () was unable to execute the above query because its nature is still to execute two independent queries ==> is there a way to fix preload (), the answer is of course yes and It’s not strange because it’s join
1 2 3 4 5 | User.joins(:addresses).where("addresses.name = ?", "Ha Noi").preload(:addresses) ==> SELECT users.* FROM users INNER JOIN addresses ON addresses.user_id = users.id WHERE addresses.name = 'Ha Noi' |
And remember, joins () works on the Inner join mechanism rather than the Left outer join as eager_load () did.
Summarizing a little
If you persevere to read this, surely you have the concept of eager load and its purpose. Your job now is to directly apply it into your own lines of code in a way that makes your application smoother.