Hitchhiking in the topic ...

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Thu, 24 Jan 2008 1:45 pm

Taking advantage of the topic, a curiosity. What is faster: count or exists?

Crude example:
Code: Select all
SELECT PESSOA.CODIGO, PESSOA.NOME
  FROM PESSOA
WHERE EXISTS (SELECT *
                 FROM VENDAS
                WHERE VENDAS.COD_PESSOA = PESSOA.COD)
or
Code: Select all
SELECT PESSOA.CODIGO, PESSOA.NOME
  FROM PESSOA
WHERE (SELECT COUNT(*)
          FROM VENDAS
         WHERE VENDAS.COD_PESSOA = PESSOA.COD) > 0
Marciel
Location: Vitória - ES

Sabe qual o cúmulo da ignorância, da apatia e da ira?
Não sei, não quero saber e tenho raiva de quem sabe.

Poston Thu, 24 Jan 2008 1:46 pm

Was to answer the topic concerning exists ... sorry for the pointless topic.
Marciel
Location: Vitória - ES

Sabe qual o cúmulo da ignorância, da apatia e da ira?
Não sei, não quero saber e tenho raiva de quem sabe.

Poston Thu, 24 Jan 2008 7:27 pm

If the purpose is only to determine whether a record in the table, certainly exists has better performance.

Internally, the query exists clause will be held, but the data will not be retrieved, the command will return true as soon as the first line which satisfies the condition is found, or false if there is no such record.

In the case of the count, in addition to performing the query, the database will retrieve all records that satisfy this condition (the difference in performance is very clear when this query returns many records, especially if there is an index that can be used with the conditions defined in the query) count the number of records, which will then be returned to the top-level query.
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Fri, 25 Jan 2008 8:17 am

And if NOT EXISTS and COUNT = 0? To assess if does not exist, the table must be checked until the end. In this case, the performance would be the same?
Marciel
Location: Vitória - ES

Sabe qual o cúmulo da ignorância, da apatia e da ira?
Não sei, não quero saber e tenho raiva de quem sabe.

Poston Sun, 27 Jan 2008 6:48 pm

Marciel, the situation is almost the same.
If there is even a record, both commands should behave similarly.

However, if the subquery will return multiple records, the not exists and count = 0 will behave the same way (the not exists will return false in the first record found, whereas the count will count all records returned, and then compare the result with the value 0).
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Fri, 01 Feb 2008 6:56 am

And in this case, the best performance?

Code: Select all
SELECT PESSOA.CODIGO, PESSOA.NOME
  FROM PESSOA
WHERE EXISTS (SELECT *
                 FROM VENDAS
                WHERE VENDAS.COD_PESSOA = PESSOA.COD)
or
Code: Select all
SELECT PESSOA.CODIGO, PESSOA.NOME
  FROM PESSOA
WHERE PESSOA.COD IN (SELECT VENDAS.COD_PESSOA
                        FROM VENDAS)
Marciel
Location: Vitória - ES

Sabe qual o cúmulo da ignorância, da apatia e da ira?
Não sei, não quero saber e tenho raiva de quem sabe.



Return to SQL

Who is online

Users browsing this forum: No registered users and 4 guests