在我看來如何實現謂詞下推
我有一個報表（大約 10 億行）和一個很小的維度表：
CREATE TABLE dbo.Sales_unpartitioned ( BusinessUnit int NOT NULL, [Date] date NOT NULL, SKU varchar(8) NOT NULL, Quantity numeric(10, 2) NOT NULL, Amount numeric(10, 2) NOT NULL, CONSTRAINT PK_Sales_unpartitioned PRIMARY KEY CLUSTERED (BusinessUnit, [Date], SKU) ); --- Demo data: INSERT INTO dbo.Sales_unpartitioned SELECT severity AS BusinessUnit, DATEADD(day, message_id, '2000-01-01') AS [Date], LEFT([text], 3) AS SKU, 1000.*RAND(CHECKSUM(NEWID())) AS Quantity, 10000.*RAND(CHECKSUM(NEWID())) AS Amount FROM sys.messages WHERE [language_id]=1033; --- Artificially inflate statistics of demo data: UPDATE STATISTICS dbo.Sales_unpartitioned WITH ROWCOUNT=1000000000; --- Dimension table: CREATE TABLE dbo.BusinessUnits ( BusinessUnit int NOT NULL, SalesManager nvarchar(250) NULL, PRIMARY KEY CLUSTERED (BusinessUnit) ); INSERT INTO dbo.BusinessUnits (BusinessUnit) SELECT DISTINCT BusinessUnit FROM dbo.Sales;…我在其中添加了應用程序用於 OLTP 樣式報告的報告視圖。
CREATE OR ALTER VIEW dbo.SalesReport_unpartitioned AS SELECT bu.BusinessUnit, s.[Date], s.SKU, s.Quantity, s.Amount FROM dbo.BusinessUnits AS bu CROSS APPLY ( --- Regular sales SELECT t.BusinessUnit, t.[Date], t.SKU, t.Quantity, t.Amount FROM dbo.Sales_unpartitioned AS t WHERE t.BusinessUnit=bu.BusinessUnit AND t.SKU LIKE 'T%' UNION ALL --- This is a special reporting entry. We only --- want to see today's row. In case of duplicates, --- get the row with the first "SKU". SELECT TOP (1) s.BusinessUnit, s.[Date], s.SKU, s.Quantity, s.Amount FROM dbo.Sales_unpartitioned AS s WHERE s.BusinessUnit=bu.BusinessUnit AND s.[Date]=CAST(SYSDATETIME() AS date) AND s.SKU LIKE 'S%' ORDER BY s.BusinessUnit, s.[Date], s.SKU ) AS s這個想法是，使用者應用程序將使用 SELECT 查詢來查詢此視圖，該查詢過濾一系列日期和一個或多個業務單位。為此，我選擇了一種
CROSS APPLY模式，以便查詢可以“循環”每個業務單元，查找日期範圍，並在 SKU 上應用殘差過濾器。範例應用查詢：
DECLARE @from date='2021-01-01', @to date='2021-12-31'; SELECT * FROM dbo.SalesReport_unpartitioned WHERE BusinessUnit=16 AND [Date] BETWEEN @from AND @to ORDER BY BusinessUnit, [Date], SKU;我希望查詢計劃看起來像這樣： Desired plan
但是，計劃結果如下： 實際計劃
我希望 SQL Server 在 Date 列上執行“謂詞下推”，允許 Clustered Index Seek 查找單個 BusinessUnit 和日期範圍，然後在 SKU 上應用剩余謂詞。這適用於“s”分支中的 Seek（帶有 的分支
TOP）——可能是因為它在查詢中有一個硬編碼的 Date 謂詞——但不適用於“t”分支。但是，在“t”分支上，SQL Server 僅在 SKU 上使用剩余謂詞搜尋特定的 BusinessUnit，從而有效地檢索所有日期。只有在計劃結束時，它才會應用過濾日期列的過濾運算符。
在一個大表中，這會帶來非常顯著的性能損失——當您只需要一周的時間時，您最終可能會從磁碟讀取 20 年的數據。
我嘗試過的事情
解決方法：
- 將視圖轉換為具有過濾“s”和“t”查詢的@fromDate 和@toDate 參數的內聯表值函式將根據需要啟用Seek on (BusinessUnit, Date)，但需要重寫應用程式碼。
- 將(from to )
UNION ALL移出將啟用謂詞下推。它在 BusinessUnit 表上又進行了一次搜尋，這是完全可以接受的。CROSS APPLY``CROSS APPLY (UNION)``CROSS APPLY() UNION CROSS APPLY()修復了 Seek，但改變了結果：
- 令人驚訝的是，刪除“s”查詢的
TOP (1)andORDER BY會使謂詞下推對“t”起作用，但會從“s”返回太多行。UNION ALL通過刪除“s”或“t”查詢來消除將啟用謂詞下推，但會產生不正確的結果。無變化或不可行：
- 替換
TOP (1)為ROW_NUMBER()模式不會改變 Seek。- 將 the 更改
CROSS APPLY為強制INNER LOOP JOIN修復了“t”上的 Seek，但實際上將“s”更改為 Scan，這甚至更糟。- 添加跟踪標誌 8780 以允許優化器在計劃上工作更長時間不會改變任何事情。該計劃已經完全優化，沒有提前終止。
一個常見的執行緒似乎是更改/簡化“s”查詢（刪除
TOP,ORDER BY）解決了“t”查詢的問題，這對我來說感覺違反直覺。我在尋找什麼
我試圖了解這是否是優化器的缺點，是否是故意成本/優化機制的結果，或者我是否只是忽略了某些東西。
我試圖了解這是否是優化器的缺點，是否是故意成本/優化機制的結果，或者我是否只是忽略了某些東西。
這是所有這些的一小部分。
提供的查詢中有很多內容——真的太多了——所以為了避免寫半本書，我將把它歸結為導致你沒有得到你所追求的計劃的主要因素：
優化器不會將謂詞下推到應用的內側。
在apply之上的關係選擇（過濾器，謂詞）上執行的規則很自然地被稱為 .
SELonApply它執行以下邏輯替換：選擇（A 應用 B）-> 選擇（選擇 A 應用 B）
它獲取涉及 A 和 B 的潛在復雜選擇的一部分，並將那些它可以向下推到驅動表 A 的部分。沒有選擇的部分被推到 B。選擇的部分不能被推倒留在後面。
這聽起來像是一個令人震驚的疏忽，與經驗背道而馳。那是因為它不是完整的故事。
優化器嘗試在編譯過程的早期將應用轉換為等效連接（在簡化期間，在瑣碎計劃和基於成本的優化之前）。它能夠將選擇向下推送到join的任一側，這是安全的。在基於成本的優化過程中，該連接又可以轉化為物理應用。
所有這一切的效果是讓優化器看起來像是將謂詞推到了應用的內側：
- 書面應用轉換為連接。
- 謂詞下推連接的任一側。
- 加入轉換為應用。
讓我給你看一個例子：
DECLARE @T1 table (pk integer PRIMARY KEY, c1 integer NOT NULL INDEX ic1); DECLARE @T2 table (fk integer NOT NULL, c2 integer NOT NULL, PRIMARY KEY (fk, c2)); SELECT T1.*, T2.* FROM @T1 AS T1 CROSS APPLY ( SELECT T2.* FROM @T2 AS T2 WHERE T2.fk = T1.pk ) AS T2 WHERE 1 = 1 AND T1.c1 = 1 AND T2.c2 = 2;
如果您仔細查看該計劃，您會看到 T2 上的謂詞被推到了內部查找，並且嵌套循環連接是一個應用（它具有外部引用）。這是唯一可能的，因為優化器最初能夠將應用重寫為連接，推送謂詞，然後稍後轉換回應用。
我們可以使用未記錄的跟踪標誌 9114 禁用 apply-to-join 重寫：
DECLARE @T1 table (pk integer PRIMARY KEY, c1 integer NOT NULL INDEX ic1); DECLARE @T2 table (fk integer NOT NULL, c2 integer NOT NULL, PRIMARY KEY (fk, c2)); SELECT T1.*, T2.* FROM @T1 AS T1 CROSS APPLY ( SELECT T2.* FROM @T2 AS T2 WHERE T2.fk = T1.pk ) AS T2 WHERE 1 = 1 AND T1.c1 = 1 AND T2.c2 = 2 OPTION (QUERYTRACEON 9114);這意味著 only
SELonApply可以使用，它只推送到驅動表 A：
請注意，T2.c2 上的選擇部分在過濾器中“卡在”應用上方。（內側查找僅在應用內指定的 fk/pk 相等性上。）
優化器建立在關係原則之上。它讚賞關係模式設計和使用關係結構的查詢。Apply（橫向連接）是一個相對較新的擴展。與 apply 相比，優化器知道更多的 join 技巧，因此需要儘早進行重寫。
當您使用諸如 apply 或非關係 Top 之類的東西時，您隱含地對最終計劃形狀承擔了更多責任。換句話說，您通常必須以不同的方式表達您的查詢（如在您的解決方法中）以獲得良好的結果。
作為記錄，我的偏好是使用帶有顯式謂詞放置的內聯表值函式。如果我要重寫視圖，我可能會選擇：
CREATE OR ALTER VIEW dbo.SalesReport_unpartitioned AS --- Regular sales SELECT BU.BusinessUnit, RS.[Date], RS.SKU, RS.Quantity, RS.Amount FROM dbo.BusinessUnits AS BU JOIN dbo.Sales_unpartitioned AS RS ON RS.BusinessUnit = BU.BusinessUnit WHERE RS.SKU LIKE 'T%' UNION ALL --- This is a special reporting entry. SELECT BU.BusinessUnit, SR.[Date], SR.SKU, SR.Quantity, SR.Amount FROM dbo.BusinessUnits AS BU JOIN dbo.Sales_unpartitioned AS SR ON SR.BusinessUnit = BU.BusinessUnit WHERE 1 = 1 AND SR.SKU LIKE 'S%' --- We only want to see today's row. AND SR.[Date] = CONVERT(date, SYSDATETIME()) --- In case of duplicates, get the row with the first "SKU". AND SR.SKU = ( SELECT MIN(SR2.SKU) FROM dbo.Sales_unpartitioned AS SR2 WHERE SR2.BusinessUnit = SR.BusinessUnit AND SR2.[Date] = SR.[Date] AND SR2.SKU LIKE 'S%' ); GO對於提供的測試查詢：
DECLARE @from date='2021-01-01', @to date='2021-12-31'; SELECT * FROM dbo.SalesReport_unpartitioned WHERE BusinessUnit=16 AND [Date] BETWEEN @from AND @to ORDER BY BusinessUnit, [Date], SKU;執行計劃是：
橙色部分是正常銷售。黃色部分用於特殊報告條目。
