Fastest way to determine if record exists
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 = ?;
? 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.
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).
(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 = ?; will outperform all of your suggestions as it will terminate execution after it finds the first record.