优化实体框架查询，避免延迟加载

问题描述

我有一个LINQ查询需要几秒钟（〜2.6S）运行。但我要减少到尽可能少的。

I have a linq query that takes a couple of seconds(~2.6s) to run. But i want to reduce that to as little as possible.

我只需要读取，所以我已经包括了.AsNoTracking（）的行为。

I only require reading so I've included the .AsNoTracking() behavior.

我也测试没有包括报表的查询，但GET请求后，我的行动慢了进一步的，所以我离开了包括优化我的其他操作。

I have also tested the query without include statements, but my operations after the get request slowed it down further so i left the includes to optimize my other operations.

的主要目的是为了减少数据库的谈话，为此在了ToList（），包括语句

代码：

var obj = _context.MyContextModel.AsNoTracking()
            .Where(x => x.CategoryList.Model.Id == 1)
            .Where(x => x.CategoryList.Model.TypeId == 1)
            .Where(x => x.Year.Select(y=>y.Datetime).Any(item => item.Year == 2010))
            .Include(x => x.LinkedMarket).AsNoTracking()
            .Include(x => x.Year).AsNoTracking()
            .Include(x => x.CategoryList).AsNoTracking()
            .Include(x => x.CategoryList.Model).AsNoTracking();

return obj.AsParallel().ToList();

这操作通常会返回约1000-2000 MyContextModel记录，不包括包括

This operation usually returns about 1000-2000 MyContextModel records, not including the "includes"

如何进一步优化呢？
我应该加载对象容器类？还是另一种解决方案？

How can i optimize this further?Should i load objects to a container class? or another solution?

更新

_context.Configuration.ProxyCreationEnabled = false;
_context.Configuration.LazyLoadingEnabled = false;
var obj = _context.MyContextModel.AsNoTracking()
                .Where(x => x.CategoryList.Model.Id == 1)
                .Where(x => x.CategoryList.Model.TypeId == 1)
                .Where(x => x.LinkedMarket.FirstOrDefault(mar=>mar.MarketID == marketId) != null)
                .Include(x => x.Year).AsNoTracking()
                .Include(x => x.CategoryList).AsNoTracking()
                .Include(x => x.CategoryList.Model).AsNoTracking();

return obj.AsParallel().ToList();

基本上我已经删除where子句该过滤器在今年（我这样做后，为此该包括一年）
我添加了一个WHERE子句从getgo指定市场。

Basically i've removed where clause which filter the year(I do that later, therefor the include of the year)I've added a Where clause that specify market from the getgo.

我已经删除了包含其中载有市场。

I've removed the Include which contained market.

一大表现贼，是联系市场（我不知道为什么，EF的东西不喜欢。）

One big performance thief was the Linked market(i don't know exactly why, something EF didn't like.)

这减少了查询有关0,4秒平均值。
和整个操作由4+秒设置为惊人的0.7秒。

This reduced the query to about a average on 0,4 seconds.And the whole operation set from 4+ seconds to a stunning 0,7 seconds.

推荐答案

每个包括你做将最终在一个数据库中的联接执行。
假设你的左表中记录的大小非常大的1024个字节，你有许多细节，比如1000，并且详细记录尺寸仅为100
这将导致信息为左表要重复1000次，这个信息是要由db被放置在导线和EF具有滤除重复来创建左实例

Each include you do will end up with a join being executed in the db.Suppose your left table is very big 1024 bytes in record size and that you have many details, say 1000 and and that the detail record size is only 100.This will result in the information for the left table to be repeated 1000 times, this information is going to be put on the wire by the db and EF has to filter out the duplicated to create your left instance.

它可以会更好不要使用包括，做一个明确的负荷。基本上执行在同样的情况下2查询。

It can be better to not use include and do an explicit load. Basically executing 2 queries on the same context.

下面我利用这个原理有一个例子。它可以比依赖于包括更快的高达10倍。 （一个数据库只能处理limitited一些有效的BTW连接）

I have an example using this principle below. It can be up to 10 times faster than relying on include. (A db can handle only a limitited number of joins efficiently btw)

var adressen = adresRepository
                .Query(r => r.RelatieId == relatieId)
                .Include(i => i.AdresType)
                .Select().ToList();

var adresids = (from a in adressen select a.AdresId).ToList();
            IRepositoryAsync<Comm> commRepository = unitOfWork.RepositoryAsync<Comm>();

            var comms = commRepository
                .Query(c => adresids.Contains(c.AdresId))
                .Include(i => i.CommType)
                .Select();

有关的COMMTYPE和adresType我使用包括因为有一个1对1的关系，我避免太很多连接，因此我多次查询会比使用包括单一的速度更快。我不包括在第一个查询的通讯科尽量避免第二个查询，问题是，2查询是在这种情况下多单的速度更快。

For the commType and adresType I use include because there is a 1 to 1 relationship, I am avoiding too many joins and thus my multiple queries will be faster than a single one using include. I am not including the Comms in the first query to try and avoid the second query, the point is that 2 queries are faster in this case than a single one.

底线有更多的考虑不仅仅是避免延迟加载，也有大约需要其中包括需要和一些考虑不。您可能需要的信息，以及包括快速和容易的，但在同样的情况下额外的查询可以更快。

Bottom line is there is more to consider than just avoiding lazy load, there is also some consideration needed about which includes are needed and which are not. You might need that information, and an include is quick and easy, but an extra query in the same context can be faster.

这篇关于优化实体框架查询，避免延迟加载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！