实体框架包括绩效 [英] Entity Framework Include performance

问题描述

我一直在关注Entity Framework的性能，尤其是在使用 Includes 以及生成和执行各种查询所花费的时间周围。

I have been looking at Entity Framework performance, particularly around the use of Includes and the time taken to both generate and execute the various queries.

我将详细说明所做的更改，但如果您认为这些假设有误，请更正我的意思。

I am going to detail changes I have made, but please correct me if you think any of these assumptions are wrong.

首先，我们有大约10,000个项目（不是db）中，数据库已显着规范化（这导致大量的导航属性）。当前，该方法是延迟加载所有内容，并且鉴于请求一项可以缓冲数十个数据库请求，因此性能相当差，尤其是对于较大的数据集。
（这是一个继承的项目，第一步正在尝试在不进行重大重组的情况下提高性能）

Firstly we have around 10,000 items (not many) in a db and the database is significantly normalised (which results in a significant number of navigation properties). Currently the approach is to lazy load everything and given that requesting one item can spool off tens of db requests, the performance is quite poor, particularly for larger sets of data. (This is an inherited project and step one is trying to improve performance without significant restructuring)

所以我的第一步是获取查询结果并然后将导航属性的包含应用于这些结果。
我知道这从技术上讲会执行2个查询，但是如果我们存储了10,000个项目，但只想返回10个项目，则仅在这10个项目上包含导航属性就更有意义。

So my first step was to take the results of a query and then apply the Includes for the navigation properties only to those results. I know this technically performs 2 queries, but if we have 10,000 items stored, but only want to return 10 items, it makes more sense to only include the navigation properties on those 10 items.

其次，在查询结果上使用多个包含并且结果集大小很大的情况下，它仍然遭受性能不佳的困扰。对于什么时候渴望加载以及何时将延迟加载保留在原处，我一直很务实。
我的下一个更改是批量加载查询包含，因此执行：

Secondly, where multiple includes are used on a query result and that result set size is quite large, it still suffered from poor performance. I have been pragmatic about when to eager load and when to leave the lazy loading in place. My next change was to load query includes in batches, so performing:

query.Include（q => q.MyInclude ）.Load（）;

这再次显着提高了性能，尽管它进行了更多的数据库调用（每批包含一个）比大型查询更快，或者至少减少了尝试生成大型查询的Entity Framework的开销。

This once again significantly improved performance, although a few more db calls (one for each batch of includes) it was quicker than a large query or at the very least reduced the overhead of of Entity Framework trying to produce that large query.

所以代码现在看起来像这样：

So the code now looks something like this:

    var query = ctx.Filters.Where(x => x.SessionId == id)
        .Join(ctx.Items, i => i.ItemId, fs => fs.Id, (f, fs) => fs);
    query
        .Include(x => x.ItemNav1)
        .Include(x => x.ItemNav2).Load();

    query
        .Include(x => x.ItemNav3)
        .Include(x => x.ItemNav4).Load();

    query
        .Include(x => x.ItemNav5)
        .Include(x => x.ItemNav6).Load();

现在，这是合理的表现，但是，进一步改善它会很好。

Now this is reasonably performant, however, it would be nice to improve this further.

我曾考虑过使用 LoadAsync（），在进行更多重构之后，它可能会更好，并且更适合

I had considered using LoadAsync(), which after a bit more refactoring would be possible and it would better fit with the rest of the architecture.

但是，您一次只能在db上下文中执行一个查询。因此，我想知道是否有可能创建新的数据库上下文，对每组导航属性（异步）执行 LoadAsync（），然后将所有结果连接起来。

However, you can only execute one query at a time on a db context. So I was wondering if there was anyway to possible create a new db context, perform LoadAsync() on each group of navigation properties (asynchronously) and then concatenate all of the results.

从技术上讲，我知道如何创建新上下文，为每个导航组触发 LoadAsync（），

I know technically how you might create a new context, fire off a LoadAsync() for each navigation group, but not how to concatenate the results, I don't know if it is definitely possible or whether it goes against good practice.

所以我的问题是；但我不知道是否可以将结果串联起来。这是否可能，或者还有其他方法可以进一步提高性能吗？我尝试使用Entity Framework提供的功能，而不是精心制作一些存储的proc。谢谢

So my question is; is this possible or, is there another way I can further improve performance? I'm trying to stick with what Entity Framework provides rather than crafting some stored procs. Thanks

更新

关于性能差异，我发现所有使用包含在一个语句中，并将它们装入小组。运行查询时返回6000个项目。 （使用SQL事件探查器和VS诊断程序确定时间）

Regarding the performance disparity I'm seeing between using all Includes in one statement and Loading these in small groups. When running a query that returns 6000 items. (Using SQL profiler and VS diagnostics to determine times)

分组包含：总共需要大约8秒钟来执行包含。

Grouped Includes: In total takes ~8 seconds to execute the includes.

包含在一条语句中：SQL查询加载大约需要30秒。 （经常会超时）

Includes in one statement: SQL query is taking ~30 seconds to load. (Often getting timeouts)

经过更多调查后，我认为EF将sql结果转换为模型时并没有太多开销。但是，我们已经看到EF将近500ms的时间来生成复杂的查询，这并不理想，但是我不确定这是否可以解决

After a bit more investigation, I don't think there is much overhead when EF converts the sql results to models. However we have seen nearly 500ms taken for EF to generate complex queries, which isn't ideal, but I'm not sure this can be resolved

UPDATE 2

在Ivan的帮助下并遵循 https://msdn.microsoft.com/en-gb/data/hh949853.aspx 我们能够进一步改进，尤其是使用 SelectMany 。我会向所有试图提高其EF性能的人强烈推荐msdn文章。

With Ivan's help and following this https://msdn.microsoft.com/en-gb/data/hh949853.aspx we were able to improve things further, particularly using SelectMany. I would highly recommend the msdn article to anyone attempting to improve their EF performance.

推荐答案

您的第二种方法依赖于EF导航属性修复过程。问题是尽管每个

Your second approach relies on the EF navigation property fixup process. The problem is though that every

query.Include(q => q.ItemNavN).Load();

声明还将包括所有主记录数据以及相关的实体数据。

statement will also include all the master record data along with the related entity data.

使用相同的基本思想，一项潜在的改进可能是对每个导航属性执行一次 Load 来替换包括和 Select （用于引用）或 SelectMany （用于集合）-类似于EF Core在内部处理 Include 的方式。

Using the same basic idea, one potential improvement could be to execute one Load per each navigation property, replacing the Include with either Select (for references) or SelectMany (for collections) - something similar to how EF Core processes the Includes internally.

以第二个方法示例为例，您可以尝试以下操作并比较性能：

Taking your second approach example, you could try the following and compare the performance:

var query = ctx.Filters.Where(x => x.SessionId == id)
    .Join(ctx.Items, i => i.ItemId, fs => fs.Id, (f, fs) => fs);

query.Select(x => x.ItemNav1).Load();
query.Select(x => x.ItemNav2).Load();
query.Select(x => x.ItemNav3).Load();
query.Select(x => x.ItemNav4).Load();
query.Select(x => x.ItemNav5).Load();
query.Select(x => x.ItemNav6).Load();

var result = query.ToList();
// here all the navigation properties should be populated 

这篇关于实体框架包括绩效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！