What is the best way to verify the existence of a record in the database via application?
Today I use a method that sends a COUNT with the record Id as a parameter to the database. Is there a better, perhaps more performative way to do this?
3 answers
As far as I know this form is good and there is nothing that brings significant improvement. This is enough:
SELECT COUNT(id) FROM tabela WHERE coluna = valor
I put on GitHub for future reference .
But this only works if you just want to verify the very existence. If later will give a INSERT, UPDATE or do some other thing that depends on the existence or not of the record, then you are doing something wrong because you may incur run condition .
Hello, it would be nice if you put the code used in the application.
With database access I use the following way.
Select 1 from tabela where coluna=valor
Simple and functional.
Additional Data:
-
Using In Real Data
Table with 1,541,770 records.
-
DBMS: PostgreSQL
Being searched through a field that makes up the primary key
SELECT count(*) FROM parcela_prev WHERE cor_cod = 'x'
Total query runtime: 916 Ms.
1 row retrieved.
SELECT 1 FROM parcela_prev WHERE cor_cod = 'x'
Total query runtime: 949 ms.
26299 rows retrieved.
In the real environment gave little difference, following the logic in both cases when returning 1 or more records the first option became more performative.
As @ Andrew mentioned, I prefer to use also:
select 1 from tabela where coluna=valor
Out of curiosity, in SQL Server you might need something like this if you need to create an SQL script that needs to make some decision upon the existence of a record:
if exists (select 1 from tabela where coluna=valor)
begin
//SQL
end