User:Dudu9929/sandbox

Eager Loading is a design pattern that is used in software engineering to load objects from databases to applications. When eager loading is applied to a process, a single query will load all the data of the related entities from the database so that the frequency of the database access will be reduced.

Description

Eager loading is a loading mechanism designed for Object-Relational Mapping patterns. As the name suggests, eager loading loads the data "eagerly" as opposed to the default enabled lazy loading. When a database query is raised in some related entities, eager loading will load everything upon being asked, while lazy loading will load a certain object only when that object is explicitly needed.

For example, there is a list of objects students with attributes dob and year. The Ruby code for printing out the Year of Birth of each student and the corresponding SQL code are

students = Student.limit(10)
students.each do |student|
  puts student.dob.year
end

#   SELECT * FROM students LIMIT 10
#   SELECT dobs.* FROM dobs  WHERE dob.student_id = 1
#   SELECT dobs.* FROM dobs  WHERE dob.student_id = 2
#   SELECT dobs.* FROM dobs  WHERE dob.student_id = 3
#   SELECT dobs.* FROM dobs  WHERE dob.student_id = 4
#   SELECT dobs.* FROM dobs  WHERE dob.student_id = 5
#   SELECT dobs.* FROM dobs  WHERE dob.student_id = 6
#   SELECT dobs.* FROM dobs  WHERE dob.student_id = 7
#   SELECT dobs.* FROM dobs  WHERE dob.student_id = 8
#   SELECT dobs.* FROM dobs  WHERE dob.student_id = 9
#   SELECT dobs.* FROM dobs  WHERE dob.student_id = 10

When loading objects from database, this ruby code will adopt lazy loading by default. However, there is an underlying problem in this code. As the entity requests data for each object, the SQL code will execute 10+1=11 queries to the database. This is the 'N+1 problem' in Object-Relational Mapping.

students = Student.eager_load(:dob)
students.each do |student|
  puts student.dob.year
end

As compared to the first code block, this ruby code section utilizes the eager_load method to realize a simple eager loading pattern and the corresponding SQL statement is

SELECT
#  "students"."id" AS t0_r0, "users"."dob" AS t0_r1
#  "dob"."year" AS t1_r0, "dob"."month" AS t1_r1, "dob"."day" AS t1_r2, "dob"."student_id" AS t1_r3
#  FROM "students"
#  LEFT OUTER JOIN "dobs" ON "dobs"."student_id" = "students"."id"

The SQL code above executes only 1 query as eager loading will load all the data in one combined large query. This code will run much faster and helps to solve the 'N+1 problem' in lazy loading.

Implementation

Eager loading can be implemented in different Object-Relational Mapping patterns such as Active Record and Microsoft Entity Framework.

Rails in Active Record

There are three different methods to achieve eager loading from database in Active Record with Ruby on Rails. For the same "student" example above:

Preload()

Student.preload(:dob)
#   SELECT * FROM students LIMIT 10
#   SELECT dobs.* FROM dobs
#     WHERE (dob.student_id IN (1,2,3,4,5,6,7,8,9,10))

When Preload() method is used, rails uses separate queries for each database to get related data.

Eager_load()

Student.eager_load(:dob)
#  SELECT
#  "students"."id" AS t0_r0, "users"."dob" AS t0_r1
#  "dob"."year" AS t1_r0, "dob"."month" AS t1_r1, "dob"."day" AS t1_r2, "dob"."student_id" AS t1_r3
#  FROM "students"
#  LEFT OUTER JOIN "dobs" ON "dobs"."student_id" = "students"."id"

As is shown above, eager_load() method can load all attributes of the object in one single query by adding a LEFT OUTER JOIN statement in corresponding SQL code.

Includes()

Includes() method behaves more flexibly in different situations.

• Situation 1

  Student.includes(:dob)
  #   SELECT * FROM students LIMIT 10
  #   SELECT dobs.* FROM dobs
  #     WHERE (dob.student_id IN (1,2,3,4,5,6,7,8,9,10))
  

  In this situation, include() method behaves the same as preload() method with 2 separate queries for 2 databases.
• Situation 2

  Student.includes(:dob).reference(:dob)
  #  SELECT 
  #  "students"."id" AS t0_r0, "users"."dob" AS t0_r1
  #  "dob"."year" AS t1_r0, "dob"."month" AS t1_r1, "dob"."day" AS t1_r2, "dob"."student_id" AS t1_r3
  #  FROM "students"
  #  LEFT OUTER JOIN "dobs" ON "dobs"."student_id" = "students"."id"
  

  In this case, includes().reference() method behaves the same as eager_load() method. The reference() method will force the include() method use one query to complete the data loading.

JavaScript in Microsoft Entity Framework

In Microsoft Entity Framework, eager loading can be achieved by JavaScript. Also, for the "student" case, include() method can be used to implement eager loading.

using (var students = new students()) 
{ 
   
    var students1 = students.dob
                          .Include(dob => dob.year == 1994) 
                          .ToList(); 
 

    var student1 = students.dob
                        .Where(dob => dob.year == 1994) 
                        .Include(dob => dob.year) 
                        .FirstOrDefault(); 
}

Application Scenarios

Multiple Navigation Properties Access

When the code needs to access lots of navigation properties from the entities, it will access many addresses of the database and largely increase the number of queries. On this occasion, we can use multiple include statement with eager loading to reduce the queries and increase the operation efficiency.

Data Known at Run Time

If data needed at run time is exactly known, adopting eager loading will help load the precise datasets much faster.

Increased Network Latency

When network condition is unfavorable and large latency is detected, eager loading will typically be the choice as it requires fewer data round trips.

Issues

Higher Complexity

Assume lots of objects are defined in the code and only a few of them have to be loaded from database. To realize eager loading, code has to be written for each defined object, but for lazy loading, only the loaded objects have to be included in the code, which makes the code for eager loading much more complex in most cases.

More Resources

When an end user is using eager loading, more space has to be reserved to load a large number of objects from database. It will result in much space overhead if the data received is huge.

Larger Payload

When more than 3 entities are required in a query, much navigation properties from the entities are needed to be accessed. In this case, payload returned from database can be extremely large. To avoid the data transmission declination for the payload exceeding the limits, lazy loading can be applied in this situation.

See also

1. Software Design Pattern
2. Lazy Loading
3. Active Record Pattern
4. Entity Framework
5. SQL