为什么 IsNull 比合并(相同查询)慢两倍? [英] Why IsNull is twice slow as coalesce (same query)?
问题描述
我们在 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 比合并(相同查询)慢两倍?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!