为什么 IsNull 比合并(相同查询)慢两倍?

问题描述：

我们在 SQL Server 2008 (SP1) - 10.0.2531.0 (X64) - Win2008 SP2 (X64) 上遇到了一个奇怪的情况.

We met a strange situation on 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 万行.

UPD: All columns in comparison (except IDs) are varchar(~30...200)
T1 is ~130mln rows, T2 is ~300k rows.

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

These query on rather big Dev server run ~5 hours - this is slow, but what we can do?

虽然我们调查了可能的优化方法 - 我们发现，在上面的查询中将 "isnull" 更改为 "coalesce" 可以获得双倍的性能增益 - 现在查询运行~2 小时

And while we investigated possible ways of optimisation - we found, that changing "isnull" to "coalesce" in query above gives double performance gain - and query now run for ~2 hours

UPD:当我们删除所有 ISNULL 检查并仅使用 t1.vchCol1 = t2.vchCol1 时，查询在 40 分钟后完成.

UPD: When we remove all ISNULL checks and use just t1.vchCol1 = t2.vchCol1 the query finishes after 40mins.

问题是:这是已知行为吗?我们应该避免在任何地方使用 IsNull 吗?

Question is: Is this known behavior and we should avoid using IsNull everywhere?

答

我想知道通过明确地拆分案例您是否会看到改进:

I wonder if you'd see an improvement by splitting the cases out explicitly:

...
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))
...

为什么 IsNull 比合并(相同查询)慢两倍?

相关推荐