“选择最佳1 1”。 VS“ IF EXISTS（SELECT 1”） [英] "SELECT TOP 1 1" VS "IF EXISTS(SELECT 1"

问题描述

我有一些.NET代码，以中等高的间隔检查SQL记录的存在。我想使此检查尽可能便宜。

I have some .NET code that checks for the existence of a SQL record at a moderately-high interval. I am looking to make this check as "cheap" as possible.

我想知道两个查询的特征：

I'm wondering the characteristics of two queries:

IF EXISTS(SELECT 1
          FROM   BigTable
          WHERE  SomeColumn = 200)
  SELECT 1 AS FOUND
ELSE
  SELECT 0 AS FOUND

VS

SELECT TOP 1 1
FROM   BigTable
WHERE  SomeColumn = 200 

它们都产生类似的执行计划。但是SELECT TOP 1 1似乎执行起来更快：要解析的查询更少，并且当找不到记录时，它在管道中的发送更少。我还假设它在客户端运行得更快，因为我只需要检查Record Count，而不是整理IF EXISTS的返回值即可。

They both produce similar execution plans. But the SELECT TOP 1 1 seems to execute faster: Less query to parse and when record is not found, it sends less down the pipe. I'm also assuming it runs faster at the client because I just need to check the Record Count, rather than marshaling the return value of IF EXISTS.

大部分性能好处微不足道。但是，如果两者始终返回相同的结果，那为什么不选择稍微更快的方法呢？

Most of the performance benefits are negligible. But if both consistently return the same result, then why not choose the slightly faster method?

SELECT TOP 1 1是最好的方法吗？来检查.NET中是否存在记录？

Is "SELECT TOP 1 1" THEE best way to check for an existence of a record in .NET?

（我们使用.NET 3.5，我试图避免使用LINQ，因为在应用程序的其他地方未使用它我们也有一些旧的VB6应用程序正在迁移/重写，因此它们可能也需要执行。）

(We use .NET 3.5, and I am trying to avoid LINQ because it is not used elsewhere in the application. We also have some legacy VB6 apps that we are migrating/rewriting, so they may need to execute this as well.)

编辑：
有关设计的更多细节。该记录是标题。还有另一个具有子记录的表，当找到此标头时将读取/分析该子记录。缺少记录是一件好事：无需做任何工作。

Just a little more detail on design. This record is a "header". There is another table that has child records that will be read/parsed when this header is found. The lack of a record is a good thing: there is no work to do.

EDIT2：缺少满足条件的记录会经常发生。

The lack of a record that meets the condition will occur more often. They come in sporadic waves.

推荐答案

我建议如果存在（选择* ...） ，除非这实际上导致性能问题。它以比其他方式更好的方式表达查询的意图。

I'd recommend IF EXISTS(SELECT * ...), unless this is actually causing a performance issue. It expresses the intent of the query in a much better understood fashion than alternatives.

我会避免 COUNT（ *）（如当前答案一样），除非您实际上需要表中的行数。

I'd avoid COUNT(*) (as in the current answers) unless you actually need the count of rows from the table.

如果您想从结果中检查行数的效率，我可能会选择：

If you want the "efficiency" of checking the rowcount from the result, I'd probably go for:

select 1 where exists(select * from BigTable where SomeColumn=200)

与第二个查询生成的结果集相同（ 0或1行）

Which produces the same result set as your second query (either 0 or 1 row)

这篇关于“选择最佳1 1”。 VS“ IF EXISTS（SELECT 1”）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！