哪个更快COALESCE或ISNULL？

I understand the difference between these functions but my question is when checking for a single null value would ISNULL be any quicker than using COALESCE?

我理解这些函数之间的区别，但我的问题是，当检查单个空值时，ISNULL会比使用COALESCE更快吗？

e.g

例如

COALESCE(SELECT TOP 1 SomeValue FROM SomeTable, 0)

ISNULL(SELECT TOP 1 SomeValue FROM SomeTable, 0)

6 个解决方案

#1

Had a quick look into this as it's interesting to see a number of different comparisons out there on the performance between the 2. I think this blog post by Adam Machanic is most accurate in the performance benchmarking done on this topic, where the bottom line is:

快速浏览一下，因为看到2之间的性能有很多不同的比较很有意思。我认为Adam Machanic的这篇博文在这个主题的性能基准测试中是最准确的，其中底线是：

... and ISNULL appears to pretty consistently out-perform COALESCE by an average of 10 or 12 percent

......而且ISNULL的表现一直比COALESCE平均高出10％或12％

However, I share the same view as what he then goes on to say - that the difference is pretty negligible - e.g. in his tests, a million executions showed up on average a 0.7s difference. Is it worth it? I'd suggest there are probably bigger areas to optimise. But read the article, it's a good read.

但是，我和他接着说的那样有着相同的看法 - 这种差异可以忽略不计 - 例如在他的测试中，一百万人的处决平均显示出0.7秒的差异。这值得么？我建议可能有更大的优化领域。但阅读文章，这是一个很好的阅读。

#2

In this case, ISNULL is the best option. Because, SQL Server interprets COALESCE function as a CASE statement. So, your query

在这种情况下，ISNULL是最佳选择。因为，SQL Server将COALESCE函数解释为CASE语句。所以，你的查询

COALESCE(SELECT TOP 1 SomeValue FROM SomeTable, 0)

COALESCE（从SomeTable中选择前1个SomeValue，0）

will be written by SQL Server as

将由SQL Server编写为

CASE
案件
WHEN (SELECT TOP 1 SomeValue FROM SomeTable) IS NOT NULL
WHEN（选择TOPT SomeValue FROM SomeTable）不是NULL
THEN (SELECT TOP 1 SomeValue FROM SomeTable)
然后（从SomeTable中选择前1个SomeValue）
ELSE 0
ELSE 0
END
结束

If you observe the above interpretation, "SomeTable" will be scanned twice. But ISNULL will be evaluated only once.

如果您观察到上述解释，“SomeTable”将被扫描两次。但ISNULL只会被评估一次。

#3

ISNULL will be faster i think because it has lesser function/code implementation for itself making it faster than COALESCE

我认为ISNULL会更快，因为它本身的功能/代码实现较少，使其比COALESCE更快

#4

Please check the link to prefer ISNULL over COALESCE when given the choice is that ISNULL tends to produce query plans that are more efficient than COALESCE.

如果ISNULL倾向于生成比COALESCE更有效的查询计划，请检查链接以优先选择ISNULL而不是COALESCE。

ISNULL Vs COALESCE

ISNULL与COALESCE
ISNULL vs COALESCE speed test

ISNULL vs COALESCE速度测试
Please check the Performance: ISNULL vs. COALESCE

请查看表演：ISNULL与COALESCE

#5

For what its worth You have a very specific use case so i used a sample of your actual question on the first in value on a table that came to mind and controlled the script for other variables. I assumed someval was an int as you used 0. My suggestion is that you choose your specific someval/sometable case and do the test yourself.

为了它的价值你有一个非常具体的用例，所以我使用了一个实际问题的样本在一个表上的第一个值，并且控制了其他变量的脚本。我假设someval是一个int，因为你使用0.我的建议是你选择你特定的someval / sometable case并自己做测试。

declare @val  int = 0;
declare @time1  Datetime2 = getdate();
declare @time2  Datetime2 = getdate();
Select @time1 = GETDATE();
while @MyCounter < 1000000
Begin
 Select @val = ISNULL((SELECT TOP 1 LocationID FROM location), 0)
 Select @MyCounter +=1;
END
Select @time2 = GETDATE();
Print datediff(millisecond,@time1,@time2);
Select @MyCounter = 0;
Select @time1 = GETDATE();
while @MyCounter < 1000000
Begin
 Select @val = COALESCE((SELECT TOP 1 LocationID FROM Location), 0)
 Select @MyCounter +=1;
END
Select @time2 = GETDATE();
Print datediff(millisecond,@time1,@time2);

The results were pretty dramatic, 11270 for isnull and 18930 for coalesce. Reversing the order of the loops as a second test produced 18260 for coalesce and 10810 for isnull. For your specific case I would say isnull is clearly faster.

结果非常引人注目，isnull为11270，cowellce为18930。作为第二次测试，反转循环的顺序产生18260用于合并，10810用于isnull。对于你的具体情况，我会说isnull显然更快。

This is not to say it is better in any other given situation. Using straight up values, or nvarchars or bits instead of int, or a column that is not a primary key, or Nesting isnull versus adding parameters to coalesce could change things.

这并不是说在任何其他特定情况下它都会更好。使用直接值，或nvarchars或位而不是int，或者不是主键的列，或者Nesting isnull与添加参数以进行合并可能会改变一些事情。

This only addresses the question as it was asked.

这仅解决了问题。

#6

-2

I just ran a test on my own db. About 700k rows.

我刚刚对自己的数据库进行了测试。大约700k行。

SELECT COUNT(*) FROM table WHERE COALESCE(field_1,field_2,field_3,field_4) IS NOT NULL

Result of 12106 obtained in 56 seconds.

在56秒内获得12106的结果。

SELECT COUNT(*) FROM table WHERE field_1 IS NOT NULL OR field_2 IS NOT NULL OR field_3 IS NOT NULL OR field_4 IS NOT NULL

Result of 12106 obtained in 0.00 seconds.

在0.00秒内获得12106的结果。

#1