c# - 改进大型EF多级包含的性能

Question

我是EF新手（就像今天才刚开始，我只使用过其他ORM），并且正在经历火的洗礼。

我被要求改善另一个开发人员创建的此查询的性能：

      var questionnaires = await _myContext.Questionnaires
            .Include("Sections")
            .Include(q => q.QuestionnaireCommonFields)
            .Include("Sections.Questions")
            .Include("Sections.Questions.Answers")
            .Include("Sections.Questions.Answers.AnswerMetadatas")
            .Include("Sections.Questions.Answers.SubQuestions")
            .Include("Sections.Questions.Answers.SubQuestions.Answers")
            .Include("Sections.Questions.Answers.SubQuestions.Answers.AnswerMetadatas")
            .Include("Sections.Questions.Answers.SubQuestions.Answers.SubQuestions")
            .Include("Sections.Questions.Answers.SubQuestions.Answers.SubQuestions.Answers")
            .Include("Sections.Questions.Answers.SubQuestions.Answers.SubQuestions.Answers.AnswerMetadatas")
            .Include("Sections.Questions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions")
            .Include("Sections.Questions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions.Answers")
            .Include("Sections.Questions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions.Answers.AnswerMetadatas")
            .Include("Sections.Questions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions")
            .Include("Sections.Questions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions.Answers")
            .Include("Sections.Questions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions.Answers.AnswerMetadatas")
            .Include("Sections.Questions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions")
            .Include("Sections.Questions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions.Answers")
            .Include("Sections.Questions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions.Answers.AnswerMetadatas")
        .Where(q => questionnaireIds.Contains(q.Id))
        .ToListAsync().ConfigureAwait(false);

快速的网上冲浪告诉我，如果您深入运行多个级别，则Include（）会导致cols *行产品和较差的性能。

我在SO上看到了一些有用的答案，但是它们仅提供了一些不太复杂的示例，并且我无法找出重写上述示例的最佳方法。

该部分的多次重复-“ Sections.Questions.Answers.SubQuestions.Answers.SubQuestions.Answers ...”对我来说似乎很可疑，因为它可以单独完成，然后发出另一个查询，但我不知道如何构建或这种方法是否甚至可以提高性能。

问题：


如何在确保最终结果集相同的情况下，将该查询重写为更明智的方法，以提高性能？
给定最后一行： .Include("Sections.Questions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions.Answers.SubQuestions.Answers.AnswerMetadatas")
为什么需要所有中间线？ （我猜这是因为某些联接可能不是左联接？）


EF版本信息：包id =“ EntityFramework” version =“ 6.2.0” targetFramework =“ net452”

我意识到这个问题有点废话，但是我正尝试从无知的角度尽快解决。


  编辑


经过半天的思考，并感谢StuartLC的建议，我提出了一些选择：

效果差-拆分查询，使其执行多次往返以获取数据。这可能会为用户提供稍慢的体验，但是将停止SQL超时。 （这并不比增加EF命令超时好多少）。

很好-更改子表上的聚集索引以使其父表的外键聚集（假设您没有很多插入操作）。

很好-更改代码以仅查询前几个级别并延迟加载低于此级别的任何内容，即删除除前几个Includes之外的所有内容，然后更改ICollections-Answers.SubQuestions，Answer.Metadatas和Question。答案都是虚拟的。大概使这些虚拟化的不利之处在于，如果应用程序中的任何（其他）现有代码都希望这些ICollection属性被急切加载，则您可能必须更新该代码（即，如果希望/需要它们立即在该代码中加载） ）。我将进一步研究此选项。进一步编辑-不幸的是，如果由于自引用循环而需要序列化响应，则此方法将无效。

不平凡-手动编写一个sql存储的proc / view并建立一个指向它的新EF对象。

长期的

显而易见的，最好的，但最耗时的选项-重写应用程序设计，因此它不需要在单个api调用中就包含整个数据树，也可以使用以下选项：

重写应用程序以NoSQL方式存储数据（例如，将对象树存储为json，因此不存在联接）。正如Stuart所提到的，如果您需要以其他方式（通过问卷调查表ID以外的方式）过滤数据，则这不是一个好选择，您可能需要这样做。另一种选择是根据需要部分存储NoSQL样式和部分关系。

