Lazy loading Performance issues

Lazy loading allows us to reduce the size of the Main entity object in a LINQ query which has references to related entity objects. When the query executes it fetched the data of only the main entity object, data for the related objects are loaded only when we access the Navigational property from the main table.

With this approach we might end us firing multiple SQL queries to the underlying database and eventually bring down the performance. Let us assume we have a Grid in the View which displays a list of employees and each item has a details link which will display employee data and its related entity department’s data, then when the page loads a SQL query related to the employee’s entity will execute and get the list of employees. Later when the user clicks on each employee details link separate SQL queries will get executed to get each employees department data as follows.

Query to Get the List of Employees

SELECT [e].[EmployeeId], [e].[Age], [e].[City], [e].[CountryId], [e].[DepartmentId], [e].[Email], [e].[Name], [e].[Phone], [e].[State]

FROM [Employee] AS [e]

Queries to get Each employees Department detailsexec sp_executesql N'SELECT [e].[DepartmentId], [e].[Name]

FROM [Department] AS [e]

WHERE [e].[DepartmentId] = @__get_Item_0',N'@__get_Item_0 int',@__get_Item_0=1

exec sp_executesql N'SELECT [e].[DepartmentId], [e].[Name]

FROM [Department] AS [e]

WHERE [e].[DepartmentId] = @__get_Item_0',N'@__get_Item_0 int',@__get_Item_0=2

Notice that each time the user clicks on an employee’s details link a new SQL query is triggered. This way we might end up firing multiple SQL queries instead of one big query at the beginning. 

However if the user clicks on the same link again SQL query will not be triggered as the result is stored in the EF Context after the first execution.

This doesn’t mean that Lazy loading is to be avoided always, it should be used at the right context, make sure to avoid it in the scenarios like the above, this will end up triggering multiple SQL queries and if the user base of the application has 1000s of users we will end up firing up multiple thousands of SQL equerries and eventually slowing down the SQL Server.