Anti-Semi Join 錯誤的解決方法

我建構了以下 SQL Server 查詢，但它遇到了SQL Server 2005 中的反半連接缺陷，這會導致基數估計不准確（1 – 呃！）並且永遠執行。由於它是一個長期生產的 SQL Server，我不能輕易建議升級版本，因此我不能在這個特定查詢上強制使用 traceflag 4199 提示。

我很難重構WHERE AND NOT IN (SELECT). 有人可以幫忙嗎？我已經確保嘗試使用基於集群密鑰對的最佳連接。

SELECT TOP 5000 d.doc2_id
   ,d.direction_cd
   ,a.address_type_cd
   ,d.external_identification
   ,s.hash_value
   ,d.publishdate
   ,d.sender_address_id AS [D2 Sender_Address_id]
   ,a.address_id AS [A Address_ID]
   ,d.message_size
   ,d.subject
   ,emi.employee_id
FROM assentor.emcsdbuser.doc2 d(NOLOCK)
INNER JOIN assentor.emcsdbuser.employee_msg_index emi(NOLOCK)
   ON d.processdate = emi.processdate
   AND d.doc2_id = emi.doc2_id
INNER LOOP JOIN assentor.emcsdbuser.doc2_address a(NOLOCK)
   ON emi.doc2_id = a.doc2_id
   AND emi.address_type_cd = a.address_type_cd
   AND emi.address_id = a.address_id
INNER JOIN sis.dbo.sis s(NOLOCK) ON d.external_identification = s.external_identification
WHERE d.publishdate > '2008-01-01'
   **AND d.doc2_id NOT IN (
       SELECT doc2_id
       FROM assentor.emcsdbuser.doc2_address d2a(NOLOCK)
       WHERE d.doc2_id = d2a.doc2_id
           AND d2a.address_type_cd = 'FRM'
       )**
OPTION (FAST 10)

請注意，該Employee_MSG_Index表是 500m 行，doc2是 1.5b 行，SIS是 ~500m 行。

任何幫助，將不勝感激！

由於它是一個長期生產的 SQL Server，我不能輕易建議升級版本

反半連接基數估計錯誤可在2005 至 2012 年（含）的所有 SQL Server 版本上重現。所有這些都需要跟踪標誌 4199 才能啟用修復，因此如果不啟動 4199，升級將無法解決您的問題（當然，從 2005 年升級還有許多其他充分的理由）。

…因此，我無法在此特定查詢上強制使用 traceflag 4199 提示。

如果只是一個特定的查詢受到影響，您可以使用OPTION (QUERYTRACEON 4199)為該查詢啟用跟踪標誌。此查詢提示已記錄並支持與 4199 一起使用，並且從 SQL Server 2005 Service Pack 2 開始適用。

此提示有效地圍繞查詢執行，因此需要DBCC TRACEON (4199)係統管理員權限。如果這是一個問題，請使用計劃指南添加提示。DBCC TRACEOFF (4199)

您還應該查看使用 4199 enabled instance-wide測試您的整個系統。計劃回歸是可能的，但總體而言，您可能會發現此標誌啟用的各種優化器修復非常值得。所有未來影響計劃的查詢處理器修復都需要啟動此標誌。

說了這麼多…

正如ypercube 的回答中提到的，該錯誤需要兩個或更多連接列才能顯示（在許多細節中）。您的子句中的冗餘NOT IN導致優化器看到兩個列比較（儘管邏輯上只有一個），從而暴露了錯誤。

刪除這種冗餘將“解決”這個特定查詢的問題，儘管其他確實有多個連接謂詞的查詢仍然容易受到攻擊。

例子

為了說明，這是一個基於問題中連結的 CSS 部落格文章的範例（但帶有完整的腳本！）：

CREATE TABLE dbo.tst_TAB1
(
   c1 integer NOT NULL, 
   c2 integer NOT NULL, 
   c3 integer NOT NULL
);

CREATE TABLE dbo.tst_TAB2
(
   c1 integer NOT NULL, 
   c2 integer NOT NULL, 
   c3 integer NOT NULL
);

CREATE INDEX i ON dbo.tst_TAB1 (c1, c2);
CREATE INDEX i ON dbo.tst_TAB2 (c1, c2);

