检查 SQL 查询是否会返回结果的有效方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1708815/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Efficient way to check if a SQL query will return results
提问by Robert
I would like to write a query that simply returns 1 or 0 depending if there will be results.
我想编写一个只返回 1 或 0 的查询，具体取决于是否会有结果。
I am thinking to use this
我想用这个
IF EXISTS(
select * from myTable
where id=7 and rowInsertDate BETWEEN '01/01/2009' AND GETDATE()
)
SELECT 1
ELSE
SELECT 0
That's the general premise.
这是一般前提。
The final results will actually be a far more complex query, taking one to many parameters and the string built up and executed using sp_executesql
最终结果实际上将是一个复杂得多的查询，采用一对多参数和使用 sp_executesql 构建和执行的字符串
My question is lets say the 'count' would return 376986 and takes 4 seconds to calculate. Is using the IF EXISTS going to stop as soon as it find 1 row that satisfies the criteria.
我的问题是可以说“计数”将返回 376986 并需要 4 秒来计算。使用 IF EXISTS 会在找到满足条件的 1 行后立即停止。
I'm deciding wether to use IF EXISTS or just query the @@ROWCOUNT and see if it is greater than zero.
我决定是使用 IF EXISTS 还是只查询 @@ROWCOUNT 并查看它是否大于零。
I did try some tests and both pretty much ran at the same speed but in 2 years time when there's alot more data is it likely using IF EXISTS is going to be a performance gain or not?
我确实尝试了一些测试，并且两者几乎都以相同的速度运行，但是在有更多数据的 2 年内，使用 IF EXISTS 是否会提高性能？
Thanks
谢谢
采纳答案by AdaTheDev
IF EXISTS should be more efficient, because it is optimised to stop as soon as it find the first row. This is how I would always do this kind of check, not using a COUNT().
IF EXISTS 应该更有效，因为它被优化为一旦找到第一行就停止。这就是我总是做这种检查的方式，而不是使用 COUNT()。
For performance comparison, just ensure you are testing fairly by clearing down the data and execution plan caches (non-production db server only) before each test:
对于性能比较，只需在每次测试之前清除数据和执行计划缓存（仅限非生产数据库服务器），确保您进行公平测试：
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
回答by Nathan Feger
Do you have an index on id and date?
你有关于 id 和 date 的索引吗？
maybe you just want:
也许你只是想要：
select top 1 1 from myTable where id=7 and rowInsertDate > '01/01/2009'
note: this would return 1 if data exists, or nothing otherwise.
注意：如果数据存在，这将返回 1，否则返回。
another edit. This won't return a row with the value null if there is no data, but rather will not return any rows. More like null in its more figurative sense.
另一个编辑。如果没有数据，这不会返回值为 null 的行，而是不会返回任何行。更像是具有象征意义的 null。
回答by Alex Bagnolini
This is the fastest i could get in my projects:
这是我在项目中可以得到的最快速度：
SELECT CASE WHEN EXISTS (
select top 1 1
from myTable
where id=7
and rowInsertDate BETWEEN '01/01/2009' AND GETDATE()
) THEN 1 ELSE 0 END AS AnyData
回答by Ender
I would just write it this way :
我会这样写：
IF EXISTS(
SELECT 0 FROM myTable
WHERE id=7 and rowInsertDate BETWEEN '01/01/2009' AND GETDATE()
)
SELECT 1
ELSE
SELECT 0
That way you don't return any data just check for conditions. I find this query structure super fast.
这样您就不会返回任何数据，只需检查条件即可。我发现这个查询结构超级快。
回答by statenjason
If you don't need 376986 rows and just want to know if something exists then IF EXISTS makes a lot more sense. Also, another helpful bit is to ask for an indexed column (primary key) instead of * because you don't care about the actual data.
如果您不需要 376986 行并且只想知道是否存在某些内容，那么 IF EXISTS 更有意义。此外，另一个有用的位是要求索引列（主键）而不是 * 因为您不关心实际数据。
回答by Kristen
The final results will actually be a far more complex query, taking one to many parameters and the string built up and executed using sp_executesql
最终结果实际上将是一个复杂得多的查询，采用一对多参数和使用 sp_executesql 构建和执行的字符串
I think you, at least, need the full FROM, JOIN and WHERE syntax, otherwise your actual query may find nothiong (e.g. by adding an INNER JOIN that was not in the original IF EXISTS query and turns out to not be satisfied).
我认为您至少需要完整的 FROM、JOIN 和 WHERE 语法，否则您的实际查询可能找不到任何内容（例如，通过添加一个不在原始 IF EXISTS 查询中的 INNER JOIN 并且结果不满意）。
If you are going to that trouble you might want to get the PKs into some sort of "Batch ID Holding Table" so that you can just reference the PKs for the second "Presentation" part of your query.
如果您遇到了那个麻烦，您可能希望将 PK 放入某种“批次 ID 保存表”中，以便您可以只引用查询的第二个“演示”部分的 PK。
What are you planning to do if you get 376,986 results? If you are going to show them to the user on screen, with some sort of paging, then having the results in a "Batch ID Holding Table" might assist with that (although, obviously, any additions / deletions etc. to the udnerlying data will muck up the paged display).
如果您得到 376,986 个结果，您打算做什么？如果您要在屏幕上通过某种分页将它们显示给用户，那么将结果放在“批次 ID 保存表”中可能会有所帮助（尽管显然，对底层数据的任何添加/删除等将弄乱分页显示）。
Alternatively, if you are going to be using paging just use TOP / LIMIT / SET ROWCOUNT to restrict the results to the first page full (make sure you have an ORDER BY so the sequence is repeatable), and then sort out what to do for Page 2 when the user presses the NEXT-PAGE button (we tackle that by the NEXT-PAGE button containing the PK of the last record displayed, in sort-order, so that the Next Page can resume from that point onwards).
或者，如果您打算使用分页，只需使用 TOP / LIMIT / SET ROWCOUNT 将结果限制在第一页满（确保您有一个 ORDER BY 以便序列是可重复的），然后整理出要做什么当用户按下 NEXT-PAGE 按钮时的第 2 页（我们通过 NEXT-PAGE 按钮包含显示的最后一条记录的 PK，按排序顺序处理，以便下一页可以从该点开始继续）。
The Query Optimiser will do different things depending on what the SELECT list is - so asking "IF EXISTS" followed by "SELECT Col1, COl2, ... FROM ..." may in effect mean that you run the complete query twice, differently, using different cached data and query plans, so overall that may be more of a strain on your server, and cause the users to wait longer, than just geting the first page / 100 rows etc.
查询优化器将根据 SELECT 列表的内容执行不同的操作 - 因此询问“IF EXISTS”后跟“SELECT Col1, COl2, ... FROM ...”可能实际上意味着您两次以不同的方式运行完整的查询，使用不同的缓存数据和查询计划，因此总体而言，这可能会给您的服务器带来更多压力，并导致用户等待更长的时间，而不仅仅是获取第一页/100 行等。
SQL Server will cache the query plan for sp_ExecuteSQL, but make sure you parameterise the query so that the cached plan is resued where possible
SQL Server 将缓存 sp_ExecuteSQL 的查询计划，但请确保您参数化查询，以便在可能的情况下重新使用缓存的计划
回答by MandoMando
I think Alex Bagnolini's answer is correct. The system wouldn't let me comment on his answer (new acct). The only modification I'd make is to change the second 1 to id.
我认为 Alex Bagnolini 的回答是正确的。系统不会让我评论他的回答（新帐户）。我要做的唯一修改是将第二个 1 更改为 id。
Sometimes reducing the list in the project section (that's the column list) allows the db engine to hit the index only, and not the table, thus faster. This depends on your DB engine, and index structure/size, of course. (all rowInsertDate dates should be < getDate(), so you can skip that comparison)
有时减少项目部分中的列表（即列列表）允许数据库引擎仅命中索引，而不是表，从而更快。当然，这取决于您的数据库引擎和索引结构/大小。（所有 rowInsertDate 日期都应该是 < getDate()，因此您可以跳过该比较）
SELECT CASE WHEN EXISTS ( select top 1 id from myTable where id=7 and rowInsertDate > '01/01/2009' ) THEN 1 ELSE 0 END AS AnyData
SELECT CASE WHEN EXISTS（从 myTable 中选择前 1 个 id，其中 id=7 和 rowInsertDate > '01/01/2009'）THEN 1 ELSE 0 END AS AnyData
回答by Philip Kelley
First off, you should try to dummy up a database containing as much data as you think you (or your successors) might have to deal with in two years. Then your tests will be a lot more productive.
首先，您应该尝试创建一个包含您（或您的继任者）在两年内可能需要处理的数据量的数据库。那么你的测试将会更有效率。
IF EXISTS() will be faster, since the database engine only has to find a first matching record to your criteria. It will of course be faster still with proper indexes.
IF EXISTS() 会更快，因为数据库引擎只需要找到符合您条件的第一个匹配记录。当然，使用适当的索引会更快。
Another hint, don't use *, since you don't actually need to retrieve columns.
另一个提示，不要使用 *，因为您实际上不需要检索列。
IF EXISTS(select 1 from myTable where id=7 and rowInsertDate BETWEEN '01/01/2009' AND GETDATE())
...should (from what I've read) work a bit faster.
...应该（根据我读过的内容）工作得更快一点。
