sql - 选择带有表别名的 SQL View Slow

Question

我很困惑为什么在使用表别名(25 秒)时选择我的 SQL View 如此之慢，但在删除别名时运行得更快(2 秒)

- 这个查询需要 25 秒。

SELECT [Extent1].[Id]                        AS [Id],
       [Extent1].[ProjectId]                 AS [ProjectId],
       [Extent1].[ProjectWorkOrderId]        AS [ProjectWorkOrderId],
       [Extent1].[Project]                   AS [Project],
       [Extent1].[SubcontractorId]           AS [SubcontractorId],
       [Extent1].[Subcontractor]             AS [Subcontractor],
       [Extent1].[ValuationNumber]           AS [ValuationNumber],
       [Extent1].[WorksOrderName]            AS [WorksOrderName],
       [Extent1].[NewGross],
       [Extent1].[CumulativeGross],
       [Extent1].[CreateByName]              AS [CreateByName],
       [Extent1].[CreateDate]                AS [CreateDate],
       [Extent1].[FinalDateForPayment]       AS [FinalDateForPayment],
       [Extent1].[CreateByEmail]             AS [CreateByEmail],
       [Extent1].[Deleted]                   AS [Deleted],
       [Extent1].[ValuationStatusCategoryId] AS [ValuationStatusCategoryId]
FROM   [dbo].[ValuationsTotal] AS [Extent1] 

- 这个查询需要 2 秒。

SELECT [Id],
       [ProjectId],
       [Project],
       [SubcontractorId],
       [Subcontractor],
       [NewGross],
       [ProjectWorkOrderId],
       [ValuationNumber],
       [WorksOrderName],
       [CreateByName],
       [CreateDate],
       [CreateByEmail],
       [Deleted],
       [ValuationStatusCategoryId],
       [FinalDateForPayment],
       [CumulativeGross]
FROM   [dbo].[ValuationsTotal] 

这是我的 SQL View 代码 -

WITH ValuationTotalsTemp(Id, ProjectId, Project, SubcontractorId, Subcontractor, WorksOrderName, NewGross, ProjectWorkOrderId, ValuationNumber, CreateByName, CreateDate, CreateByEmail, Deleted, ValuationStatusCategoryId, FinalDateForPayment)
     AS (SELECT vi.ValuationId                             AS Id,
                v.ProjectId,
                p.NAME,
                b.Id                                       AS Expr1,
                b.NAME                                     AS Expr2,
                wo.OrderNumber,
                SUM(vi.ValuationQuantity * pbc.BudgetRate) AS 'NewGross',
                sa.ProjectWorkOrderId,
                v.ValuationNumber,
                up.FirstName + ' ' + up.LastName           AS Expr3,
                v.CreateDate,
                up.Email,
                v.Deleted,
                v.ValuationStatusCategoryId,
                sa.FinalDateForPayment
         FROM   dbo.ValuationItems AS vi
                INNER JOIN dbo.ProjectBudgetCosts AS pbc
                        ON vi.ProjectBudgetCostId = pbc.Id
                INNER JOIN dbo.Valuations AS v
                        ON vi.ValuationId = v.Id
                INNER JOIN dbo.ProjectSubcontractorApplications AS sa
                        ON sa.Id = v.ProjectSubcontractorApplicationId
                INNER JOIN dbo.Projects AS p
                        ON p.Id = v.ProjectId
                INNER JOIN dbo.ProjectWorkOrders AS wo
                        ON wo.Id = sa.ProjectWorkOrderId
                INNER JOIN dbo.ProjectSubcontractors AS sub
                        ON sub.Id = wo.ProjectSubcontractorId
                INNER JOIN dbo.Businesses AS b
                        ON b.Id = sub.BusinessId
                INNER JOIN dbo.UserProfile AS up
                        ON up.Id = v.CreateBy
         WHERE ( vi.Deleted = 0 )
               AND ( v.Deleted = 0 )
         GROUP  BY vi.ValuationId,
                   v.ProjectId,
                   p.NAME,
                   b.Id,
                   b.NAME,
                   wo.OrderNumber,
                   sa.ProjectWorkOrderId,
                   v.ValuationNumber,
                   up.FirstName + ' ' + up.LastName,
                   v.CreateDate,
                   up.Email,
                   v.Deleted,
                   v.ValuationStatusCategoryId,
                   sa.FinalDateForPayment)
SELECT Id,
       ProjectId,
       Project,
       SubcontractorId,
       Subcontractor,
       NewGross,
       ProjectWorkOrderId,
       ValuationNumber,
       WorksOrderName,
       CreateByName,
       CreateDate,
       CreateByEmail,
       Deleted,
       ValuationStatusCategoryId,
       FinalDateForPayment,
       (SELECT SUM(NewGross) AS Expr1
        FROM   ValuationTotalsTemp AS tt
        WHERE ( ProjectWorkOrderId = t.ProjectWorkOrderId )
              AND ( t.ValuationNumber >= ValuationNumber )
        GROUP  BY ProjectWorkOrderId) AS CumulativeGross
FROM   ValuationTotalsTemp AS t 

任何想法这是为什么？

SQL 查询使用表别名运行，因为它是从 Entity Framework 生成的，所以我无法更改它。我需要修改我的 SQL View ，以便能够在不影响性能的情况下处理表别名。

Answer 1

执行计划非常不同。
缓慢的部分有一个突出的问题。它估计单行将输入到嵌套循环连接并导致对 ValuationItems 的单次扫描。实际上，它最终会执行 1,000 多次这样的扫描。
估计的

实际的

SQL Server 2014 引入了新的基数估计器。您的快速计划正在使用它。这在 XML 中显示为 CardinalityEstimationModelVersion="120"你的慢计划不是( CardinalityEstimationModelVersion="70" )。
因此，在这种情况下，新估算器使用的假设似乎为您提供了更好的计划。
差异的原因可能是因为快速运行跨数据库(引用 [ProbeProduction].[dbo].[ValuationsTotal])并且大概您正在执行它的数据库具有 2014 的兼容性级别，因此自动获取新的 CardinalityEstimator。
慢的正在 ProbeProduction 的上下文中执行本身，我假设该数据库的兼容级别必须是 < 2014 - 所以你默认使用旧的基数估计器。
您可以使用 OPTION (QUERYTRACEON 2312)让慢查询使用新的基数估计器(更改数据库兼容性模式以全局更改行为不应该在不仔细测试现有查询的情况下完成，因为它可能导致回归和改进)。
或者，您可以尝试在旧版 CE 的限制范围内调整查询。也许添加连接提示以鼓励它使用更类似于更快计划的东西。