Lazy vs eager loading performance on Entity Framework

Question

So I have the following model classes on my DbContext:

Everytime I render a list of LoanApplication objects I do something like this:

var context = new MyContext();
var applications = context.LoanApplications.Where(d => d.PropertyThatIWantToFilter = localVariable);

This returns an IQueryable that then I convert to a ViewModel like this on my controller method call:

var vm = applications.Select(d => new LoanApplicationViewModel(d));

Inside the LoanApplicationViewModel constructor I accept the entity object and do the corresponding mapping. The thing is that, since the Solicitors collection is a navigational property, a call is made to the database each time a new view model is instanced. The average number of solicitors per application is two, so that means that if I render a table listing the last 10 applications then the app is making about ~18-20 trips to the database.

I thought there had to be a better way to get this collection, so I changed my original query to eagerly load the collection like so:

var applications = context.LoanApplications.Include("Solicitors").Where...

Although this reduced the number of calls to the database to only one, the query was much slower, about 50% more slow.

The database is hosted on SQL Azure, and we've implemented Transient Fault Handling, but I want to reduce the quantity of calls made to the database without reducing response-time performance.

What is the best practice here?

Answer 1

"What is the best practice here?"

The best practice is to

1. set !application wide! performance target
2. profile, benchmark, and locate bottle neck
3. review and fine tune the bottle neck that gives you the greatest performance win for least work. (and from my experience 90% of the time it's not tsql)

Now that may seem a bit irrelevant, but from that point of view, which ever loading pattern you PROFILED to be optimal within your application domain is the correct way to go.

There's no "best practice" of eager/lazy. That's why both options are both available. Also if the tsql is your bottle neck and switching between eager/lazy still isn't hitting your performance target, you will need to go down a whole plethora of other tools such as query analyzer and query plan analyser in SSMS.

---

For some background:

I was googling "eager loading slow" and came here. Here's my result:

var foo = _context.Foos
    //.Include("Answers")
    //.Include("Attachments")
    .FirstOrDefault(q => q.Id == key);

Eager loading: 106ms

Lazy loading: 11ms + 5ms + 5ms

Lazy loading wins, end of story.

Answer 2

In addition to SQL statements that gives a huge results or lots of calls when using both eager and lazy there is huge job that takes place by putting and mapping into the ObjectContext/DbContext from the result. This causes a huge performance hit and I can't really recommend any of these when retrieving large amount of data.

The best solution is to specify an explicit Select call. However, it's a bit difficult to give you an example on how to do this without knowing how your viewmodel object is built up. So, what I do here is giving you an example that uses anonymous object's as result from the query.

This example gives you contacts with information about the customer the contact belongs to.

var contacts = context.Contacts.Where(row => row.CategoryId == 1)
                      .Select(row => new {
                                             ContactId = row.Id,
                                             Name = row.Name,
                                             CustomerName = row.Customer.Name
                                         }).ToList();

This query will generate an SQL SELECT that joins Contacts with Customer using an inner join, and then only select the Contact.Id, Contact.Name and Customer.Name columns.

This solution is far most the most effective way to retrieve data from server if you don't intend to work with the data and save the changes right back to the same context. It doesn't use either eager nor lazy loading.