Select SQL View Slow with table alias

Question

I'm confusing why choosing my SQL view is so slow when using a table alias (25 seconds), but it is much faster when deleting an alias (2 seconds)

- This request takes 25 seconds.

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] 

- This request takes 2 seconds.

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

this is my SQL View code -

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 

Any ideas why this is so?

The SQL query is executed with a table alias because it is generated from the Entity Framework, so I cannot change this. I will need to change the SQL view to be able to handle the table alias without affecting performance.

Answer 1

Implementation plans are very different.

, . , ValuationItems. 1000 .

SQL Server 2014 . . XML CardinalityEstimationModelVersion="120" (CardinalityEstimationModelVersion="70").

, , , , .

, , , - ( [ProbeProduction]. [dbo]. [ValuationsTotal]) , , , , 2014, CardinalityEstimator.

ProbeProduction, , < 2014 - .

OPTION (QUERYTRACEON 2312), ( , , ).

, CE. , , - .

Answer 2

( !). , , , . , - .

Answer 3

For a smile would you pull out where you try? I could do a bunch of loop combining and filtering at the end.
It might make it filter forward.

FROM       dbo.ValuationItems AS vi
INNER JOIN dbo.Valuations     AS v
             ON vi.ValuationId = v.Id
            AND vi.Deleted = 0 
            AND  v.Deleted = 0 
-- other joins 
-- NO where

If you have many loop joins then try the built-in hash join (at all)