樣本數據：

INSERT dbo.tst_TAB1
   (c1, c2, c3)
SELECT 
   number, number, number
FROM master.dbo.spt_values
WHERE 
   [type] = N'P' 
   AND number BETWEEN 1 AND 2047;

INSERT dbo.tst_TAB2 (c1, c2, c3)
VALUES (1, 1, 1);

NOT IN使用冗餘謂詞測試查詢：

SELECT
   T1.c1
FROM tst_TAB1 AS t1 
WHERE
   t1.c1 NOT IN 
   (
       SELECT 
           t2.c1 
       FROM tst_TAB2 AS t2
       -- This is redundant!
       WHERE
           t2.c1 = t1.c1
   );

估計的執行計劃顯示在反半連接後估計有 1 行：

**旁注：**事實上，這是另一個（罕見）錯誤的範例。編寫WHERE子句 ast1.c1 = t2.c1而不是t2.c1 = t1.c1允許優化器看到這兩個連接謂詞實際上是相同的，並且錯誤不會出現。

相同的查詢OPTION (QUERYTRACEON 4199)：

SELECT
   T1.c1
FROM tst_TAB1 AS t1 
WHERE
   t1.c1 NOT IN 
   (
       SELECT 
           t2.c1 
       FROM tst_TAB2 AS t2
       WHERE
           t2.c1 = t1.c1
   )
OPTION (QUERYTRACEON 4199);

估計的執行計劃現在顯示估計為2046 行，這完全正確：

我們還可以刪除多餘的謂詞：

SELECT
   T1.c1
FROM tst_TAB1 AS t1 
WHERE
   t1.c1 NOT IN 
   (
       SELECT 
           t2.c1 
       FROM tst_TAB2 AS t2
   );

執行計劃碰巧使用了額外的不相關優化（Stream Aggregate），但重要的是，加入後估計是正確的，無需啟用 4199：

多個反半連接列

NOT IN可以使用語法在多個列上表達反半聯接。這些情況將需要 4199。例如，下一個查詢連接c1和c2：

SELECT
   T1.c1
FROM tst_TAB1 AS t1 
WHERE
   t1.c1 NOT IN 
   (
       SELECT 
           t2.c1 
       FROM tst_TAB2 AS t2
       WHERE
           t2.c2 = t1.c2
   );

執行計劃顯示錯誤的 1 行估計：

使用 4199，問題得到解決：

SELECT
   T1.c1
FROM tst_TAB1 AS t1 
WHERE
   t1.c1 NOT IN 
   (
       SELECT 
           t2.c1 
       FROM tst_TAB2 AS t2
       WHERE
           t2.c2 = t1.c2
   )
OPTION (QUERYTRACEON 4199);

其他語法

最好避免以這種方式使用NOT IN，尤其是出於聯機叢書中提到的原因：

這個問題已經寫過很多次了NOT IN。有許多替代語法可用，其中是我個人的偏好。請注意，更改語法不會避免基數估計錯誤：NULLs``NOT EXISTS

SELECT
   T1.c1
FROM dbo.tst_TAB1 AS t1 
WHERE 
   NOT EXISTS 
   ( 
       SELECT 1 
       FROM dbo.tst_TAB2 AS t2 
       WHERE 
           t2.c1 = t1.c1 
           AND t2.c2 = t1.c2
   );

兩列反半連接產生 1 行估計，需要 4199 來修復它。執行計劃與之前看到的完全一樣，所以我不再贅述。NOT EXISTS語法確實避免NULLs了.NOT IN

其他意見

我同意 ypercube 的其他意見。

• 在查詢中的每個表上添加NOLOCK提示是一種不好的程式碼氣味。如果查詢確實可以容忍READ UNCOMMITTED事務語義，請顯式設置隔離級別。
• TOP沒有ORDER BY是糟糕程式碼的另一個標誌。TOP需要一個ORDER BY子句來定義什麼TOP意思。永遠不要依賴觀察到的行為，使用明確的頂級ORDER BY來獲得保證。
• INNER LOOP JOIN和一般的連接提示，暗示一個FORCE ORDER查詢提示。這嚴重限制了優化器的自由度，通常會被誤解和誤用。切勿使用您不完全理解的提示。

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