使用EXCEPT查询时，SQL Server的性能问题

Question

一般来说，我的查询语句包含三个独立的SELECT查询，我使用EXCEPT和UNION操作符。当单独执行这些查询时，结果会在1-2秒内返回，但是如果使用EXCEPT操作符，查询可能需要几个小时。

查询结构（简化）如下：

SELECT DISTINCT FIELD_1, FIELD_2, FIELD_3 FROM MYTABLE

EXCEPT

(
SELECT DISTINCT FIELD_1, FIELD_2, FIELD_3 FROM MYTABLE WHERE XXX

UNION

SELECT DISTINCT FIELD_1, FIELD_2, FIELD_3 FROM MYTABLE WHERE YYY
)

有没有任何方法可以加快整个查询的速度，或者EXCEPT运算符通常如此缓慢，应该避免使用？

- user11034064

---

WHERE NOT EXISTS? - jarlh

表格有列（columns），而非字段（fields）。 - jarlh

我会从这里开始，并在这里和这里提供资料。由于你在所有三个查询中使用了相同的表，因此我确定有一个更加优美的解决方案，例如 @jarlh 提供的“NOT EXISTS”，但你没有展示执行计划，这是必要的。我不明白为什么你需要在这里使用“UNION”和“EXCEPT”。此外，“DISTINCT”是不必要的。 - S3S

那么这样的语句如何？SELECT DISTINCT FIELD_1, FIELD_2, FIELD_3 FROM MYTABLE WHERE NOT XXX AND NOT YYY？ - Peter B

你将看到Aaron的结论，即“这只是一种非常冗长的方式，告诉你，在查找表A中所有行时，如果某些条件在表B中不存在，使用NOT EXISTS通常是最好的选择”。 - S3S

Answer 1

您可以使用 GROUP BY 来实现此操作。

SELECT FIELD_1, FIELD_2, FIELD_3 
FROM MYTABLE
GROUP BY FIELD_1, FIELD_2, FIELD_3 
HAVING MAX(CASE WHEN (XXX) OR (YYY) THEN 1 ELSE 0 END) = 0

- Martin Smith

---

好的答案。重要提示：任何使用此（或大多数其他答案）的人应确保如果它表示复合条件，则将XXX放在自己的括号中。当然，YYY也是如此。 - Richard II

确实，思路不错。这个版本可能会比我的简化版（v3）提供更好的性能，这取决于XXX或YYY的逻辑。 - Alexander Volok

@RichardII - 谢谢。我还是加上它们以保险起见。 - Martin Smith

@RichardII. . . 如果过滤器变得复杂，例如(OR + AND)，那么这将是可怕的。 - Yogesh Sharma

@Yogesh，你在评论中提到的“This”指的是什么？查询本身？我建议的更改？另外，你认为会有什么问题？性能？可读性？ - Richard II

Answer 2

首先，我不会使用“field to with except”，最好使用ID，因为它是可索引的。

下面的查询性能会更快。

这就是我会这样做的方式。

SELECT DISTINCT FIELD_1, FIELD_2, FIELD_3 FROM MYTABLE
where Id not in (
SELECT Id FROM MYTABLE WHERE XXX and YYY
)

- Alen.Toma

---

OP可能没有向表中添加新列所需的权限，但这也并非必要。请参见此答案的更新：https://dev59.com/ebHma4cB1Zd3GeqPPrZL#54594763，了解一种更简单的方法。 - Richard II

我看到你假设表中有一个主键。在表中拥有一个主键总是很好的。 - Alen.Toma

Answer 3

有时将工作负载拆分成多个步骤可以使查询优化器的任务更加简单，特别是当执行需要多个小时时。

-- Step 1
SELECT FIELD_1, FIELD_2, FIELD_3 INTO #Step1 FROM
(
SELECT FIELD_1, FIELD_2, FIELD_3 FROM MYTABLE WHERE XXX    
UNION  
SELECT FIELD_1, FIELD_2, FIELD_3 FROM MYTABLE WHERE YYY
) d

-- Step 2:
SELECT DISTINCT FIELD_1, FIELD_2, FIELD_3 FROM MYTABLE    
EXCEPT    
SELECT FIELD_1, FIELD_2, FIELD_3  FROM #Step1

请注意，一些DISTINCT子句已被移除。

---

更新，第三版。 基于 OP 的最后一次更新：

选项卡：它是相同的表格。第一个查询基本上给出了几乎整个表格，第二个和第三个查询是我需要从第一个查询结果中去除的子集

我相信整个查询可以重写为：

SELECT DISTINCT FIELD_1, FIELD_2, FIELD_3 FROM MYTABLE ext
WHERE NOT EXISTS (

SELECT * FROM (
    SELECT FIELD_1, FIELD_2, FIELD_3 FROM MYTABLE  
    WHERE  ( (XXX) OR (YYY))  -- original filter
)   list 
WHERE
    list.FIELD_1 = ext.FIELD_1
AND list.FIELD_2 = ext.FIELD_2
AND list.FIELD_3 = ext.FIELD_3
) 

- Alexander Volok

---

在步骤1中，您缺少一个FROM。 - S3S

@benjaminmoskovits，CTE不会将该部分隔离成自己的执行。因此，最终整个SQL命令将编译为一个通用查询计划。使用临时表的想法是为了分割工作负载，并使优化器更容易生成高效的查询计划。然而，OP提供了一个关键的评论，即所有操作都涉及单个表，因此我添加了更新来回答。 - Alexander Volok

@Alexander，我在你发帖后才写下我的回答。在我发布我的回答之后，我看到你更新了你的帖子，包括相同的概念。时间问题。我保证我没有抄袭你 :-) - Richard II

简化不可行。如果FIELD_1，FIELD_2，FIELD_3组中有匹配和不匹配xxx/yyy条件的混合行，则会返回它们。OP中的查询将排除存在与这些条件匹配的单个行的组。 - Martin Smith

@RichardII，是的，当OP更新问题时，这是显而易见的事情，但Martin发表了正确的评论，无论如何，我提供了另一个简化版本。 - Alexander Volok

Answer 4

我会使用带有CTE的NOT EXISTS：

WITH CTE AS (
     <your union query>
)
SELECT mt.*
FROM MYTABLE mt
WHERE NOT EXISTS (SELECT 1 FROM CTE c WHERE c.FIELD_1 = mt.FIELD_1 AND . . . );   

- Yogesh Sharma