Plano de Execução como Consulta
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
Plano de Execução como Visualização
-- 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'
Problema
A consulta sozinha ou como um procedimento armazenado funciona bem; no entanto, a consulta como uma exibição decide fazer varreduras em vez de buscas e usa índices diferentes. Como posso fazer com que a consulta se comporte corretamente como uma exibição? O que está causando o uso de diferentes índices e varredura em vez de busca?
Arquivos SQLPlan
Índices
-- 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])
