Entity Framework 6生成的查询效率极低

Question

我需要的查询语句如下：

select top 10 *
from vw_BoosterTargetLog
where OrganizationId = 4125
order by Id desc

它在子秒内执行。

这是我的Entity Framework（6.1.2）C#版本的等效代码：

return await db.vw_BoosterTargetLog
    .Where(x => x.OrganizationId == organizationId)
    .OrderByDescending(x => x.Id)
    .Take(numberToRun)
    .ToListNolockAsync();

以下是它生成的SQL：

SELECT TOP (10) 
    [Project1].[OrganizationId] AS [OrganizationId], 
    [Project1].[BoosterTriggerId] AS [BoosterTriggerId], 
    [Project1].[IsAutomatic] AS [IsAutomatic], 
    [Project1].[C1] AS [C1], 
    [Project1].[CustomerUserId] AS [CustomerUserId], 
    [Project1].[SourceUrl] AS [SourceUrl], 
    [Project1].[TargetUrl] AS [TargetUrl], 
    [Project1].[ShowedOn] AS [ShowedOn], 
    [Project1].[ClickedOn] AS [ClickedOn], 
    [Project1].[BoosterTargetId] AS [BoosterTargetId], 
    [Project1].[TriggerEventGroup] AS [TriggerEventGroup], 
    [Project1].[TriggerIgnoreIdentifiedUsers] AS [TriggerIgnoreIdentifiedUsers], 
    [Project1].[TargetTitle] AS [TargetTitle], 
    [Project1].[BoosterTargetVersionId] AS [BoosterTargetVersionId], 
    [Project1].[Version] AS [Version], 
    [Project1].[CookieId] AS [CookieId], 
    [Project1].[CoalescedId] AS [CoalescedId], 
    [Project1].[OrganizationName] AS [OrganizationName], 
    [Project1].[ShowedOnDate] AS [ShowedOnDate], 
    [Project1].[SampleGroupSectionName] AS [SampleGroupSectionName], 
    [Project1].[Selector] AS [Selector], 
    [Project1].[SelectorStep] AS [SelectorStep]
    FROM ( SELECT 
        [Extent1].[OrganizationId] AS [OrganizationId], 
        [Extent1].[OrganizationName] AS [OrganizationName], 
        [Extent1].[BoosterTriggerId] AS [BoosterTriggerId], 
        [Extent1].[IsAutomatic] AS [IsAutomatic], 
        [Extent1].[SampleGroupSectionName] AS [SampleGroupSectionName], 
        [Extent1].[Selector] AS [Selector], 
        [Extent1].[SelectorStep] AS [SelectorStep], 
        [Extent1].[BoosterTargetId] AS [BoosterTargetId], 
        [Extent1].[CookieId] AS [CookieId], 
        [Extent1].[CustomerUserId] AS [CustomerUserId], 
        [Extent1].[CoalescedId] AS [CoalescedId], 
        [Extent1].[SourceUrl] AS [SourceUrl], 
        [Extent1].[TriggerEventGroup] AS [TriggerEventGroup], 
        [Extent1].[TriggerIgnoreIdentifiedUsers] AS [TriggerIgnoreIdentifiedUsers], 
        [Extent1].[TargetTitle] AS [TargetTitle], 
        [Extent1].[TargetUrl] AS [TargetUrl], 
        [Extent1].[ShowedOn] AS [ShowedOn], 
        [Extent1].[ShowedOnDate] AS [ShowedOnDate], 
        [Extent1].[ClickedOn] AS [ClickedOn], 
        [Extent1].[BoosterTargetVersionId] AS [BoosterTargetVersionId], 
        [Extent1].[Version] AS [Version], 
         CAST( [Extent1].[Id] AS int) AS [C1]
        FROM (SELECT 
    [vw_BoosterTargetLog].[OrganizationId] AS [OrganizationId], 
    [vw_BoosterTargetLog].[OrganizationName] AS [OrganizationName], 
    [vw_BoosterTargetLog].[BoosterTriggerId] AS [BoosterTriggerId], 
    [vw_BoosterTargetLog].[IsAutomatic] AS [IsAutomatic], 
    [vw_BoosterTargetLog].[SampleGroupSectionName] AS [SampleGroupSectionName], 
    [vw_BoosterTargetLog].[Selector] AS [Selector], 
    [vw_BoosterTargetLog].[SelectorStep] AS [SelectorStep], 
    [vw_BoosterTargetLog].[BoosterTargetId] AS [BoosterTargetId], 
    [vw_BoosterTargetLog].[CookieId] AS [CookieId], 
    [vw_BoosterTargetLog].[CustomerUserId] AS [CustomerUserId], 
    [vw_BoosterTargetLog].[CoalescedId] AS [CoalescedId], 
    [vw_BoosterTargetLog].[Id] AS [Id], 
    [vw_BoosterTargetLog].[SourceUrl] AS [SourceUrl], 
    [vw_BoosterTargetLog].[TriggerEventGroup] AS [TriggerEventGroup], 
    [vw_BoosterTargetLog].[TriggerIgnoreIdentifiedUsers] AS [TriggerIgnoreIdentifiedUsers], 
    [vw_BoosterTargetLog].[TargetTitle] AS [TargetTitle], 
    [vw_BoosterTargetLog].[TargetUrl] AS [TargetUrl], 
    [vw_BoosterTargetLog].[ShowedOn] AS [ShowedOn], 
    [vw_BoosterTargetLog].[ShowedOnDate] AS [ShowedOnDate], 
    [vw_BoosterTargetLog].[ClickedOn] AS [ClickedOn], 
    [vw_BoosterTargetLog].[BoosterTargetVersionId] AS [BoosterTargetVersionId], 
    [vw_BoosterTargetLog].[Version] AS [Version]
    FROM [dbo].[vw_BoosterTargetLog] AS [vw_BoosterTargetLog]) AS [Extent1]
        WHERE [Extent1].[OrganizationId] = 4125
    )  AS [Project1]
    ORDER BY [Project1].[C1] DESC

