SQL Server Performance – Query Fast but Sluggish as View

execution-planperformancequery-performancesql serversql-server-2016view

Execution Plan as Query

SELECT VP.Branch
     , VP.ROUTE AS Route
     , VP.SAPCustomerID
     , S.CustomerID
     , S.ProductID
     , S.Date
     -- Group by Customer
     , CustomerQuantity = SUM(S.Quantity) OVER (PARTITION BY VP.Branch, VP.ROUTE, VP.SAPCustomerID, S.ProductID, S.Date)
     , CustomerFourWeekSalesAvg = SUM(S.FourWeekSalesAvg) OVER (PARTITION BY VP.Branch, VP.ROUTE, VP.SAPCustomerID, S.ProductID, S.Date)
     -- Group by Route
     , SUM(S.Quantity) OVER (PARTITION BY VP.Branch, VP.ROUTE, S.ProductID, S.Date) AS RouteQuantity
     , RouteFourWeekSalesAvg = SUM(S.FourWeekSalesAvg) OVER (PARTITION BY VP.Branch, VP.ROUTE, S.ProductID, S.Date)
     -- Group by Branch
     , BranchQuantity = SUM(S.Quantity) OVER (PARTITION BY VP.Branch, S.ProductID, S.Date) 
     , BranchFourWeekSalesAvg = SUM(S.FourWeekSalesAvg) OVER (PARTITION BY VP.Branch, S.ProductID, S.Date) 
FROM vw_SalesByWeek AS S
INNER JOIN SAP_VisitPlan AS VP WITH (NOLOCK) 
    ON VP.CustomerID = S.CustomerID
    AND VP.DateFrom <= S.Date
    AND VP.DateTo >= S.Date

enter image description here

Execution Plan as View

-- Where vw_SalesByWeekSummary is the query above exactly.
SELECT [Branch]
      ,[Route]
      ,[SAPCustomerID]
      ,[CustomerID]
      ,[ProductID]
      ,[Date]
      ,[CustomerQuantity]
      ,[CustomerFourWeekSalesAvg]
      ,[RouteQuantity]
      ,[RouteFourWeekSalesAvg]
      ,[BranchQuantity]
      ,[BranchFourWeekSalesAvg]
FROM vw_SalesByWeekSummary
WHERE Route = '0600'

enter image description here

Problem

The query alone or as a stored procedure works just fine; however, the query as a view decides to do scans instead of seeks and uses different indexes.
How can I get the query to behave properly as a view? What is causing it to use different indexes and scan instead of seek?

SQLPlan files

Indexes

-- Solo Query Plan Indexes
CREATE NONCLUSTERED INDEX [IX_VisitPlan_ByRoute] ON [dbo].[SAP_VisitPlan] ([ROUTE] ASC) INCLUDE ([Branch])
CREATE UNIQUE NONCLUSTERED INDEX [UIX_CacheCS_ByWeek] ON [dbo].[Cache_ConvSalesByWeek] ([CustomerID] ASC, [ProductID] ASC, [WkStartDate] ASC) INCLUDE ([ID], [SoldQuantity], [Route], [FourWeekSalesAvg], [NumberOfPriorSalesWeeks])
CREATE NONCLUSTERED INDEX [IX_CacheSS_4wkAvg] ON [dbo].[Cache_ScanSalesByWeek] ([CustomerID] ASC, [ProductID] ASC, [Route] ASC) INCLUDE ([FourWeekSalesAvg], [WkStartDate], [SoldQuantity])

-- View Query Plan Indexes
CREATE UNIQUE NONCLUSTERED INDEX [UIX_VisitPlan_PK] ON [dbo].[SAP_VisitPlan] ([SAPCustomerID] ASC, [CustomerID] ASC, [DateTo] DESC, [DateFrom] DESC, [ROUTE] ASC, [DriverNumber] ASC) INCLUDE ([Branch])
CREATE NONCLUSTERED INDEX [IX_CacheCS] ON [dbo].[Cache_ConvSalesByWeek] ([CustomerID] ASC, [ProductID] ASC, [WkStartDate] ASC) INCLUDE ([SoldQuantity], [FourWeekSalesAvg], [Route])
CREATE NONCLUSTERED INDEX [IX_ScanSalesByWeek_Sunday] ON [dbo].[Cache_ScanSalesByWeek] ([WkStartDate] ASC) INCLUDE ([CustomerID], [ProductID], [SoldQuantity], [Route], [FourWeekSalesAvg])

Best Answer

The essential problem is that filtering on Route = N'0600' before computing the window functions (as in the query) is different from filtering on Route = N'0600' after computing the window functions (as in the view).

This would give different (= incorrect) results for the window functions in general, so the optimizer does not do that. See Of Windowing Functions And Where Clauses by Erik Darling for more information.

If all the window functions in the view were partitioned on Route, the optimizer would consider pushing the predicate down into the view because correct results would still be obtained. Sadly, that is not the case for your view. Adding OPTION (RECOMPILE) would not help in this case.

Consider rewriting (or supplementing) the view as an inline table-valued function, with an explicit parameter for Route. I have an example of the technique in an answer on Stack Overflow.

If you have other filter criteria beyond Route alone, and if you're going to have a stored procedure anyway, you could abstract away the type of filter criteria - make a TVF for route, and when route is passed to the stored procedure, call it. Make another TVF for date range, and when dates are passed, call it. Gets complicated when they pass both route and dates, if that's possible, but separation is likely going to be the safest way to get reliably predictable performance regardless of which parameter is passed. If you have optional filter criteria it may be that dynamic SQL and/or option (recompile) is your best bet.

Related Question