查詢調整幫助

全部，

需要一些提示/技巧來調整此查詢：

WITH Candidates(ActivityId) AS(
   SELECT ActivityId
   FROM EmailHashBase WITH (NOLOCK)
   WHERE ActivityId IN
       (
           SELECT ActivityId
           FROM EmailHashBase WITH (NOLOCK)
           WHERE HashType = 0
               AND Hash IN (3901304,293296907)
           GROUP BY ActivityId
           HAVING COUNT(ActivityId) >= 2
       )
       AND HashType = 1
       AND Hash IN (1169472434)
   GROUP BY ActivityId
   HAVING   COUNT(ActivityId) >= 1
)
SELECT b.ActivityId
FROM (
   SELECT 
       a.ActivityId, 
       ROW_NUMBER() over (ORDER BY a.ModifiedOn DESC) as Rownumber
   FROM ActivityPointerBase AS a WITH (NOLOCK)
   WHERE a.ActivityId IN
       (
           SELECT ActivityId
           FROM Candidates AS c
           WHERE(
                   (
                       SELECT COUNT(*)
                       FROM EmailHashBase AS h
                       WHERE h.ActivityId = c.ActivityId
                           AND h.HashType   = 0
                   ) <= 2
               ) 
           AND ((SELECT COUNT(*) FROM EmailHashBase AS h WHERE h.ActivityId = c.ActivityId AND h.HashType = 1) = 1)
       )
   )b
WHERE Rownumber = 1

單個查詢中有太多子查詢（…where in (select…)）

這通常是一種反性能模式

嘗試將查詢分解為較小的查詢，以便逐步收集數據，並使用#temp 表 - 您可以將中間結果放入#temp 表中，並在以下查詢中查詢它們

分解較小部分的複雜查詢並使用 #temp 表來保存中間結果的方法 - 也可以減少阻塞，因此您不必使用 NOLOCK 提示

注意NOLOCK提示有時可以在開發環境中使用，但強烈不推薦在生產環境中使用

條件看起來很奇怪。例如：

AND ((SELECT COUNT(*) FROM EmailHashBase AS h WHERE h.ActivityId = c.ActivityId AND h.HashType = 1) = 1)

但 c.ActivityId 來自 CTE 候選人，您有這樣的過濾器：

AND HashType = 1
       AND Hash IN (1169472434)
   GROUP BY ActivityId
   HAVING   COUNT(ActivityId) >= 1

如果您將 CTE 的最後一行更改為“HAVING COUNT(ActivityId) = 1”，您似乎可以刪除第一個條件。

關於這部分的相同想法：

WHERE(
                   (
                       SELECT COUNT(*)
                       FROM EmailHashBase AS h
                       WHERE h.ActivityId = c.ActivityId
                           AND h.HashType   = 0
                   ) <= 2
               ) 

但是您已經以這種方式過濾了您的 CTE：

WHERE HashType = 0
               AND Hash IN (3901304,293296907)
           GROUP BY ActivityId
           HAVING COUNT(ActivityId) >= 2

因此，您可以使用“HAVING COUNT(ActivityId) = 2”並以這種方式重寫您的原始查詢：

WITH Candidates(ActivityId) AS(
   SELECT ActivityId
   FROM EmailHashBase WITH (NOLOCK)
   WHERE ActivityId IN
       (
           SELECT ActivityId
           FROM EmailHashBase WITH (NOLOCK)
           WHERE HashType = 0
               AND Hash IN (3901304,293296907)
           GROUP BY ActivityId
           HAVING COUNT(ActivityId) = 2
       )
       AND HashType = 1
       AND Hash IN (1169472434)
   GROUP BY ActivityId
   HAVING   COUNT(ActivityId) = 1
)
SELECT b.ActivityId
FROM (
   SELECT 
       a.ActivityId, 
       ROW_NUMBER() over (ORDER BY a.ModifiedOn DESC) as Rownumber
   FROM ActivityPointerBase AS a WITH (NOLOCK)
   JOIN Candidates AS c ON a.ActivityId = c.ActivityId
    )b
WHERE Rownumber = 1

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