Performance with Exists

Question

Performance with Exists

Navigation

#1 by (2 votes)

2

One colleague mentioned that when doing EXISTS in SQL, it is recommended to use DISTINCT 1 to improve performance. For example, instead of doing:

SELECT *  
  FROM CLIENTES  
 WHERE NOT EXISTS (SELECT CODCLI 
                     FROM VENDAS 
                    WHERE VENDAS.CODCLI = CLIENTES.CODCLI)

He suggested doing:

SELECT *  
  FROM CLIENTES  
 WHERE NOT EXISTS (SELECT DISTINCT 1
                     FROM VENDAS 
                    WHERE VENDAS.CODCLI = CLIENTES.CODCLI)

Is there a difference (performance) in the execution of the above SQL?
What would be recommended?

Banks: Oracle and FireBird (ATM).

sql oracle firebird

asked by anonymous 03.04.2018 / 15:23

1 answer

Generating functions: What are the advantages of using them? How to disable the suggestion of passwords saved in the browser via HTML [duplicated]

score 2 · Answer 1

In some tables, when using Joins, some values may be duplicated in some relationships, so it has to search multiple lines within that same face, but if you use the distinct one, it will only search that first time, no matter if that same guy could have another version in some field, then in tables with large numbers of records, it ends up being faster.

Let's suppose, you want to make a select in the sales table, this sale belongs to a customer that has two different contract types, but the customer contract is not directly linked to the sale, only to the customer, if you do a joins until the contracts, it will duplicate the same sale lines, one for each contract, and with the distinct it will look only at the first contract and will "skip" the second to not duplicate the same sale, now imagine making one select on all sales that goes into all customers and goes through all customer contracts ... would be much faster.