Query is fast but becomes sluggish when created as a view

Shelby115 08/16/2018. 1 answers, 150 views
sql-server query-performance sql-server-2016 execution-plan view

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])

1 Answers


Paul White 08/19/2018.

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.


HighResolutionMusic.com - Download Hi-Res Songs

1 The Chainsmokers

Beach House flac

The Chainsmokers. 2018. Writer: Andrew Taggart.
2 (G)I-DLE

POP/STARS flac

(G)I-DLE. 2018. Writer: Riot Music Team;Harloe.
3 Ariana Grande

​Thank U, Next flac

Ariana Grande. 2018. Writer: Crazy Mike;Scootie;Victoria Monét;Tayla Parx;TBHits;Ariana Grande.
4 Anne-Marie

Rewrite The Stars flac

Anne-Marie. 2018. Writer: Benj Pasek;Justin Paul.
5 Clean Bandit

Baby flac

Clean Bandit. 2018. Writer: Jack Patterson;Kamille;Jason Evigan;Matthew Knott;Marina;Luis Fonsi.
6 Nicki Minaj

No Candle No Light flac

Nicki Minaj. 2018. Writer: Denisia “Blu June” Andrews;Kathryn Ostenberg;Brittany "Chi" Coney;Brian Lee;TJ Routon;Tushar Apte;ZAYN;Nicki Minaj.
7 BlackPink

Kiss And Make Up flac

BlackPink. 2018. Writer: Soke;Kny Factory;Billboard;Chelcee Grimes;Teddy Park;Marc Vincent;Dua Lipa.
8 Imagine Dragons

Bad Liar flac

Imagine Dragons. 2018. Writer: Jorgen Odegard;Daniel Platzman;Ben McKee;Wayne Sermon;Aja Volkman;Dan Reynolds.
9 BTS

Waste It On Me flac

BTS. 2018. Writer: Steve Aoki;Jeff Halavacs;Ryan Ogren;Michael Gazzo;Nate Cyphert;Sean Foreman;RM.
10 Halsey

Without Me flac

Halsey. 2018. Writer: Halsey;Delacey;Louis Bell;Amy Allen;Justin Timberlake;Timbaland;Scott Storch.
11 Little Mix

Woman Like Me flac

Little Mix. 2018. Writer: Nicki Minaj;Steve Mac;Ed Sheeran;Jess Glynne.
12 Brooks

Limbo flac

Brooks. 2018.
13 Fitz And The Tantrums

HandClap flac

Fitz And The Tantrums. 2017. Writer: Fitz And The Tantrums;Eric Frederic;Sam Hollander.
14 Backstreet Boys

Chances flac

Backstreet Boys. 2018.
15 Lady Gaga

I'll Never Love Again flac

Lady Gaga. 2018. Writer: Benjamin Rice;Lady Gaga.
16 Diplo

Close To Me flac

Diplo. 2018. Writer: Ellie Goulding;Savan Kotecha;Peter Svensson;Ilya;Swae Lee;Diplo.
17 Rita Ora

Velvet Rope flac

Rita Ora. 2018.
18 Bradley Cooper

Always Remember Us This Way flac

Bradley Cooper. 2018. Writer: Lady Gaga;Dave Cobb.
19 Imagine Dragons

Machine flac

Imagine Dragons. 2018. Writer: Wayne Sermon;Daniel Platzman;Dan Reynolds;Ben McKee;Alex Da Kid.
20 Erika Sirola

Speechless flac

Erika Sirola. 2018. Writer: Teemu Brunila;Stefan Dabruck;Jürgen Dohr;Guido Kramer;Dennis Bierbrodt;Chris Braide;Robin Schulz.

Related questions

Hot questions

Language

Popular Tags