Part 10 Difference between eager loading and lazy loading

Text version of the video

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

Slides

LINQ to SQL Tutorial - All Text Articles & Slides

LINQ to SQL Tutorial Playlist

Dot Net, SQL, Angular, JavaScript, jQuery and Bootstrap complete courses

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 something called n + 1 select problem. Let us understand this with an 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.

[oumeilan]

I watched a few clips and start liking the tutorials. Very practical!

[snldev]

I really enjoyed the vid. thanks for sharing it!

[shailabhagwat]

Crystal Clear Explanation thanks for making this topic simple

[vm]

Thanks for the video!! Quick thing: In the example of eager loading: if we just need the Departments' data then we don't need to query Employees at all. It is just one set of data that we need and we are done. How does this fall under eager loading? Little confused. Appreciate any insight. Thanks in advance.

[ikrambk]

Thanks for the video, very interesting

[jawadahmed]

could you differentiate between deffered execution and lazy loading

[deniskhaetsky]

Venkat, please please please, do a FILESTREAM video for SQL server! Storing large files is always useful!

[sunilanthony]

Hi Venkat...Is it possible to upload NHibernate tutorial?