Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
602 views
in Technique[技术] by (71.8m points)

c# - Lazy vs eager loading performance on Entity Framework

So I have the following model classes on my DbContext:

Loans

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

"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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...