Tsql查詢速度慢由於Or inside where子句，導致索引掃描而不是seek

我一直在努力使這個查詢更有效地工作。

我發現 where 子句中 Ors 的數量是這個查詢中最大的問題。此查詢位於儲存過程中。

我到了我能想到的唯一選擇的地步。：

• 為所有不同的入站參數可能性創建 16 個不同的查詢。
• 創建一個動態 sql 查詢，但我不相信這會更快
• 恢復到字元串 sql，但我不喜歡這樣做，因為它們的執行速度不如儲存過程。

我相信其他人之前遇到過這個問題。查詢性能從大約一秒或更短的時間開始並不可怕，但在某些情況下，它被多次命中導致長達 5 或 6 秒的延遲。

下面查詢。：

DECLARE @PERSON_ID AS INT
DECLARE @ITEM_ID AS INT
DECLARE @ITEM_VERSION AS INT
DECLARE @ITEM_SUB_NAME AS VARCHAR(250)
DECLARE @ITEM_SUB_SUB_NAME AS VARCHAR(250)

--DEFAULTS
SET @PERSON_ID = 0
SET @ITEM_ID = 0
SET @ITEM_VERSION = 1
SET @ITEM_SUB_NAME = NULL
SET @ITEM_SUB_SUB_NAME = NULL

   SELECT ID, PERSON_ID, 
          ISNULL(ITEM_VERSION, 1) AS ITEM_VERSION,
          ISNULL(ITEM_SUB_NAME, '') AS 'ITEM_SUB_NAME',
          ISNULL(ITEM_SUB_SUB_NAME, '') AS 'ITEM_SUB_SUB_NAME',
          ISNULL(ITEM_DATE, '1/1/1900') AS 'ITEM_DATE',
   FROM PERSON_TBL s WITH (NOLOCK)     
   WHERE    ( PERSON_ID = @PERSON_ID OR @PERSON_ID = 0 )
   AND ( ITEM_VERSION = @ITEM_VERSION OR ( @ITEM_VERSION = 1 AND ITEM_VERSION IS NULL ))
   AND ( EMPLOYEE_ID = @EMPLOYEE_ID OR @EMPLOYEE_ID = 0 )
   AND ( ITEM_SUB_NAME = @ITEM_SUB_NAME OR @ITEM_SUB_NAME IS NULL )
   AND ( ITEM_SUB_SUB_NAME = @ITEM_SUB_SUB_NAME OR @ITEM_SUB_SUB_NAME IS NULL )
   ORDER BY PERSON_ID, ITEM_SUB_NAME

這就是我所說的“廚房水槽”儲存過程——您需要一個過程來處理使用者可能輸入的所有可能的搜尋條件組合。幾乎不可能讓 SQL Server 派生出一個對所有這些組合都是最優和高效的單一執行計劃——我不在乎你認為什麼樣的技巧ISNULL可以拉動COALESCE或OR不能拉動它。

我通常按​​此順序嘗試的解決方案是：

1. 添加OPTION (RECOMPILE)到查詢。是的，您每次都需要支付編譯成本，但您將獲得正確的計劃，因為您提供了所提供的參數及其值。
2. 使用動態 SQL。現在您將能夠根據傳遞的不同參數記憶體多個不同的計劃。將此與伺服器級設置optimize for ad hoc workloads（來自 Kimberly Tripp 的更多資訊here和here）相結合，以便僅完全記憶體多次使用的計劃版本。例子：

DECLARE
 @PERSON_ID         INT = 0,
 @ITEM_ID           INT = 0,
 @ITEM_VERSION      INT = 1,
 @ITEM_SUB_NAME     VARCHAR(250),
 @ITEM_SUB_SUB_NAME VARCHAR(250);

DECLARE @sql NVARCHAR(MAX) = N'',

SET @sql = N'SELECT ID, PERSON_ID, 
  ISNULL(ITEM_VERSION, 1) AS ITEM_VERSION,
  ISNULL(ITEM_SUB_NAME, '''') AS ITEM_SUB_NAME,
  ISNULL(ITEM_SUB_SUB_NAME, '''') AS ITEM_SUB_SUB_NAME,
  ISNULL(ITEM_DATE, ''19000101'') AS ITEM_DATE
FROM dbo.PERSON_TBL AS s WITH (NOLOCK)     
WHERE ITEM_VERSION ' 
+ CASE WHEN @ITEM_VERSION <> 1 THEN 
  N' = @ITEM_VERSION' ELSE N' IS NULL' END
+ CASE WHEN @PERSON_ID <> 0 THEN 
  N' AND PERSON_ID = @PERSON_ID' ELSE N'' END
+ CASE WHEN @EMPLOYEE_ID <> 0 THEN
  N' AND EMPLOYEE_ID = @EMPLOYEE_ID' ELSE N'' END
+ CASE WHEN @ITEM_SUB_NAME IS NOT NULL THEN
  N' AND ITEM_SUB_NAME = @ITEM_SUB_NAME' ELSE N'' END
+ CASE WHEN @ITEM_SUB_SUB_NAME IS NOT NULL THEN
  N' AND ITEM_SUB_SUB_NAME = @ITEM_SUB_SUB_NAME' ELSE N'' END
ORDER BY PERSON_ID, ITEM_SUB_NAME;';

EXEC sys.sp_executesql @sql,
 N'@PERSON_ID INT, @ITEM_ID INT, @ITEM_VERSION INT,
   @ITEM_SUB_NAME VARCHAR(250), @ITEM_SUB_SUB_NAME VARCHAR(250)',
 @PERSON_ID, @ITEM_ID, @ITEM_VERSION, 
 @ITEM_SUB_NAME, @ITEM_SUB_SUB_NAME;

如果您發現參數嗅探仍然是一個問題，即使您對提供的每種參數組合都有單獨的計劃（由於基於實際值的基數大不相同），您也可以OPTION (RECOMPILE)在動態 SQL 中添加 - 您再次支付編譯成本，但當你有一個更簡單的 where 子句時應該會更好。

有關此模式的一些資源：

• “廚房水槽”程序（影片）
• #BackToBasics：更新的廚房水槽範例

Paul White 也有一篇很棒的文章值得一讀：

• 參數嗅探、嵌入和重新編譯選項

作為旁白：

• 停止使用單引號作為別名分隔符。它使它們看起來像字元串，並且在某些情況下被棄用，更不用說這裡甚至不需要分隔符。（我在這裡談論這個問題。）
• 停止使用模棱兩可的區域日期格式，例如m/d/yyyy（或者是d/m/yyyy？）。
• 始終使用架構前綴。
• NOLOCK用作魔術渦輪按鈕時要非常小心。
• 我對COALESCEvs.ISNULL的看法

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