COALESCE 或 ISNULL 哪个更快? [英] Which is quicker COALESCE OR ISNULL?

问题描述

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

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?

例如

COALESCE(SELECT TOP 1 SomeValue FROM SomeTable, 0)

对比

ISNULL(SELECT TOP 1 SomeValue FROM SomeTable, 0)

推荐答案

快速浏览一下这个，因为在 2 之间的性能上看到许多不同的比较很有趣.我认为 这篇 由 Adam Machanic 撰写的博文是最准确的关于这个主题的性能基准测试，底线是:

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:

... 并且 ISNULL 看起来很漂亮始终优于 COALESCE平均 10% 或 12%

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

然而，我与他接下来所说的观点相同——差异可以忽略不计——例如在他的测试中，一百万次执行平均有 0.7 秒的差异.这值得么?我建议可能有更大的领域需要优化.但是阅读文章，这是一个很好的阅读.

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.

这篇关于COALESCE 或 ISNULL 哪个更快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！