懒惰对实体框架的加载性能 [英] Lazy vs eager loading performance on Entity Framework

问题描述

所以我在DbContext上有以下模型类：

每当我呈现LoanApplication对象列表时，我都会这样做：

  var context = new MyContext（）; 
 var applications = context.LoanApplications.Where（d => d.PropertyThatIWantToFilter = localVariable）; 
  

这将返回一个IQueryable，然后我转换为ViewModel，就像我的控制器方法调用： p>

  var vm = applications.Select（d => new LoanApplicationViewModel（d））; 
  

内部的 LoanApplicationViewModel 构造函数我接受实体对象做相应的映射。事实是，由于Solicitors集合是一个导航属性，因此每次实例化新的视图模型时都会调用数据库。每个应用程序的平均律师人数是两个，所以这意味着如果我提供一个列出最后10个应用程序的表，那么该应用程序正在大约到18-20次访问数据库。

我以为必须有一个更好的方法来获取这个集合，所以我改变了我的原始查询，热切地加载集合，如下所示：

  var applications = context.LoanApplications.Include（Solicitors）。其中... 
  

尽管这样将数据库的调用次数减少到只有一个，查询速度要慢得多，慢50％。 >

数据库托管在SQL Azure上，我们实施了瞬态故障处理，但是我想减少对数据库的调用数量，而不会减少响应时间性能。

这里最好的做法是什么？

解决方案

这里最好的做法是什么？

最好的做法是将

1. 设置！性能目标


2. 配置文件，基准测试和定位瓶颈


3. 审查并微调瓶颈，为您提供最佳性能赢得最少的工作。 （和我的经验90％的时间不是tsql）

现在看起来有点无关紧要，但从视图，您在PROFILED中加载模式在应用程序域内是最佳的，是正确的方式。

没有渴望/懒惰的最佳做法。这就是为什么两个选项都可用。另外，如果tsql是你的瓶颈，而且渴望/懒惰之间的切换还没有达到您的性能目标，那么您将需要在SSMS中下载大量其他工具，如查询分析器和查询计划分析器。

---

对于某些背景：

我是googling渴望加载缓慢和来这里。这是我的结果：

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

渴望加载：106ms

懒惰加载：11ms + 5ms + 5ms

懒惰加载胜利，故事结束。

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?

解决方案

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

这篇关于懒惰对实体框架的加载性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！