C# 实体框架上的延迟加载与急切加载性能

Lazy vs eager loading performance on Entity Framework

提问by amhed

So I have the following model classes on my DbContext:

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

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

每次渲染 LoanApplication 对象列表时，我都会执行以下操作：

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:

这将返回一个 IQueryable，然后我在控制器方法调用中将其转换为这样的 ViewModel：

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

Inside the LoanApplicationViewModelconstructor 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.

在LoanApplicationViewModel构造函数中，我接受实体对象并进行相应的映射。问题是，由于 Solicitors 集合是一个导航属性，每次实例化新视图模型时都会调用数据库。每个申请的平均律师人数是两个，这意味着如果我呈现一个列出最后 10 个申请的表格，那么该应用大约需要 18-20 次访问数据库。

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.

尽管这将调用数据库的次数减少到只有一次，但查询速度要慢得多，大约慢了 50%。

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.

数据库托管在 SQL Azure 上，我们已经实施了瞬态故障处理，但我想在不降低响应时间性能的情况下减少对数据库的调用数量。

What is the best practice here?

这里的最佳做法是什么？

回答by Malcolm O'Hare

If you could somehow query your solicitors table and filter the query using your already fetched list of applications then the fetched Entities would be cached in your context, which I believe will then be used for the navigation property instead of hitting the database.

如果您可以以某种方式查询您的律师表并使用您已经获取的应用程序列表过滤查询，那么获取的实体将被缓存在您的上下文中，我相信它将用于导航属性而不是访问数据库。

I'm not sure exactly how to write the solicitors fetching query, but I was thinking something like this

我不确定如何编写获取查询的律师，但我在想这样的事情

int[] applicationIDs = applications.Select(x => x.ID).ToArray();
var solicitors = context.Solicitors.Where(x => x.Applications.Any(y => applicationIDs.Contains(y.ID))).ToArray(); // added toarray to cause execution cause im never sure when the LINQ actually runs

回答by Fendy

Have you considered to use sql view?

你有没有考虑过使用sql视图？

I don't quite sure about Sql Azure. However in sql server, you can have performance penalty when joining 2 tables without having proper indexes. Maybe this happen in your query.

我不太确定 Sql Azure。但是，在 sql server 中，在没有正确索引的情况下加入 2 个表时可能会降低性能。也许这发生在您的查询中。

To be noted, your before query is accessing 1 table with where clause, 2 calls. And in the after query, it is accessing 2 tables with where clause, 1 call. There is join in your after query and is likely to need different index.

需要注意的是，您的 before 查询正在使用 where 子句访问 1 个表，2 次调用。在之后的查询中，它使用 where 子句访问 2 个表，1 次调用。查询后有连接，可能需要不同的索引。

You can create a sql view to make sure that a proper index is used. Then make your application call the view. Stored procedure can be used for this purpose too but it is less suitable for this.

您可以创建一个 sql 视图以确保使用正确的索引。然后让您的应用程序调用视图。存储过程也可用于此目的，但不太适合此目的。

回答by Rune G

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.

除了在使用 Eager 和 Lazy 时会产生大量结果或大量调用的 SQL 语句之外，还有大量工作是通过将结果放入 ObjectContext/DbContext 并将其映射到 ObjectContext/DbContext 中来实现的。这会导致巨大的性能损失，在检索大量数据时，我不能真正推荐其中任何一种。

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.

最好的解决方案是指定一个显式的 Select 调用。但是，在不知道您的 viewmodel 对象是如何构建的情况下，为您提供有关如何执行此操作的示例有点困难。所以，我在这里做的是给你一个使用匿名对象作为查询结果的例子。

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.

此查询将生成一个 SQL SELECT，它使用内部联接将 Contacts 与 Customer 联接起来，然后只选择 Contact.Id、Contact.Name 和 Customer.Name 列。

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.

如果您不打算使用数据并将更改保存回相同的上下文，则此解决方案是最有效的从服务器检索数据的方法。它既不使用急切加载，也不使用延迟加载。

回答by Anand

Eager loading fetches redundant master data. It will take lots of memory, though object graph in context stores only single master data per entity, but SQL will dump lots of data in it's plate. I took following image from here

急切加载获取冗余主数据。这将需要大量内存，尽管上下文中的对象图每个实体只存储单个主数据，但 SQL 会在它的盘子中转储大量数据。我从这里拍摄了以下图片

If you see, Data of User table also repeated as many as UserDetails table in result set of SQL query. That seem to differentiating factor in performance (In your case master columns has more records then detail table).

如果您看到，在 SQL 查询的结果集中，User 表的 Data 也与 UserDetails 表重复的次数相同。这似乎是性能的差异化因素（在您的情况下，主列的记录多于详细表）。

If performance is your major concern, I would recommend You to use LINQ joinwith same where clause while fetching data for detail table separatelySo in your case :-

如果性能是您的主要关注点，我建议您在单独获取详细信息表的数据时使用具有相同 where 子句的LINQ join所以在您的情况下：-

step1

第1步

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

and then step2

然后 步骤2

var solicitors = from s in context.Solicitors
join loanApp in context.LoanApplications
select s.columns
where loanApp. <<Same condition as in step 1 where clause>>

Thanks, your question made me to review my own code :-)

谢谢，你的问题让我检查自己的代码:-)

回答by Sleeper Smith

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

1. 设置！应用广泛！绩效目标
2. 配置文件、基准和定位瓶颈
3. 查看并微调瓶颈，让您以最少的工作获得最大的性能。（根据我的经验，90% 的时间不是 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.

没有急切/懒惰的“最佳实践”。这就是为什么这两个选项都可用的原因。此外，如果 tsql 是您的瓶颈，并且在急切/懒惰之间切换仍然没有达到您的性能目标，您将需要使用大量其他工具，例如 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

急切加载：106ms

Lazy loading: 11ms + 5ms + 5ms

延迟加载：11ms + 5ms + 5ms

Lazy loading wins, end of story.

延迟加载获胜，故事结束。