实体框架执行查询非常非常慢
本文关键字：非常 查询 执行 框架 实体 | 更新日期: 2023-09-27 18:19:20
我使用6.1.1的实体框架对SQL Server 2012。
我把有问题的查询附在这个问题上。
当直接对我们的数据库运行时(即从SQL生成)，该查询以<15毫秒。
然而，当在实体框架中处理时，它是~650毫秒到投影物化(因此没有跟踪导致问题)
此外，当使用Web API和Odata做前100名时，它甚至更慢。(例如，如果我首先在Web API控制器的端点上对结果执行. toarray()，它比通过odata过滤前100个查询要快。
更糟糕的是，oData的内联查询导致实体框架生成的计数上的组比，这是奇数，需要<在服务器上处理10ms，但实体框架处理完整选择所需的时间与处理完整选择所需的时间相同，从而使服务时间翻倍。>
我可以理解一些开销，但是我们讨论的是100条记录到一个投影中，处理这个不应该超过半秒。
此外，考虑到where子句(使用谓词生成)的复杂性，我本以为第二次执行这个操作时，由于EF 5+中的动态缓存，它会大大加快速度，但它根本没有任何区别。我可以为你提供它投射到的DTO，如果它重要的话，但它只是字符串和datetimeoffset直接没有计算或任何东西。
还要注意所有的测试都是在发布模式下完成的，而不是在调试模式下。计时器用于在自定义格式化器中隔离两个独立的查询，并计算实体框架为它们提供服务所需的时间。
我下到DB级别，创建了一个DB阅读器，并通过结果循环并手动创建dto使用(所有的，而不是过滤的)使用生成的完全相同的查询，只是为参数输入不同的值，并在<30毫秒作为参考。
如果您希望我以某种方式为您提供生成此谓词的构建谓词，请让我知道并给我指示，我会很乐意这样做。
下面是结果查询(同样，在30毫秒内运行，但实体框架需要2.5秒来产生结果，并通过WebAPI将其发送出去):
SELECT TOP (@p__linq__15)
[Project45].[Status] AS [Status],
[Project45].[C6] AS [C1],
[Project45].[C7] AS [C2],
[Project45].[C1] AS [C3],
[Project45].[C8] AS [C4],
[Project45].[Priority] AS [Priority],
[Project45].[C9] AS [C5],
[Project45].[Name1] AS [Name],
[Project45].[C10] AS [C6],
[Project45].[Name2] AS [Name1],
[Project45].[C11] AS [C7],
[Project45].[C12] AS [C8],
[Project45].[C4] AS [C9],
[Project45].[C13] AS [C10],
[Project45].[C5] AS [C11],
[Project45].[C14] AS [C12],
[Project45].[C3] AS [C13],
[Project45].[C15] AS [C14],
[Project45].[C2] AS [C15],
[Project45].[C16] AS [C16],
[Project45].[Identifier] AS [Identifier],
[Project45].[C17] AS [C17],
[Project45].[Name] AS [Name2],
[Project45].[C18] AS [C18],
[Project45].[ID] AS [ID]
FROM ( SELECT
[Filter1].[ID1] AS [ID],
[Filter1].[Identifier] AS [Identifier],
[Filter1].[Name1] AS [Name],
[Filter1].[Priority] AS [Priority],
[Filter1].[Status] AS [Status],
[Filter1].[Name2] AS [Name1],
[Filter1].[Name3] AS [Name2],
CAST( [Filter1].[DueDate] AS datetimeoffset) AS [C1],
CAST( CASE WHEN ([Filter1].[CancelledOn] IS NULL) THEN CASE WHEN ([Filter1].[RejectedOn1] IS NULL) THEN CASE WHEN ([Filter1].[SubmittedOn] IS NULL) THEN [Filter1].[CreatedOn] ELSE [Filter1].[SubmittedOn] END ELSE [Filter1].[RejectedOn1] END ELSE [Filter1].[CancelledOn] END AS datetimeoffset) AS [C2],
CASE WHEN ([Filter1].[CancelledByID] IS NOT NULL) THEN [Filter1].[UserName1] WHEN ([Filter1].[RejectedByID1] IS NOT NULL) THEN [Filter1].[UserName2] WHEN ([Filter1].[SubmittedByID] IS NOT NULL) THEN [Filter1].[UserName3] ELSE [Filter1].[UserName4] END AS [C3],
CASE WHEN ([Filter1].[FirstName1] IS NULL) THEN N'' ELSE [Filter1].[FirstName2] END + N' ' + CASE WHEN ([Filter1].[LastName1] IS NULL) THEN N'' ELSE [Filter1].[LastName2] END AS [C4],
CAST( [Filter1].[SubmittedOn] AS datetimeoffset) AS [C5],
N'c4eabbaf-7433-430d-bdd7-2f9180bce7cc' AS [C6],
N'DueDate' AS [C7],
N'Priority' AS [C8],
N'Project' AS [C9],
N'RequestType' AS [C10],
N'Status' AS [C11],
N'SubmittedByName' AS [C12],
N'SubmittedOn' AS [C13],
N'MajorEventBy' AS [C14],
N'MajorEventDate' AS [C15],
N'Identifier' AS [C16],
N'Name' AS [C17],
N'ID' AS [C18]
FROM ( SELECT [Extent1].[ID] AS [ID1], [Extent1].[Identifier] AS [Identifier], [Extent1].[Name] AS [Name1], [Extent1].[Priority] AS [Priority], [Extent1].[DueDate] AS [DueDate], [Extent1].[CreatedOn] AS [CreatedOn], [Extent1].[CreatedByID] AS [CreatedByID], [Extent1].[SubmittedOn] AS [SubmittedOn], [Extent1].[SubmittedByID] AS [SubmittedByID], [Extent1].[RejectedOn] AS [RejectedOn1], [Extent1].[RejectedByID] AS [RejectedByID1], [Extent1].[CancelledOn] AS [CancelledOn], [Extent1].[CancelledByID] AS [CancelledByID], [Extent1].[Private] AS [Private], [Extent1].[Status] AS [Status], [Extent2].[UserName] AS [UserName1], [Extent3].[UserName] AS [UserName2], [Extent4].[UserName] AS [UserName3], [Extent5].[UserName] AS [UserName4], [Extent6].[Name] AS [Name2], [Extent7].[Name] AS [Name3], [Extent8].[FirstName] AS [FirstName1], [Extent9].[FirstName] AS [FirstName2], [Extent10].[LastName] AS [LastName1], [Extent11].[LastName] AS [LastName2]
FROM [dbo].[Requests] AS [Extent1]
LEFT OUTER JOIN [dbo].[Users] AS [Extent2] ON [Extent1].[CancelledByID] = [Extent2].[ID]
LEFT OUTER JOIN [dbo].[Users] AS [Extent3] ON [Extent1].[RejectedByID] = [Extent3].[ID]
LEFT OUTER JOIN [dbo].[Users] AS [Extent4] ON [Extent1].[SubmittedByID] = [Extent4].[ID]
INNER JOIN [dbo].[Users] AS [Extent5] ON [Extent1].[CreatedByID] = [Extent5].[ID]
INNER JOIN [dbo].[Projects] AS [Extent6] ON [Extent1].[ProjectID] = [Extent6].[ID]
INNER JOIN [dbo].[RequestTypes] AS [Extent7] ON [Extent1].[RequestTypeID] = [Extent7].[ID]
LEFT OUTER JOIN [dbo].[Users] AS [Extent8] ON [Extent1].[SubmittedByID] = [Extent8].[ID]
LEFT OUTER JOIN [dbo].[Users] AS [Extent9] ON [Extent1].[SubmittedByID] = [Extent9].[ID]
LEFT OUTER JOIN [dbo].[Users] AS [Extent10] ON [Extent1].[SubmittedByID] = [Extent10].[ID]
LEFT OUTER JOIN [dbo].[Users] AS [Extent11] ON [Extent1].[SubmittedByID] = [Extent11].[ID]
WHERE [Extent1].[isDeleted] <> cast(1 as bit)
) AS [Filter1]
WHERE (( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[AclDataMarts] AS [Extent12]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[SecurityGroupUsers] AS [Extent13]
WHERE ([Extent12].[SecurityGroupID] = [Extent13].[SecurityGroupID]) AND ([Extent13].[UserID] = @p__linq__0)
)) AND ([Extent12].[PermissionID] IN (cast('5d6dd388-7842-40a1-a27a-b9782a445e20' as uniqueidentifier), cast('a58791b5-e8af-48d0-b9cd-ed0b54e564e6' as uniqueidentifier), cast('0cabf382-93d3-4dac-aa80-2de500a5f945' as uniqueidentifier))) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[RequestDataMarts] AS [Extent14]
WHERE ([Extent12].[DataMartID] = [Extent14].[DataMartID]) AND ([Extent14].[RequestID] = [Filter1].[ID1])
))
)) OR ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[AclProjects] AS [Extent15]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[SecurityGroupUsers] AS [Extent16]
WHERE ([Extent15].[SecurityGroupID] = [Extent16].[SecurityGroupID]) AND ([Extent16].[UserID] = @p__linq__1)
)) AND ([Extent15].[PermissionID] IN (cast('5d6dd388-7842-40a1-a27a-b9782a445e20' as uniqueidentifier), cast('a58791b5-e8af-48d0-b9cd-ed0b54e564e6' as uniqueidentifier), cast('0cabf382-93d3-4dac-aa80-2de500a5f945' as uniqueidentifier), cast('0549f5c8-6c0e-4491-be90-ee0f29652422' as uniqueidentifier), cast('40db7de2-eefa-4d31-b400-7e72ab34de99' as uniqueidentifier))) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Requests] AS [Extent17]
WHERE ([Extent15].[ProjectID] = [Extent17].[ProjectID]) AND ([Extent17].[ID] = [Filter1].[ID1])
))
)) OR ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[AclProjectDataMarts] AS [Extent18]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[SecurityGroupUsers] AS [Extent19]
WHERE ([Extent18].[SecurityGroupID] = [Extent19].[SecurityGroupID]) AND ([Extent19].[UserID] = @p__linq__2)
)) AND ([Extent18].[PermissionID] IN (cast('5d6dd388-7842-40a1-a27a-b9782a445e20' as uniqueidentifier), cast('a58791b5-e8af-48d0-b9cd-ed0b54e564e6' as uniqueidentifier), cast('0cabf382-93d3-4dac-aa80-2de500a5f945' as uniqueidentifier))) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[RequestDataMarts] AS [Extent20]
WHERE ([Extent18].[DataMartID] = [Extent20].[DataMartID]) AND ([Extent20].[RequestID] = [Filter1].[ID1])
)) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Requests] AS [Extent21]
WHERE ([Extent18].[ProjectID] = [Extent21].[ProjectID]) AND ([Extent21].[ID] = [Filter1].[ID1])
))
)) OR ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[AclGlobal] AS [Extent22]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[SecurityGroupUsers] AS [Extent23]
WHERE ([Extent22].[SecurityGroupID] = [Extent23].[SecurityGroupID]) AND ([Extent23].[UserID] = @p__linq__3)
)) AND ([Extent22].[PermissionID] IN (cast('5d6dd388-7842-40a1-a27a-b9782a445e20' as uniqueidentifier), cast('a58791b5-e8af-48d0-b9cd-ed0b54e564e6' as uniqueidentifier), cast('0cabf382-93d3-4dac-aa80-2de500a5f945' as uniqueidentifier), cast('5ccb0ec2-006d-4345-895e-5dd2c6c8c791' as uniqueidentifier), cast('0549f5c8-6c0e-4491-be90-ee0f29652422' as uniqueidentifier), cast('40db7de2-eefa-4d31-b400-7e72ab34de99' as uniqueidentifier)))
)) OR ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[AclRequestSharedFolders] AS [Extent24]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[SecurityGroupUsers] AS [Extent25]
WHERE ([Extent24].[SecurityGroupID] = [Extent25].[SecurityGroupID]) AND ([Extent25].[UserID] = @p__linq__4)
)) AND ([Extent24].[PermissionID] IN (cast('5ccb0ec2-006d-4345-895e-5dd2c6c8c791' as uniqueidentifier))) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[RequestSharedFolderRequests] AS [Extent26]
WHERE ([Extent24].[RequestSharedFolderID] = [Extent26].[RequestSharedFolderID]) AND ([Extent26].[RequestID] = [Filter1].[ID1])
))
)) OR ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[AclOrganizations] AS [Extent27]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[SecurityGroupUsers] AS [Extent28]
WHERE ([Extent27].[SecurityGroupID] = [Extent28].[SecurityGroupID]) AND ([Extent28].[UserID] = @p__linq__5)
)) AND ([Extent27].[PermissionID] IN (cast('0549f5c8-6c0e-4491-be90-ee0f29652422' as uniqueidentifier), cast('40db7de2-eefa-4d31-b400-7e72ab34de99' as uniqueidentifier))) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Requests] AS [Extent29]
WHERE ([Extent27].[OrganizationID] = [Extent29].[OrganizationID]) AND ([Extent29].[ID] = [Filter1].[ID1])
))
)) OR ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[AclProjectOrganizations] AS [Extent30]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[SecurityGroupUsers] AS [Extent31]
WHERE ([Extent30].[SecurityGroupID] = [Extent31].[SecurityGroupID]) AND ([Extent31].[UserID] = @p__linq__6)
)) AND ([Extent30].[PermissionID] IN (cast('0549f5c8-6c0e-4491-be90-ee0f29652422' as uniqueidentifier), cast('40db7de2-eefa-4d31-b400-7e72ab34de99' as uniqueidentifier))) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Requests] AS [Extent32]
WHERE ([Extent30].[ProjectID] = [Extent32].[ProjectID]) AND ([Extent32].[ID] = [Filter1].[ID1])
)) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Requests] AS [Extent33]
WHERE ([Extent30].[OrganizationID] = [Extent33].[OrganizationID]) AND ([Extent33].[ID] = [Filter1].[ID1])
))
))) AND ( NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[AclDataMarts] AS [Extent34]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[SecurityGroupUsers] AS [Extent35]
WHERE ([Extent34].[SecurityGroupID] = [Extent35].[SecurityGroupID]) AND ([Extent35].[UserID] = @p__linq__7)
)) AND ([Extent34].[PermissionID] IN (cast('5d6dd388-7842-40a1-a27a-b9782a445e20' as uniqueidentifier), cast('a58791b5-e8af-48d0-b9cd-ed0b54e564e6' as uniqueidentifier), cast('0cabf382-93d3-4dac-aa80-2de500a5f945' as uniqueidentifier))) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[RequestDataMarts] AS [Extent36]
WHERE ([Extent34].[DataMartID] = [Extent36].[DataMartID]) AND ([Extent36].[RequestID] = [Filter1].[ID1])
)) AND ([Extent34].[Allowed] <> cast(1 as bit))
)) AND ( NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[AclProjects] AS [Extent37]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[SecurityGroupUsers] AS [Extent38]
WHERE ([Extent37].[SecurityGroupID] = [Extent38].[SecurityGroupID]) AND ([Extent38].[UserID] = @p__linq__8)
)) AND ([Extent37].[PermissionID] IN (cast('5d6dd388-7842-40a1-a27a-b9782a445e20' as uniqueidentifier), cast('a58791b5-e8af-48d0-b9cd-ed0b54e564e6' as uniqueidentifier), cast('0cabf382-93d3-4dac-aa80-2de500a5f945' as uniqueidentifier), cast('0549f5c8-6c0e-4491-be90-ee0f29652422' as uniqueidentifier), cast('40db7de2-eefa-4d31-b400-7e72ab34de99' as uniqueidentifier))) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Requests] AS [Extent39]
WHERE ([Extent37].[ProjectID] = [Extent39].[ProjectID]) AND ([Extent39].[ID] = [Filter1].[ID1])
)) AND ([Extent37].[Allowed] <> cast(1 as bit))
)) AND ( NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[AclProjectDataMarts] AS [Extent40]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[SecurityGroupUsers] AS [Extent41]
WHERE ([Extent40].[SecurityGroupID] = [Extent41].[SecurityGroupID]) AND ([Extent41].[UserID] = @p__linq__9)
)) AND ([Extent40].[PermissionID] IN (cast('5d6dd388-7842-40a1-a27a-b9782a445e20' as uniqueidentifier), cast('a58791b5-e8af-48d0-b9cd-ed0b54e564e6' as uniqueidentifier), cast('0cabf382-93d3-4dac-aa80-2de500a5f945' as uniqueidentifier))) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[RequestDataMarts] AS [Extent42]
WHERE ([Extent40].[DataMartID] = [Extent42].[DataMartID]) AND ([Extent42].[RequestID] = [Filter1].[ID1])
)) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Requests] AS [Extent43]
WHERE ([Extent40].[ProjectID] = [Extent43].[ProjectID]) AND ([Extent43].[ID] = [Filter1].[ID1])
)) AND ([Extent40].[Allowed] <> cast(1 as bit))
)) AND ( NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[AclGlobal] AS [Extent44]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[SecurityGroupUsers] AS [Extent45]
WHERE ([Extent44].[SecurityGroupID] = [Extent45].[SecurityGroupID]) AND ([Extent45].[UserID] = @p__linq__10)
)) AND ([Extent44].[PermissionID] IN (cast('5d6dd388-7842-40a1-a27a-b9782a445e20' as uniqueidentifier), cast('a58791b5-e8af-48d0-b9cd-ed0b54e564e6' as uniqueidentifier), cast('0cabf382-93d3-4dac-aa80-2de500a5f945' as uniqueidentifier), cast('5ccb0ec2-006d-4345-895e-5dd2c6c8c791' as uniqueidentifier), cast('0549f5c8-6c0e-4491-be90-ee0f29652422' as uniqueidentifier), cast('40db7de2-eefa-4d31-b400-7e72ab34de99' as uniqueidentifier))) AND ([Extent44].[Allowed] <> cast(1 as bit))
)) AND ( NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[AclRequestSharedFolders] AS [Extent46]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[SecurityGroupUsers] AS [Extent47]
WHERE ([Extent46].[SecurityGroupID] = [Extent47].[SecurityGroupID]) AND ([Extent47].[UserID] = @p__linq__11)
)) AND ([Extent46].[PermissionID] IN (cast('5ccb0ec2-006d-4345-895e-5dd2c6c8c791' as uniqueidentifier))) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[RequestSharedFolderRequests] AS [Extent48]
WHERE ([Extent46].[RequestSharedFolderID] = [Extent48].[RequestSharedFolderID]) AND ([Extent48].[RequestID] = [Filter1].[ID1])
)) AND ([Extent46].[Allowed] <> cast(1 as bit))
)) AND ( NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[AclOrganizations] AS [Extent49]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[SecurityGroupUsers] AS [Extent50]
WHERE ([Extent49].[SecurityGroupID] = [Extent50].[SecurityGroupID]) AND ([Extent50].[UserID] = @p__linq__12)
)) AND ([Extent49].[PermissionID] IN (cast('0549f5c8-6c0e-4491-be90-ee0f29652422' as uniqueidentifier), cast('40db7de2-eefa-4d31-b400-7e72ab34de99' as uniqueidentifier))) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Requests] AS [Extent51]
WHERE ([Extent49].[OrganizationID] = [Extent51].[OrganizationID]) AND ([Extent51].[ID] = [Filter1].[ID1])
)) AND ([Extent49].[Allowed] <> cast(1 as bit))
)) AND ( NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[AclProjectOrganizations] AS [Extent52]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[SecurityGroupUsers] AS [Extent53]
WHERE ([Extent52].[SecurityGroupID] = [Extent53].[SecurityGroupID]) AND ([Extent53].[UserID] = @p__linq__13)
)) AND ([Extent52].[PermissionID] IN (cast('0549f5c8-6c0e-4491-be90-ee0f29652422' as uniqueidentifier), cast('40db7de2-eefa-4d31-b400-7e72ab34de99' as uniqueidentifier))) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Requests] AS [Extent54]
WHERE ([Extent52].[ProjectID] = [Extent54].[ProjectID]) AND ([Extent54].[ID] = [Filter1].[ID1])
)) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Requests] AS [Extent55]
WHERE ([Extent52].[OrganizationID] = [Extent55].[OrganizationID]) AND ([Extent55].[ID] = [Filter1].[ID1])
)) AND ([Extent52].[Allowed] <> cast(1 as bit))
)) AND (([Filter1].[Private] <> cast(1 as bit)) OR (([Filter1].[Private] = 1) AND ([Filter1].[CreatedByID] = @p__linq__14)))
) AS [Project45]
ORDER BY [Project45].[Identifier] DESC, [Project45].[ID] ASC
将LINQ转换为SQL查询需要很长时间。
您的查询不会自动缓存，因为它使用内存中的集合作为参数(uuid集合)。
请参阅MSDN页面性能考虑(实体框架)。
一个解决方案(不是很理想)是序列化这个参数并使用String.Contains操作符。