Fastest way to determine if record exists

Question

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 1

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.

Solution 2

EXISTS (or NOT EXISTS) is specially designed for checking if something exists and therefore should be (and is) the best option. It will halt on the first row that matches so it does not require a TOP clause and it does not actually select any data so there is no overhead in size of columns. You can safely use SELECT * here - no different than SELECT 1, SELECT NULL or SELECT AnyColumn... (you can even use an invalid expression like SELECT 1/0 and it will not break).

IF EXISTS (SELECT * FROM Products WHERE id = ?)
BEGIN
--do what you need if exists
END
ELSE
BEGIN
--do what needs to be done if not
END

Solution 3

Nothing can beat -

SELECT TOP 1 1 FROM products WHERE id = 'some value';

You don't need to count to know if there is a data in table. And don't use alias when not necessary.

Solution 4

SELECT CASE WHEN EXISTS (SELECT TOP 1 *
                         FROM dbo.[YourTable] 
                         WHERE [YourColumn] = [YourValue]) 
            THEN CAST (1 AS BIT) 
            ELSE CAST (0 AS BIT) END

This approach returns a boolean for you.

Solution 5

You can also use

 If EXISTS (SELECT 1 FROM dbo.T1 WHERE T1.Name='Scot')
    BEGIN
         --<Do something>
    END 

ELSE    
     BEGIN
       --<Do something>
     END

View more solutions

Comments

[Mike Christensen]

This might be database dependent. For example, counting on Postgres is rather slow.

[SnakeDoc]

Sorry, this is Java talking to M$ SQL via jdbc driver. I'll update my OP.

[Nikola Markovinović]

There is exists also.

[zerkms]

@Nikola Markovinović: how would you use it in this case?

[Nikola Markovinović]

@zerkms Depends on context. If in stored procedure it would be if exists(select null from products where id = @id); if in a query called directly by a client select case when exists (...) then 1 else 0 end.

[lcnicolau]

Does this answer your question? SQL: How to properly check if a record exists

[zerkms]

Doesn't optimizer take it into account itself when searches through PK (or any other unique key)?

[SnakeDoc]

In my case products.id is not a PK... it's just a normal field.

[Declan_K]

He nver stated that is was the PK, but if so then yes the optimizer would take that into account.

[zerkms]

@Declan_K: seems like my magic sphere failed in this case and a column entitled as id isn't PK. So +1 to your advice.

[SnakeDoc]

@zerkms lol go figure... this isn't my created db, but a provided one I have to work with... id field is the actual SKU of the product... and catalogid is the PK which is just a counter.

[Nikola Markovinović]

In spite of its name id is not primary key. So, even though you are not counting you still need to find all matching records, possibly thousands of them. About aliasing - code is constant work in progress. You never know when you'll have to go back. Aliasing helps preventing stupid runtime errors; for example, unique column name that didn't need an alias is not unique any more because somebody created a column of same name in another, joined table.

[CD Jorgensen]

If it is not the PK, I would also suggest making sure there is an index on that column. Otherwise, the query will have to do a table scan instead of a faster table seek.

[AgentSQL]

Yes, you are absolutely right. Aliasing helps a lot but i don't think it makes any difference when not using joins. So, I said don't use it if not necessary. :) And you can find a long discussion here on checking existence. :)

[Nikola Markovinović]

I don't know why I acccepted the term aliasing. Correct term is qualifying. Here is longer explanation by Alex Kuznetzov. About single table queries - it is single table now. But later, when bug is discovered and you are trying to hold the flood, client is nervous, you join another table just to face error message - easily correctable message, but not at this sweaty moment, a small stroke strikes - and you correct the error remembering never to leave a column ...

[AgentSQL]

Can't ignore that now. Thanks!! :)

[tommy_o]

wouldn't it be better to do select top 1 t From products where products.id = ?;? then it doesnt have to read any value (so if it's looking at a nonclustered index, it won't have to hop back to the PK if it's returning a PK value, etc.)? that's what I typically see and use

[SnakeDoc]

doesn't this have to first execute the SELECT statement, then execute the IF EXISTS statement... causing additional overhead and therefore more processing time?

[Nikola Markovinović]

@SnakeDoc No. Exists works with select in such a fashion that it exits as soon as one row is found. Furthermore exists merely notes the existence of record, not actual values in the record, saving the need to load the row from disk (assuming search criteria is indexed, of course). As for overhead of if - you will have to spend this minuscule time anyway.

[SnakeDoc]

@NikolaMarkovinović interesting point. I'm not sure if an Index exists on this field, and my newbish SQL doesn't know how to find out. I am working with this DB from Java via JDBC and the database is remotely located in a colo somewhere. I've only been provided a "database summary" which just details which fields exist in each table, their type, and any FK or PK's. Does this change anything?

[Nikola Markovinović]

@SnakeDoc To find out about table structure, including foreign keys and indexes, run sp_help table_name. Indexes are essential when it comes to retrieving a few rows out of many, wherther using select top or exists; if they are not present sql engine will have to perform table scan. This is the least desirable table search option. If you are not authorized to create indexes you will have to communicate to technical staff on the other side to find out whether they adjust them automatically or they expect you to suggest indexes.

[SnakeDoc]

+1 for taking the time to explain this to me. Thank you. I'm going to contact them to see if indexes exist and/or if they can be created for this.

[Paul Brewczynski]

It is worth noting that for example SQLITE database accept "Limit X" syntax, so it should be SELECT products.id FROM products WHERE products.id = ? LIMIT 1

[idmean]

Possibly your code works great, but it would be better if you add some additional information so that is better understandable.

[paulkon]

Since the row in Products could be deleted or have its id changed, would the whole code snippet in the answer be wrapped in a transaction as well? Thanks!

[Giulio Caccin]

I think we should consider @nenad-zivkovic answer over this one.

[akd]

How this can be implemented in c# code? I would like to create a method to check if a record exists. the method should return true or false. Do I need to say if exists SELECT 1 and else if SELECT 0 then use ExecuteScalar to get the value?

[Stefan Zvonar]

Can probably omit the Top statement and the * statement to make it a bit more faster, as Exist will exit once it finds a record, so something like this: SELECT CASE WHEN EXISTS (SELECT 1 FROM dbo.[YourTable] WHERE [YourColumn] = [YourValue]) THEN CAST (1 AS BIT) ELSE CAST (0 AS BIT) END

[Karl Gjertsen]

@akd This is not for C#, this is for TSQL.

[amd]

However you force the db to loop over all records, very slow on big tables

[Brandon]

@akd In EF (or rather LINQ to whatever), you can use the .Any() extension method to get EXISTS functionality.

[Konstantin]

Is it possible to directly return the result of EXISTS? Like select exists() ;

[Nenad Zivkovic]

@Konstantin You can do something like SELECT CASE WHEN EXISTS(..) THEN 1 ELSE 0 END;

[UmNyobe]

@amd care to explain why ?

[UmNyobe]

@amd your comment make total sense. This query is more a FIND ALL than FIND ANY.

[Bonez024]

This suggestion fails to mention why this would be faster over the built-in exists / not exists statements within SQL Server. Without any benchmarking i'd be hard-pressed to believe that a case statement would yeild a faster result than an immediate true/false response.

[Gert Arnold]

Why not? Maybe because it's incorrect syntax?