SQLServer Execpt和not in 性能区别

主要讲 except 和 not in 的性能上的区别。 代码如下: CREATE TABLE tb1(ID int) CREATE TABLE tb2(ID int) BEGIN TRAN DECLARE @i INT = 500 WHILE @i > 0 begin INSERT INTO dbo.tb1 VALUES ( @i — v – int ) SET @i = @i -1 end COMMIT我测试的时候tb1 是1000，tb2 是500 代码如下: DBCC FREESYSTEMCACHE (‘ALL’,’default’); SET STATISTICS IO ON 在SQL Server中，`EXCEPT` 和 `NOT IN` 是两个用于查询数据的子句，它们都可以用来找出在一个集合中但不在另一个集合中的数据。然而，它们在性能方面可能存在显著的差异，尤其是在处理大量数据时。本文将深入探讨这两种方法的性能区别，并通过给出的示例代码进行分析。 让我们看下`EXCEPT`子句的执行情况。在给出的代码中，创建了两个表`tb1`和`tb2`，然后用一个循环插入数据，使得`tb1`有1000条记录，`tb2`有500条记录。执行`EXCEPT`操作时，SQL Server选择了Merge Join作为执行计划。这个操作涉及到对两个表的排序，然后进行合并以找到不匹配的记录。从执行计划中可以看出，`tb1`和`tb2`都进行了排序，然后进行合并。性能数据显示，CPU时间和占用时间相对较低，且扫描计数和逻辑读取次数较少。 接下来，我们看`NOT IN`的执行情况。同样，使用`NOT IN`子句时，SQL Server选择了Hash Match作为执行计划。这个操作涉及到对`tb2`的全表扫描，然后与`tb1`进行哈希匹配来找出不匹配的记录。在这个过程中，还使用了Nested Loops和Row Count Spool操作，这通常会导致更高的资源消耗。执行计划显示，`NOT IN`的执行时间远高于`EXCEPT`，并且对`tb2`的扫描计数和逻辑读取次数也显著增加。 性能上的差异主要源于以下几点： 1. **排序与哈希计算**：`EXCEPT`使用了Merge Join，需要对数据进行排序，而`NOT IN`使用了Hash Match，需要构建哈希表。对于大数据量，排序通常比哈希计算更耗时。 2. **数据重复**：如果`NOT IN`子查询的结果包含重复值，那么哈希匹配可能会导致额外的计算和资源消耗。`EXCEPT`则不会受此影响，因为它会自动去除重复值。 3. **空值处理**：如果`tb2`中有空值，`NOT IN`可能会遇到问题，因为它会将空值视为一个单独的值。`EXCEPT`则可以正确地处理这种情况，不会将空值误认为是匹配项。 4. **优化器选择**：SQL Server的查询优化器可能会根据数据统计信息和当前系统状态选择不同的执行计划。在某些情况下，`NOT IN`可能因为优化器的决定而表现出更好的性能。 尽管`EXCEPT`和`NOT IN`在逻辑上等价，但它们在实际执行时的性能表现可能有很大差异。在处理大规模数据时，`EXCEPT`通常会提供更好的性能，特别是在涉及排序和不存在重复值的情况下。然而，对于特定的数据模式和系统配置，`NOT IN`的性能也可能有所改善。因此，当面临性能优化需求时，应该根据具体场景进行测试，以确定最合适的查询方式。同时，考虑使用索引、重写查询或使用其他SQL语句（如`LEFT JOIN`）也可能有助于提高性能。