Answer 1

首先，必须说这不是一个简单的查询。看来我们有：


通过嵌套的问答树进行6级递归
通过急切加载的.Include以这种方式总共连接了20个表


首先，我将花时间确定此查询在您的应用中的使用位置以及需要使用的频率，尤其要注意最常使用的位置。

YAGNI优化

最明显的起点是查看查询在您的应用程序中的使用位置，如果您一直不需要整棵树，那么建议您不要加入嵌套的问题和答案表（如果不需要）查询的所有用法。

另外，可以动态地在IQueryable上进行组合，因此，如果您的查询有多个用例（例如，从不需要问题和答案的“摘要”屏幕以及需要它们的详细信息树中） ，那么您可以执行以下操作：

var questionnaireQuery = _myContext.Questionnaires
        .Include(q => q.Sections)
        .Include(q => q.QuestionnaireCommonFields);

// Conditionally extend the joins
if (mustIncludeQandA)
{
     questionnaireQuery = questionnaireQuery
       .Include(q => q.Sections.Select(s => s.Questions.Select(q => q.Answers..... etc);
}

// Execute + materialize the query
var questionnaires = await questionnaireQuery
    .Where(q => questionnaireIds.Contains(q.Id))
    .ToListAsync()
    .ConfigureAwait(false);

SQL优化

如果确实需要始终获取整个树，请查看您的SQL表设计和索引。

1）过滤器

.Where(q => questionnaireIds.Contains(q.Id))

（我在这里假设使用SQL Server术语，但是这些概念也适用于大多数其他RDBM。）

我猜 Questionnaires.Id是集群的主键，因此将被索引，但只是检查是否合理（在SSMS中看起来会是 PK_Questionnaires CLUSTERED UNIQUE PRIMARY KEY）

2）确保所有子表在其外键上都有返回到父表的索引。

例如 q => q.Sections表示表 Sections具有返回到 Questionnaires.Id的外键-确保该表上至少具有非聚集索引-EF代码首先应自动执行此操作，但再次进行检查以确保正确。

看起来像列 IX_QuestionairreId NONCLUSTERED上的 Sections(QuestionairreId)

3）考虑更改子表上的聚簇索引，以通过其父级的外键聚类。通过 Section群集 Questions.SectionId。这样会将与同一父级相关的所有子行保持在一起，并减少了SQL需要获取的数据页数。 It isn't trivial首先要在EF代码中实现，但您的DBA可以帮助您完成此操作，这也许是自定义步骤。

其他的建议

如果此查询仅用于查询数据，而不用于更新或删除，则添加 .AsNoTracking()将在一定程度上减少EF的内存消耗和内存性能。

与性能无关，但是您将弱类型（“ Sections”）和强类型 .Include语句（ q => q.QuestionnaireCommonFields）混合在一起。我建议改用强类型包含，以提高编译时的安全性。

请注意，您只需要为渴望加载的最长链指定包含路径-这显然会迫使EF也包含所有更高级别。即您可以将20个 .Include语句减少到2个。这将更有效地完成相同的工作：

.Include(q => q.QuestionnaireCommonFields)
.Include(q => q.Sections.Select(s => s.Questions.Select(q => q.Answers .... etc))

每当出现1：1：1关系时，您都需要 .Select，但是如果导航为1：1（或N：1），则不需要.Select，例如 City c => c.Country

重新设计

最后但并非最不重要的一点是，如果仅从顶层（即 Questionnaires）过滤数据，并且通常整个一次都添加或更新整个Questionairre“树”（聚合根），那么您可以尝试以 NoSQL方式对问题和答案树进行数据建模，例如只需将整个树建模为XML或JSON，然后将整个树视为长字符串即可。这将完全避免所有令人讨厌的连接。您将需要在数据层中执行自定义反序列化步骤。如果您需要从树中的节点进行过滤，则后一种方法将不是很有用（例如，像“查找我”之类的查询，所有Questionairre的问题5的SubAnswer为“ Foo”都不适合）