TSQL 慢查詢，未按預期使用索引

我有一個在 Azure SQL 數據庫上執行的寬表，相對較大，有 14,264,775 行。

以下查詢需要一些 TLC。

IF EXISTS (
   SELECT  1/0 
   FROM dbo.table1 src
     INNER JOIN dbo.table1 tgt 
       ON tgt.Col1 = src.Col1
   WHERE tgt.ValidFrom <= src.ValidTo
   AND tgt.ValidTo >= src.ValidFrom
   AND tgt.RecordId <> src.RecordId  
)
BEGIN
   RAISERROR('Overlap detected in dbo.table1', 11, 1); 
END ;

我有這個索引。

CREATE NONCLUSTERED INDEX [IX__table1] ON dbo.table1
       ( Col1 ) 
INCLUDE (ValidFrom, ValidTo, RecordId)
GO

這是查詢中的 io 統計資訊。邏輯讀取是通過屋頂。

這是計劃 XML。我嘗試了 PasteThePlan，但它不會解析計劃 XML。（也許它不喜歡 Axure sql 數據庫計劃 xml）。

如您所見，有一個索引掃描

$$ src $$; 讀取 14,264,775 行（與表中的所有行數相同）。和一個索引搜尋$$ tgt $$; 讀取 194,405,307 行。 我需要更改什麼來提高查詢的性能？

在 1400 萬行中，有 210 萬個唯一的 Col1 值。

您似乎手動編輯了 XML 並使其無效，主要是通過添加無效字元，如<和>。修復一些問題後，我能夠將計劃載入到 SSMS 和計劃資源管理器中：

這表明您有一個名為[IX__dbo_table1__DateRange]- 問題中未提及的索引。從seek謂詞來看，這個索引至少有Col1和ValidTo在索引中的鍵。

另一個問題是使用IF EXISTS. 這引入了一個行目標，這導致優化器支持嵌套循環解決方案。請參閱相關的問答IF EXISTS 花費的時間比嵌入的 select 語句要長。

也就是說，找到任何可能的重疊範圍是一個很難用 b-tree 索引完全解決的問題，請參閱Resolving a performance issue with BETWEEN join- eager spool。

在不了解完整的表定義、索引和數據分佈的情況下，很難提出合適的解決方案。如果您只是想快速輕鬆地嘗試一些東西，而不需要過多地更改索引或源查詢，請嘗試使用雜湊連接提示：

IF EXISTS (
   SELECT  1/0 
   FROM dbo.table1 src
     INNER HASH JOIN dbo.table1 tgt -- hint added
       ON tgt.Col1 = src.Col1
   WHERE tgt.ValidFrom <= src.ValidTo
   AND tgt.ValidTo >= src.ValidFrom
   AND tgt.RecordId <> src.RecordId  
)
BEGIN
   RAISERROR('Overlap detected in dbo.table1', 11, 1); 
END ;

這將完全掃描索引兩次，但如果您的系統可以處理記憶體或 I/O 要求，並且並行或批處理模式執行可用，這可能不會太糟糕。如果有相當數量的不同Col1值，這將最有效。

假設應該不允許重疊，我的偏好是首先使用約束來避免這種情況發生。請參閱在時態數據庫設計中確保唯一條目的正確方法是什麼？

---

或者，正如ypercubeᵀᴹ在聊天中建議的那樣：

IF EXISTS
(
   SELECT 1/0
   FROM
   (
       SELECT 
           T.ValidFrom,
           PrevValidTo =
               LAG(T.ValidTo) OVER ( 
                   PARTITION BY T.Col1
                   ORDER BY T.ValidFrom)
       FROM dbo.table1 AS T
   ) AS T1
   WHERE 
       T1.PrevValidTo >= T1.ValidFrom
)
BEGIN
   RAISERROR('Overlap detected in dbo.table1', 11, 1); 
END;

使用如下索引：

CREATE NONCLUSTERED INDEX [IX__table1] 
ON dbo.table1
   (Col1, ValidFrom) 
INCLUDE 
   (ValidTo, RecordId);

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