使用实体框架提高大型查询的性能

Question

我有一个应用程序，它根据价格进行大量计算，此时需要大约87秒。根据时间表，我怀疑我使用我的包含的方式对Entity Framework来说是次优的，我可以做出重大改进。

我的时间线是这样的：

• 0秒 - 接收GET请求
• 2秒 - 发送查询到SQL Server（带有所有必要incudes查询， 其结果是> 40.000字符。我在 底部的问题）
• 5秒添加某些部分 - 从SQL Server接收查询结果
• 45一些线程正在退出
• 46，从而我线程退出
• 50某个线程正在退出
• 80收盘SQL连接（我 认为，它需要EF6〜75秒钟，将查询结果映射回我的C＃类，但我不知道）
• 80开始计算
• 87所有的计算都做

查询设计大多这样

var result = await db.MyAuthorizedFooExtension() 
    .Include(x => x.Foofie) 
    .Include(x => x.Bar.Baz) 
    .Include(x => x.FooFoo.Select(y => y.BarBar)) 
    .Include(x => x.FooFoo.Select(y => y.BazBaz)) 
//etc. 
    .ToListAsync() 

结果如下所示：

[UnionAll7].[C9] AS [C55], 
... 
    FROM (SELECT 
     CASE WHEN ([Join2].[ID1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], 
     [Extent1].[ID] AS [ID], 
... 
     [Join2].[ID1] AS [ID5], 
... 
     CAST(NULL AS varchar(1)) AS [C4], 
... 
     FROM [dbo].[Contract] AS [Extent1] 
     INNER JOIN [dbo].[Leverancier] AS [Extent2] ON [Extent1].[Leverancier] = [Extent2].[ID] 
     LEFT OUTER JOIN (SELECT [Extent3].[ID] AS [ID1], [Extent3].[Contract] AS [Contract], [Extent3].[ContractStatusOud] AS [ContractStatusOud], [Extent3].[ContractStatusNieuw] AS [ContractStatusNieuw], [Extent3].[MutatieDatumTijd] AS [MutatieDatumTijd], [Extent3].[Medewerker] AS [Medewerker], [Extent3].[Status] AS [Status1], [Extent3].[LastModifiedDate] AS [LastModifiedDate1], [Extent3].[LastModifiedBy] AS [LastModifiedBy1], [Extent4].[ID] AS [ID2], [Extent4].[Naam] AS [Naam], [Extent4].[WindowsNaam] AS [WindowsNaam], [Extent4].[Rol] AS [Rol], [Extent4].[Unit] AS [Unit], [Extent4].[Status] AS [Status2], [Extent4].[LastModifiedDate] AS [LastModifiedDate2], [Extent4].[LastModifiedBy] AS [LastModifiedBy2] 
      FROM [dbo].[ContractWijzigingHistorie] AS [Extent3] 
      INNER JOIN [dbo].[Medewerker] AS [Extent4] ON [Extent3].[Medewerker] = [Extent4].[ID]) AS [Join2] ON [Extent1].[ID] = [Join2].[Contract] 
     WHERE (EXISTS (SELECT 
      1 AS [C1] 
      FROM [dbo].[Contract] AS [Extent5] 
      WHERE (EXISTS (SELECT 
       1 AS [C1] 
       FROM [dbo].[Leverancier] AS [Extent6] 
       WHERE [Extent6].[ID] = [Extent5].[Leverancier] 
      )) AND ([Extent5].[ID] = [Extent1].[ID]) 
     )) AND ([Extent1].[Status] IN (1)) AND ((DATEPART (year, [Extent1].[Startdatum])) <= @p__linq__0) AND ((DATEPART (year, [Extent1].[Einddatum])) >= @p__linq__1) 
    UNION ALL 
     SELECT 
     2 AS [C1], 
     [Extent7].[ID] AS [ID], 
... 
     CAST(NULL AS int) AS [C9], 
     CAST(NULL AS datetime2) AS [C10], 
... 
     [Extent9].[PeriodeWaarde] AS [PeriodeWaarde], 
... 
     [Project9].[LastModifiedBy] AS [LastModifiedBy], 
... 
     [UnionAll5].[ID2] AS [C60], 
... 
     CAST(NULL AS int) AS [C62], 
... 
etc. 

如何在仍使用实体框架的同时提高性能？

Answer 1

这引出了在获得查询结果后，你在做什么的问题？如果你读出你的查询，你会看到你正在获得所有的结果，同时热切地加入相关的表格。

如果您在从查询中检索数据后过滤掉这些数据，那么您应该以在实现数据之前过滤数据（调用ToList）的方式编写查询。你还需要打电话给相关的表吗？您也应该只从数据库中选择您知道您将使用的列。

现在，再次知道你想要你的方法做什么会更容易。创建一个新的对象或一个匿名对象来选择：

var result = await db.MyAuthorizedFooExtension() 
//etc. 
.Select(s => new 
{ 
    Id = s.Id, 
    FooName = s.FooName, 
    BazId = s.Bar.Baz.Id 
} 
.ToListAsync(); 

并物化到最后。如果在可选场景中需要包含，请将查询设置为IQueryable，最后执行逻辑并实现。