确定记录是否存在的最快方法
正如标题所暗示的......我试图找出开销最少的最快方法来确定表中是否存在记录.
As the title suggests... I'm trying to figure out the fastest way with the least overhead to determine if a record exists in a table or not.
示例查询:
SELECT COUNT(*) FROM products WHERE products.id = ?;
vs
SELECT COUNT(products.id) FROM products WHERE products.id = ?;
vs
SELECT products.id FROM products WHERE products.id = ?;
说 ? 与 'TB100' 交换...第一个和第二个查询将返回完全相同的结果(例如... 1 用于此对话).最后一个查询将按预期返回 'TB100'，如果 id 不在表中，则不返回任何内容.
Say the ? is swapped with 'TB100'... both the first and second queries will return the exact same result (say... 1 for this conversation). The last query will return 'TB100' as expected, or nothing if the id is not present in the table.
目的是确定id是否在表中.如果不是，程序接下来将插入记录，如果是，程序将跳过它或根据本问题范围之外的其他程序逻辑执行 UPDATE 查询.
The purpose is to figure out if the id is in the table or not. If not, the program will next insert the record, if it is, the program will skip it or perform an UPDATE query based on other program logic outside the scope of this question.
哪个更快且开销更少?(这将在每次程序运行时重复数万次，并且每天运行多次).
Which is faster and has less overhead? (This will be repeated tens of thousands of times per program run, and will be run many times a day).
(通过 M$ 提供的 JDBC 驱动程序从 Java 对 M$ SQL Server 运行此查询)
(Running this query against M$ SQL Server from Java via the M$ provided JDBC driver)
推荐答案
SELECT TOP 1 products.id FROM products WHERE products.id = ?; 将优于您的所有建议，因为它将终止执行在它找到第一条记录之后.
SELECT TOP 1 products.id FROM products WHERE products.id = ?; will outperform all of your suggestions as it will terminate execution after it finds the first record.
相关文章