Part 10 - Difference between eager loading and lazy loading

Suggested Videos
Part 7 - What is SqlMetal
Part 8 - Lazy loading in LINQ to SQL
Part 9 - Eager loading in LINQ to SQL



In this video we will discuss the difference between eager loading and lazy loading. This is continuation to Part 9. Please watch Part 9 before proceeding.



With lazy loading there is a problem called n + 1 select problem. Let us understand this problem with an example. In this example there is a One-to-Many relationship between Department and Employee entities. A Department can have 1 or more employees.


Now, let's say we need to iterate through all the Departments, and for each Department, we want to print the list of the employees. By default, LINQ to SQL would do the following:

Select * from Departments

/* For each Department */

SELECT * FROM Employees WHERE DepartmentId = X

So, this means we have one select for the Departments, and then N additional selects to retrieve the employees belonging to each Department, where N is the total number of Departments. So, this is N + 1 problem.

What is the difference between eager loading and lazy loading? Which is good - eager loading or lazy loading?
Without looking at the application architecture and what we are trying to achieve, we cannot say one is better over the other. Both have their own advantages and disadvantages. There are clear performance trade-offs between eager and lazy loading objects from a database.

With eager loading, all the data is retrieved in a single query, which can then be cached to improve the application performance. With eager loading we are trading memory consumption for database round trips.

With lazy loading, we only retrieve just the amount of data that we need in a single query. When we need more data related to the initial data, additional queries are issued to the database. This means there are several round trips between the application server and the database server. In general, these database round trips are very often the major performance bottleneck in most applications. Lesser the round trips, better the performance.

For example, if on a given page you are only displaying Departments, then there is no reason for eager loading related Employees data. So in this case lazy loading works best. On the other hand, if you are displaying both Department and Employees data, then eager loading works best, as it avoids the additional round trips to the database.

If you are not sure of what data is exactly needed, start with lazy loading and if it is leading to N + 1 problem then eager load the data.