0 votes
1 view
in SQL by (20.3k points)

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)

1 Answer

0 votes
by (40.4k points)

The following query will outperform all the given suggestions as it'll terminate execution after it'll find the first record.

Query:

SELECT TOP 1 products.id FROM products WHERE products.id = ?; 

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...