查詢速度很快，但在創建為視圖時變得遲緩

執行計劃作為查詢

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

執行計劃作為視圖

-- 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'

問題

單獨的查詢或作為儲存過程的查詢都可以正常工作；但是，作為視圖的查詢決定進行掃描而不是查找並使用不同的索引。如何讓查詢作為視圖正常執行？是什麼導致它使用不同的索引並掃描而不是查找？

SQLPlan 文件

• 單獨查詢計劃
• 查看查詢計劃

索引

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

本質問題是在計算視窗函式Route = N'0600' 之前過濾（如在查詢中）與在計算視窗函式Route = N'0600' 後過濾（如在視圖中）不同。

這通常會給視窗函式提供不同（=不正確）的結果，因此優化器不會這樣做。有關更多資訊，請參閱Erik Darling的視窗函式和 Where 子句。

如果視圖中的所有視窗函式都在 上進行分區Route，優化器會考慮將謂詞下推到視圖中，因為仍然可以獲得正確的結果。可悲的是，您的觀點並非如此。在這種情況下，添加OPTION (RECOMPILE)將無濟於事。

考慮將視圖重寫（或補充）為內聯表值函式，並帶有一個顯式參數 for Route。我在 Stack Overflow 上的回答中有一個該技術的範例。

如果您還有其他過濾條件Route，並且無論如何您都將擁有一個儲存過程，您可以抽像出過濾條件的類型 - 為路由製作一個 TVF，當路由傳遞給儲存過程時，呼叫它. 為日期範圍製作另一個 TVF，當日期過去時，呼叫它。如果可能的話，當它們同時傳遞路線和日期時會變得複雜，但無論傳遞哪個參數，分離都可能是獲得可靠可預測性能的最安全方法。如果您有可選的過濾條件，那麼動態 SQL 和/或選項（重新編譯）可能是您最好的選擇。

引用自：https://dba.stackexchange.com/questions/215120