[SOLVED] Fastest way to determine if record exists

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.

Sample query:

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 = ?;

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.

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)

Solution

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.