IN 或 OR 哪个更快？
在 T-SQL 中什么更快？
DELETE * FROM ... WHERE A IN (x,y,z)
或者
DELETE * FROM ... WHERE A = x OR A = y OR A = z
在我的例子中，x、y 和 z 是存储过程的输入参数。 我正在尽力使 DELETE 和 INSERT 语句的性能达到最佳水平。
In T-SQL what's faster?
DELETE * FROM ... WHERE A IN (x,y,z)
Or
DELETE * FROM ... WHERE A = x OR A = y OR A = z
In my case x, y and z are input parameters for the stored procedure. And I'm trying to get the performance of my DELETE and INSERT statements to the best of my abilities.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（9）
别想；
我强烈建议您在考虑速度问题时不要依赖您或其他任何人的直觉。 相反，请尝试这两种选项，并进行某种分析/运行时间测量，并找出在您的情况下哪个选项更快。
Don't think; profile.
I urge you not to rely on intuition, yours or anyone else's, when considering questions of speed. Instead, try both options, with some kind of profiling/run time measurement, and find out which is faster in your circumstances.
“IN”将被翻译为一系列“OR”...如果您查看带有“IN”的查询的执行计划，您会发现它已将其扩展。
在我看来，使用“IN”要干净得多，特别是在较大的查询中，它使其更具可读性。
"IN" will be translated to a series of "OR"s...if you look at the execution plan for a query with "IN", you'll see it has expanded it out.
Much cleaner to use "IN" in my opinion, especially in larger queries it makes it much more readable.
在测试服务器上编写两个存储过程，一个使用
IN，另一个使用OR。 运行每个过程 10,000（或 1,000,000 次，或其他）次，并比较时间。一般来说，这几乎是对哪种方法更快的问题有一个好的答案的“唯一”方法：编写简单的计时测试用例，并运行它们很多很多次。
Write two stored procedures, one using
IN, the other usingOR, on a test server. Run each procedure 10,000 (or 1,000,000, or whatever) times, and compare the timings.In general, this is pretty much the "only" way to have a good answer to the question of which approach is faster: write simple timing test cases, and run them many, many times.
在
SQL Server中，优化器将为这些查询生成相同的计划。In
SQL Server, the optimizer will generate identical plans for these queries.他们应该根据我的经验生成完全相同的计划
看看该计划
they should generate the same exact plan from my experience
take a look at the plan
如果
A是一个计算，它将使用IN执行一次，并使用OR执行N次。If
Ais a computation, it will be performed once usingINand N times usingOR.无论 A 是否是计算或列，看起来 SQL Server 2005 都会将 IN 转换为 OR 子句。
Regardless of whether or not A is a computation or column, looks like SQL Server 2005 converts IN to OR clauses.
SQL Server 下绝对最快的是使用带有 INNER JOIN 的 DELETE。 对于三个值，您不会注意到差异，但对于更多值（我们正在执行数千个），差异是显着的。 您可以将您的值存储到临时表中，然后加入其中。
例如，
您还可以添加可选的 where 子句。
The absolute fastest under SQL Server is to use a DELETE with an INNER JOIN. With three values you wont notice the difference, but with more values (we are doing several thousand) the difference is phenominal. You could stash your values into a temporay table then join onto that.
E.g.
You can also add an optional where clause.
它必须完全相等。 大多数 RDMBS 将
IN转换为OR。当然，如果您认为从
INs到ORs的翻译非常耗时，那么使用ORs的句子会更快;-)更新：我认为
A是一列。It must be exactly equals. Most of RDMBS transalte
INtoORs.Of course, if you consider the translation from
INstoORsto be high time consuming, the sentence withORsis faster ;-)Update: I'm considering that
Ais a column.