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?
What is the best way to check the existence of a record in the database via the application?
3
asked by anonymous 27.02.2015 / 14:38
3 answers
7
As far as I know this form is good and there is nothing that brings significant improvement. Just this:
SELECT COUNT(id) FROM tabela WHERE coluna = valor
27.02.2015 / 14:45
6
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 Information:
-
Using 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 it made little difference, following the logic in both cases when it returns 1 or more records the first option became more performative.
27.02.2015 / 14:42
3
As Andrew mentioned, I'd rather use it as well:
select 1 from tabela where coluna=valor
Out of curiosity, in SQL Server you may need something like this if you need to create a SQL script that needs to make some decision about the existence of a record:
if exists (select 1 from tabela where coluna=valor)
begin
//SQL
end
28.02.2015 / 17:16