c# - 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.