多次使用Include()时,实体框架代码很慢

我一直在调试一些慢速代码,似乎罪魁祸首是下面发布的EF代码.在稍后阶段评估查询时需要4-5秒.我试图让它在1秒内运行.

我使用SQL Server Profiler对此进行了测试,似乎执行了一堆SQL脚本.它还确认SQL Server完成执行需要3-4秒.

我已经阅读了有关使用Include()的其他类似问题,并且在使用它时似乎确实存在性能损失.我试图将下面的代码分成几个不同的查询,但它并没有太大的区别.

知道我怎么能让下面更快地执行？

目前我正在处理的网络应用程序只是在等待以下内容完成时显示一个空的iframe.如果我无法获得更快的执行时间,我必须将其拆分并部分加载iframe数据或使用其他异步解决方案.这里的任何想法也将不胜感激!

using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
        {
            formInstance = context.FormInstanceSet
                                .Includes(x => x.Include(fi => fi.FormDefinition).Include(fd => fd.FormSectionDefinitions).Include(fs => fs.FormStateDefinitionEditableSections))
                                .Includes(x => x.Include(fi => fi.FormDefinition).Include(fd => fd.FormStateDefinitions))
                                .Includes(x => x.Include(fi => fi.FormSectionInstances).Include(fs => fs.FormFieldInstances).Include(ff => ff.FormFieldDefinition).Include(ffd => ffd.FormFieldMetaDataDefinition).Include(ffmdd => ffmdd.ComplexTypePropertyNames))
                                .Include(x => x.CurrentFormStateInstance)      
                                .Include(x => x.Files)
                                .FirstOrDefault(x => x.FormInstanceIdentifier == formInstanceIdentifier);

            scope.Complete();
        }

Gert Arnold.. 57

使用Include时似乎确实存在性能损失

这是轻描淡写!多个Includes在宽度和长度上快速爆炸SQL查询结果.这是为什么？

tl; dr Multiple Includes炸掉了SQL结果集.很快,通过多个数据库调用加载数据而不是运行一个mega语句变得更便宜.尝试找到最好的混合Include和Load陈述.

生长因子Includes

让我们说我们有

根实体 Root

父实体 Root.Parent

子实体Root.Children1和Root.Children2

一个LINQ语句 Root.Include("Parent").Include("Children1").Include("Children2")

这将构建一个具有以下结构的SQL语句:

SELECT *, 
FROM Root
JOIN Parent
JOIN Children1

UNION

SELECT *, 
FROM Root
JOIN Parent
JOIN Children2

context.Configuration.LazyLoadingEnabled = false;
var rootId = 1;
context.Children1.Where(c => c.RootId == rootId).Load();
context.Children2.Where(c => c.RootId == rootId).Load();
return context.Roots.Find(rootId);

SELECT *, 
FROM Root
JOIN Parent
JOIN Children1

UNION

SELECT *, 
FROM Root
JOIN Parent
JOIN Children2

context.Configuration.LazyLoadingEnabled = false;
var rootId = 1;
context.Children1.Where(c => c.RootId == rootId).Load();
context.Children2.Where(c => c.RootId == rootId).Load();
return context.Roots.Find(rootId);