在 10M 行上使用複雜 where 子句的查詢性能

我們有一個查詢會在執行時破壞我們的生產伺服器。

它是報告功能的一部分，不好的部分如下所示：

SELECT DISTINCT 
   mt.ID           AS ID
FROM 
   [dbo].[MyTable]    mt
WITH (NOLOCK)
WHERE           
   (@aVariable           IS NULL 
     OR (CONVERT(VARCHAR(22), mt.Date1, 112) >= CONVERT(VARCHAR(22), @date1, 112))
   AND (@status            IS NULL 
   OR @status <> 2 
   OR  (   @status = 2 
       AND (  SELECT COUNT(*) 
              FROM 
              MyTable mt2
              WITH (NOLOCK)
              WHERE 
                 mt2.CaseID = mt.CaseID 
                 AND mt2.Date1 > mt.Date1
            ) = 0
       )
   )
   AND (@aSecondVariable IS NULL 
          OR (CONVERT(VARCHAR(22), mt.Date1, 112) <= CONVERT(VARCHAR(22), @date1, 112)))
   AND (@aThirdVariable  IS NULL 
          OR (CONVERT(VARCHAR(22), mt.Date2, 112) >= CONVERT(VARCHAR(22), @date2, 112)))
   AND (@aFourtVariable  IS NULL 
         OR (CONVERT(VARCHAR(22), mt.Date2, 112) <= CONVERT(VARCHAR(22), @date2, 112)))

此外，表和索引的創建如下：

CREATE INDEX [MyIndex] ON [dbo].[MyTable]
   ([AColumn], [AColumn2], [Date1], [Date2]) 
WITH (FILLFACTOR = 90)

MyTable大約有 80 列和單列 Primary Key: (ID).

由MyTable mt大約 10.000.000 行組成。aVariable包含、aSecondVariable和aThirdVariable的列上有一個索引aFourthVariable。日期列的值大約有一半為空。在索引中，它們位於第 3 和第 4 位。

當我們在一台伺服器（沒有使用者）上執行查詢時，它的性能非常好。當我們在生產環境（與使用者）上執行它時，它需要的時間太長而且會超時。

我們想知道這是怎麼回事。兩台伺服器上的執行計劃相同。我們認為結果可能記憶體在某處，或者可用記憶體（2GB RAM）不足。

我們不是數據庫性能方面的專家，希望一些真正的 DBA 能給我們提供他們的看法。謝謝。

幾點建議：

• 刪除DISTINCT作為ID主鍵。你不可能在結果中得到重複的行。
• 不要轉換datetime列。這使您的條件不可分割，並且查詢將始終進行表掃描。如果將變數聲明為日期，則它們也不需要轉換，但這對於可搜尋性來說不是問題。
• 正如@Aaron 在評論中所建議的那樣，對日期和日期時間使用封閉式開放範圍。請閱讀他的部落格文章：要踢的壞習慣：錯誤處理日期/範圍查詢。
• 使用NOT EXISTS而不是(SELECT COUNT(*) ...) = 0檢查是否沒有符合某些條件的行。
• 放下WITH (NOLOCK)提示。另一篇博文：壞習慣：NOLOCK到處放，這個網站上的一個問題有幾個有價值的點：總是壞的嗎？NOLOCK.
• 添加適當的索引。我猜想單獨的索引(Date1)和(Date2)或索引(Date1, Date2)是可以的，但這需要測試。索引(CaseId, Date1)對子查詢很有用。
• （次要注意）始終使用模式前綴。@Aaron Bertrand 的另一篇博文：要踢的壞習慣：避免使用模式前綴。
• 添加OPTION (RECOMPILE)（正如@Mikael Eriksson 建議的那樣）。這基本上告訴優化器不要依賴記憶體的計劃，而是花一些時間讓每個查詢執行重新編譯查詢 - 即根據新參數值生成新計劃。有 7 個變數可以顯著改變查詢，這似乎是一個非常好的選擇。閱讀來自@Paul White 的文章，以更詳細地了解***“參數嵌入優化”***、參數嗅探以及其他選項和優勢：參數嗅探、嵌入和RECOMPILE選項。

查詢重寫：

SELECT  
   mt.ID 
FROM 
   [dbo].[MyTable] AS mt
WHERE 
     (@aVariable       IS NULL OR mt.Date1 >= CAST(@date1 AS DATE))
 AND (@aSecondVariable IS NULL OR mt.Date1 < DATEADD(day, 1, CAST(@date1 AS DATE)))
 AND (@aThirdVariable  IS NULL OR mt.Date2 >= CAST(@date2 AS DATE))
 AND (@aFourtVariable  IS NULL OR mt.Date2 < DATEADD(day, 1, CAST(@date2 AS DATE)))
 AND ( @status IS NULL 
    OR @status <> 2 
    OR ( @status = 2 
         AND NOT EXISTS
             ( SELECT * 
               FROM dbo.MyTable AS mt2
               WHERE mt2.CaseID = mt.CaseID 
                 AND mt2.Date1 > mt.Date1
             )
       ) 
     ) 
OPTION (RECOMPILE) ;

• about 部分@status可以寫得稍微緊湊一些。

我認為這不會對性能產生太大影響（如果有的話），它可能看起來很模糊，但為了完整起見，我也添加了這個版本：

SELECT  
   mt.ID 
FROM 
   [dbo].[MyTable] AS mt
WHERE 
     (@aVariable       IS NULL OR mt.Date1 >= CAST(@date1 AS DATE))
 AND (@aSecondVariable IS NULL OR mt.Date1 < DATEADD(day, 1, CAST(@date1 AS DATE)))
 AND (@aThirdVariable  IS NULL OR mt.Date2 >= CAST(@date2 AS DATE))
 AND (@aFourtVariable  IS NULL OR mt.Date2 < DATEADD(day, 1, CAST(@date2 AS DATE)))
 AND NOT EXISTS
     ( SELECT * 
       FROM dbo.MyTable AS mt2
       WHERE @status = 2 
         AND mt2.CaseID = mt.CaseID 
         AND mt2.Date1 > mt.Date1
     )
OPTION (RECOMPILE) ;

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