为什么在相同查询中，IsNull比Coalesce慢两倍？

Question

我们在 SQL Server 2008 (SP1) - 10.0.2531.0 (X64) - Win2008 SP2 (X64) 上遇到了一个奇怪的情况。以下是一个复杂的查询语句：

select t1.id, t2.id 
from t1, t2
where 
     t1.id = t2.ext_id
     and isnull(t1.vchCol1, 'Null') = isnull(t2.vchCol1, 'Null')
     and isnull(t1.vchCol2, 'Null') = isnull(t2.vchCol2, 'Null')
     .... and about 10 more comparisons with Isnull

UPD: 除了ID之外，比较中的所有列都是varchar(~30...200)。
T1有约1.3亿行，T2有约30万行。

这些查询在相当大的Dev服务器上运行大约5个小时 - 这很慢，但我们能做什么呢？

在我们调查可能的优化方法时，我们发现将查询中的"isnull"更改为"coalesce"可以使性能提高一倍 - 现在查询只需要大约2个小时就可以完成。 UPD: 当我们删除所有的ISNULL检查并仅使用t1.vchCol1 = t2.vchCol1时，查询在40分钟后完成。 问题是：这是已知的行为吗？我们应该避免在任何地方使用IsNull吗？

- zmische

---

我认为(t1.vchCol1 = t2.vchCol1)在性能上比运行一个函数要好（它喜欢强制进行表扫描），但它需要将ANSI_NULLS设置为OFF。 - cHao

你看过查询执行计划了吗？ - David

我确定你无法改变这一点，但这只是 NULL 值是邪恶的又一个原因！ - KM.

我们将尝试通过SQL逻辑（使用GAP值填充列而不是Nulls）来消除“Nulls”，并使用下面建议的“IS NULL OR”结构。 - zmische

@zmische - 你找到两者之间差异的原因了吗？ - Martin Smith

Answer 1

我在想，如果你明确地将这些情况分开，是否会看到改进：

...
AND ((t1.vchCol1 = t2.vchCol1) OR (t1.vchCol1 IS NULL AND t2.vchCol1 IS NULL))
AND ((t1.vchCol2 = t2.vchCol2) OR (t1.vchCol2 IS NULL AND t2.vchCol2 IS NULL))
...

- Joe Stefanelli

---

同意。SQL Server已经理解了这个模式。当查看链接答案时（与参数化选择语句相比，稍有不同，因为它是与另一列进行比较），我发现它在计划中只是一个简单的等式。 - Martin Smith

你认为coalesce比Isnull快2倍是正常的行为，我们所能做的就是将情况分开，以便SQL清楚地了解如何进行优化吗？那么这不应该被视为SQL Server中的性能错误吗？ - zmische

@zmische - 我本以为coalesce和isnull表达式都是不可搜索和次优的。不确定为什么您会看到两者之间的性能差异。计划有何不同？它们中的一个比另一个具有更准确的行估计吗？哈希连接中表的顺序是相同还是相反？SET STATISTICS IO ON和SET STATISTICS TIME ON显示逻辑读取和CPU时间的情况如何？也许在ISNULL版本中，由于不同的估计值，哈希连接的内存授权不足。 - Martin Smith

@Martin - 谢谢你的问题。我没有确切的统计数据 - 另一个团队现在正在使用那个服务器。但是我会尝试在下一次运行中解决这个问题。 - zmische

@zmische - 我认为内存授权不足和哈希溢出是相当可能的原因。您应该能够从估计计划中看到这一点，而无需运行整个查询，只需查看双方的估计行数和行大小即可。 - Martin Smith

Answer 2

大多数关于此主题的文章似乎相互矛盾。 ISNULL比COALESCE略快。 ISNULL和COALESCE之间的区别

COALESCE基本上转换为CASE表达式，而ISNULL是数据库引擎中内置的实现。
…
这将产生性能差异，使用COALESCE的查询通常会更差。

ISNULL与COALESCE 我在几个不同的服务器上多次运行了这些测试，ISNULL似乎比COALESCE表现更为稳定，平均快10或12％。但是，在我的服务器上，每次测试的大约平均运行时间为6秒和5.3秒之间（百万次执行），这只是一些场景中使用这些函数时所付出的功能性和标准兼容性牺牲，几乎没有什么价值。 COALESCE vs ISNULL vs IS NULL OR IS NULL OR是最佳表现者，而它与其他两个函数之间的差异很小。

- Lieven Keersmaekers

---

但是在我们的情况下，切换到COALESCE后，我们从5小时的时间提高到了2小时。原因是什么？对于我来说，ISNULL应该更快，这就是我感到困惑的原因。 - zmische

仅仅补充一下，在 ISNULL(subquery,X) 或者 COALESCE(subquery,X) 的情况下，后者会对子查询进行两次求值。 - Martin Smith

Answer 3

您可以考虑在每个表中添加一个计算列来保存校验和值。然后，在ID列和校验和值上创建索引，最后在连接中使用校验和值。类似这样：

Alter Table T1 Add CheckSumId As CHECKSUM(vchCol1, vchCol2, vchCol3)
Alter Table T2 Add CheckSumId As CHECKSUM(vchCol1, vchCol2, vchCol3)

Create NonClustered index idx_T1_Checksum On T1(id, CheckSumId)
Create NonClustered index idx_T2_Checksum On T2(ext_id, CheckSumId)

然后您的查询将变成...

select t1.id, t2.id 
from t1 Inner Join t2
       On t1.id = t2.ext_id
       And T1.CheckSumId = T2.CheckSumId
where  isnull(t1.vchCol1, 'Null') = isnull(t2.vchCol1, 'Null')
     and isnull(t1.vchCol2, 'Null') = isnull(t2.vchCol2, 'Null')

当然，这将会使用额外的索引空间，但只是2个整数，应该非常高效。每次插入、更新和删除都需要维护另一个索引，因此会有性能损失。不过，我认为这对性能会产生很大影响。

- George Mastros

Answer 4

我知道这已经是一年后了，但是...

对于这种逐列比较，您可以考虑使用EXCEPT。此外，EXCEPT将NULL视为另一个值，而不是像我所说的“它可能是任何东西！”

“当您比较行以确定不同的值时，两个NULL值被视为相等。” --来自http://msdn.microsoft.com/en-us/library/ms188055.aspx

- Chris Garcia