Blog Stats
  • Posts - 23
  • Articles - 0
  • Comments - 14
  • Trackbacks - 0

 

Monday, August 6, 2012

LINQ to SQL & Entity Framework slow relationships


Lately I've inherited some code written by another company using quite a bit of LINQ to SQL and quite a bit of lazy loading.  I changed much of the worst offenders to eager load the relationships I knew I would want, and still felt like the system was slower than I would have expected.

A sample of my code:

DataContext custom = new DataContext();
DataLoadOptions dlo = new DataLoadOptions();
custom.DeferredLoadingEnabled = false;
dlo.LoadWith<Customer>(C => C.CustomerAddress);
custom.DataLoadOptions = dlo;

custom.Customer.Where(c => c.Active == true).ToList();

After looking at the function in SQL Profiler, I noticed something interesting about how LoadWith works in LINQ to SQL - And I was surprised -

Inside one database transaction, first SQL executes the filtered query against the Customer table.  Next, for every Customer Address in the result set, the following query executes:

SELECT [t0].[AddressID],[t0].[Address1],[t0].[Address2],[t0].[City],[t0].[State],[t0].[Zip]
FROM [dbo].[CustomerAddress] AS [t0]
WHERE [t0].[AddressID] = @p0

In many of the queries, the relationship chain is several tables long, and in worst cases - causing the number of related look-ups to grow geometrically.

For many of those queries, I switched to using LINQ to SQL to fetch all of the data in one query, and iterating through the result assigning to lists on the server rather than using the LINQ to Entities approach which brought the run-time down to a much more respectable number.

 

 

Copyright © jkrebsbach