当然，所有EF查询都很丑陋：我并不抱怨这一点。我的抱怨是，在我的测试中，最好的情况下，它的执行速度比第一次慢了约10倍，最坏的情况下，慢了约100倍。

对于这么简单的查询来说，这似乎远远超出了所有合理的期望。

显然，我可以直接执行SQL，或者执行一个存储过程，或者类似的东西。在等待反馈的时候，我会这样做。但是，有没有其他建议可以加快速度？在这种情况下，有没有办法鼓励EF生成合理的SQL呢？

- Ken Smith

---

好的，vw_BoosterTargetLog.ID 的列类型是什么？ - Jeroen Mostert

我猜测你的命名方式中vw表示视图？EF和视图不兼容... 当你尝试使用它们时，你总会遇到各种问题。EF不知道如何为视图进行优化。它不知道主键是什么。它也不知道实际支持表中的任何元数据... - Erik Funkenbusch

EF生成的查询实际上并不那么不合理（除了可读性），优化器应该生成与您提出的简单查询相当的执行计划。我询问列类型的原因是因为查询中出现了 CAST([Extent1].[Id] AS int) -- 如果该列不是 INT 类型，则转换将使得结果的 ORDER BY 无比低效，因为SQL Server必须先转换所有值才能对它们进行排序。但是如果底层列是 INT 类型，优化器应该能看穿这一点。 - Jeroen Mostert

@JeroenMostert - 啊！在你的评论之后，我仔细检查了一下，结果发现我撒谎了。它实际上是一个BIGINT。这可能是问题所在。所以问题变成了如何让EF将其识别为long/Int64，我想。 - Ken Smith

转换int类型为更大的整数类型不是问题(转换将在排序后完成)，但反过来会强制在排序之前进行转换步骤，从而导致完全表扫描。你正在使用模型优先还是代码优先EF? 对于代码优先，我认为您可以自由更改实体中属性的类型为应该是什么。对于模型优先，您可以尝试修改模型，但我不知道如何使这些更改在EF重新生成时“生效”。 - Jeroen Mostert

Answer 1

EF生成的查询语句在可读性方面很糟糕，但通常仍然是相当合理的--我说这话时，我的数据访问几乎都是通过手写查询存储过程来完成。但为了使其正常工作，EF对数据库的模型需要与实际的数据库匹配，否则就会引入转换，当这种情况发生时，很容易出现可怕的性能下降，因为所有数据都要进行转换并且无法使用索引。

如果我们消除一些嵌套，EF查询可以简化为

SELECT TOP (10) *
FROM (
    SELECT *, CAST(Id AS INT) AS C1
    FROM vw_BoosterTargetLog
    WHERE OrganizationId = 4125
) _
ORDER BY C1 DESC

（这不是实际结果集，因为Id不是真实查询中最终结果集的一部分，但假装我像EF一样写出了所有列。）

如果vw_BoosterTargetLog.Id实际上不是一个INT，那么在排序之前会强制转换所有行，这会导致速度变慢。解决方案是找出该列的实际类型（在本例中是BIGINT），并相应地更新您的模型。

- Jeroen Mostert

---

非常好的发现和解释。谢谢。 - Ken Smith