Select SQL View Slow with table alias

Question

I am baffled as to why selecting my SQL View is so slow when using a table alias (25 seconds) but runs so much faster when the alias is removed (2 seconds)

-this query 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 query 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?

The SQL query runs with table alias as this is generated from Entity Framework so I have no way of changing this. I will need to modify my SQL view to be able to handle the table alias without affecting performance.

are these results reproduced when you run these queries through SSMS? Did you look at the execution plan of each? — Jeremy, Dec 08 '15 at 18:38
Also, you have a [hidden RBAR](http://www.sqlservercentral.com/articles/T-SQL/61539/) example in your view that I would correct before complaining of slowness. — Jeremy, Dec 08 '15 at 18:41
@user3405471 and you looked at the query plans for each? there is a link to the article which describes it in detail. — Jeremy, Dec 08 '15 at 19:12
I've run them over and over again in different orders and the results are the same. It's baffling to be honest — James Dev, Dec 08 '15 at 19:16
Can you upload the **actual** (not estimated) execution plan XML somewhere? — Martin Smith, Dec 08 '15 at 19:19
SQL Execution plan XML for first query (with alias) http://pastebin.com/BvCkEwej — James Dev, Dec 08 '15 at 19:27
SQL Execution plan XML for second query http://pastebin.com/rHsg4K7v — James Dev, Dec 08 '15 at 19:29
Haven't looked in detail but might just be luck of the draw. The slow one has got this obvious nastiness http://i.stack.imgur.com/X5p1g.png. Both queries timed out during optimisation. You might just be lucky that the plan found before the timeout was better in one case. — Martin Smith, Dec 08 '15 at 19:35
Though actually different versions of the CardinalityEstimator were used too because you were running one from a different database context. So presumably `ProbeProduction` has a different database compatibility level. — Martin Smith, Dec 08 '15 at 19:37
Interestingly the version run by EF indicates a bunch of missing indexes perhaps suggesting the column order DOES matter?? — Jeremy, Dec 08 '15 at 19:38
The order makes no difference I've tested in SSMS with both in the same order. — James Dev, Dec 08 '15 at 19:40
Not sure what that means @MartinSmith. Is this an indexing issue on the ValuationItems table ? — James Dev, Dec 08 '15 at 19:43
@JamesDev - SQL Server 2014 introduced a new cardinality estimator. Your fast plan is using it. `CardinalityEstimationModelVersion="120"` Your slow plan isn't. `CardinalityEstimationModelVersion="70"` This affects the assumptions used when deriving estimated row counts and these can certainly cause big differences in the eventual plan chosen. The reason for the difference is probably as the fast one is running cross database (references `[ProbeProduction].[dbo].[ValuationsTotal]`) and the database you are executing it from has compat level of 2014. — Martin Smith, Dec 08 '15 at 19:49
So you could try adding `OPTION (QUERYTRACEON 2312)` to the end of the slow one to get it to use the new CE and see if you get a better plan. — Martin Smith, Dec 08 '15 at 19:51
To Martin's point, are these queries being executed on the same server and version of SQL Server? — Jeremy, Dec 08 '15 at 19:55
Adding OPTION (QUERYTRACEON 2312) To the end of the slow query made it run in the same time as the fast query (no alias) - here is the execution plan http://pastebin.com/ibZfhYdb what is this magical OPTION ? ;) — James Dev, Dec 08 '15 at 19:59
Yes these are being run on the same server and same SSMS Sql 2014 — James Dev, Dec 08 '15 at 20:00
wow, did not know this cardinality estimator fact for SQL 2014. I learned something new today. — Jeremy, Dec 08 '15 at 20:07

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

The execution plans are very different.

The slow one has a part that leaps out as being problematic. It estimates a single row will be input to a nested loops join and result in a single scan of ValuationItems. In practice it ends up performing more than 1,000 such scans.

Estimated

Actual

enter link description here

SQL Server 2014 introduced a new cardinality estimator. Your fast plan is using it. This is shown in the XML as CardinalityEstimationModelVersion="120" Your slow plan isn't (CardinalityEstimationModelVersion="70").

So it looks as though in this case the assumptions used by the new estimator give you a better plan.

The reason for the difference is probably as the fast one is running cross database (references [ProbeProduction].[dbo].[ValuationsTotal]) and presumably the database you are executing it from has compatility level of 2014 so automatically gets the new CardinalityEstimator.

The slow one is executing in the context of ProbeProduction itself and I assume the compatibility level of that database must be < 2014 - so you are defaulting to the legacy cardinality estimator.

You can use OPTION (QUERYTRACEON 2312) to get the slow query to use the new cardinality estimator (changing the database compatibility mode to globally alter the behaviour shouldn't be done without careful testing of existing queries as it can cause regressions as well as improvements).

Alternatively you could just try and tune the query working within the limits of the legacy CE. Perhaps adding join hints to encourage it to use something more akin to the faster plan.

I'm still unsure of how to fix this. I cannot add the OPTION (QUERYTRACEON 2312) to the query as I don't want inline SQL in my Entity framework code. Does this mean that I need to somehow force my query to use the 2014 Cardinality Estimator and how can I do this apart from adding the option to the query? The database Probe Production is indeed a SQL 2013 database. — James Dev, Dec 08 '15 at 20:27
You can use a plan guide if on enterprise edition to target a specific query, Test the effects of changing compat level on overall workload for untargetted approach or just forget that the new CE exists and treat it as a standard query tuning exercise. Ensure stats up to date, look for missing indexes or better way to write query. — Martin Smith, Dec 08 '15 at 20:37

score 1 · Answer 2 · answered Dec 08 '15 at 18:37

1

The two queries are different (column order!). It is reasonable to assume the first query uses an index and is therefore much faster. I doubt it has anything to do with the aliassing.

answered Dec 08 '15 at 18:37

Tammert

196
6

column order in the `select` list doesn't make any difference to index use. – Martin Smith Dec 08 '15 at 19:12
@MartinSmith, it would be great if you could provide a solution/answer since you seem to be the most knowledgeable here. – Justin Russo Dec 08 '15 at 19:40

paparazzo · Answer 3 · 2015-12-08T20:49:26.360

For grins would take out the where and give this a try?
I might be doing a bunch of loop joins and filtering at the end
This might get it to filter up front

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 a lot of loop joins going on then try inner hash join (on all)

Select SQL View Slow with table alias

3 Answers3

Estimated

Actual