使用日期和之間的內部連接性能不佳

我在優化使用日期範圍進行內部聯接的查詢時遇到問題。查詢的目的是獲取每日數據並按週匯總。

Select  pcw.EndDate WeekEndDate, h.Store, SUM(h.DeliveryChargesTotal) DeliveryChargesTotal 
from Daily_GC_Headers h
inner join PeriodCalendar_Weeks pcw
on h.SalesDate between pcw.StartDate and pcw.EndDate
where SalesDate between @StartDate and @EndDate and isCanceled = 0 
group by pcw.EndDate, h.Store

表的簡化模式Daily_GC_Headers（1380 萬行；WHERE 子句中大約有 540 萬個匹配條件）：

Store - Varchar(10) (PK)
SalesDate - Date (PK)
TicketNumber - SmallInt (PK; starts over 1 each day at each store.)
IsCanceled - Bit
DeliveryChargesTotal - Decimal(9,2)

表的簡化模式PeriodCalendar_Weeks（570 行；53 符合條件）：

Year - smallint (PK)
Period - tinyint (PK)
Week - tinyint (PK)
StartDate - Date    
EndDate - Date

此查詢在 SSMS 中大約需要15秒。自行查詢Daily_GC_Headers（僅按 分組Store）需要2秒。針對的查詢PeriodCalendar_Weeks是“即時的”。

DBCC SHOW_STATISTICS表示統計數據是兩個表都是最新的（我們每週執行一次作業來更新它們）。我試過清除計劃記憶體。

執行計劃很奇怪。例如，它正在PeriodCalendar_Weeks. 估計行數為156.6但實際行數為153,971。然後它過濾第一個假離線的結果並執行延遲假離線。該第二個假離線的估計/實際行是540萬行，即使基礎表中的行少於 600 行。

我應該尋找或做什麼來優化它？

附加資訊

為了清楚起見，我最初在 Weeks 表上描述了一個過於簡單的 PK。我已經更新了上面的架構以顯示完整的密鑰。為 Headers 描述的 PK 是（並且曾經是）完整的密鑰。

Weeks 表中一些行的螢幕截圖：

週表中的統計數據：

Headers 表中的一些統計資訊。對於表中的整個歷史（3 年），似乎大約每 5-10 天有一個直方圖記錄。

無需返回並加入周期表即可更有效地執行此操作。

DECLARE @StartDate DATE, @EndDate DATE;

Select @StartDate = Min(StartDate), @EndDate = MAX(EndDate) 
from dbo.PeriodCalendar_Weeks pcw
where (pcw.Year = @Year and pcw.Period < @Period) 
 or  (pcw.Year = @Year and pcw.Period = @Period and pcw.Week <= @Week) 
 or (pcw.Year = @Year -1 and pcw.Period >= @Period);

SELECT 
 WeekEndDate = DATEADD(DAY, 6, DATEADD(WEEK, SalesWeek, @StartDate)), 
 Store, 
 DeliveryChargesTotal = dct
FROM 
(
 SELECT DATEDIFF(DAY, @StartDate, SalesDate)/7, Store, SUM(DeliveryChargesTotal)
 FROM dbo.Daily_GC_Headers
 WHERE SalesDate BETWEEN @StartDate AND @EndDate AND isCanceled = 0
 GROUP BY DATEDIFF(DAY, @StartDate, SalesDate)/7, Store
) AS x (SalesWeek, Store, dct)
ORDER BY WeekEndDate, Store;

如果存在許多行，過濾索引可能會有所幫助isCanceled = 1（這些只是可能的建議，取決於 的基數Store，並且可能不是最佳的）：

CREATE INDEX x ON dbo.Daily_GC_Headers
 (SalesDate) INCLUDE (Store, DeliveryChargesTotal)
 WHERE isCanceled = 0;

如果 where 的行數很少isCanceled = 1，這可能會更好：

CREATE INDEX x ON dbo.Daily_GC_Headers
 (SalesDate, IsCanceled) INCLUDE (Store, DeliveryChargesTotal);

兩者都值得在測試系統上嘗試，以及在任何一種情況下移動Store到密鑰中，或者在後一種情況下移動IsCanceled到INCLUDE列表中。在我的系統上，除了列表中的日期之外，我發現所有結果都最好INCLUDE：

CREATE INDEX x ON dbo.Daily_GC_Headers
 (SalesDate) INCLUDE (Store, IsCanceled, DeliveryChargesTotal);

同樣，您將需要測試這些方法是否有效，或者上面的查詢是否直接從 SQL Server 提供了不同/更好的建議。

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