3
Hello, first I will pass some pertinent data to the query.
Tabela Contrato (id, id_cliente, status [ativo/inativo])
Tabela Cliente (id, nome)
Tabela Dependente (id, id_cliente, nome, status [ativo/inativo])
Tabela Cartao (id, id_cliente, id_dependente, status [ativo/inativo])
Since both customers and dependents can own card and there may also be customers who have no dependents associated, I need to make a select that returns the id of contracts that will have new impressions (Inserts) of cards, these Inserts will be all dependents/customers who do not have a card with status 'A', so currently I have:
SELECT CC.ID FROM CONTRATO CC
INNER JOIN CLIENTE C ON C.ID = CC.ID_CLIENTE
LEFT JOIN DEPENDENTE D ON D.ID_CLIENTE = C.ID
LEFT JOIN CARTAO C_CLIENTE ON C_CLIENTE.ID_CLIENTE = C.ID
LEFT JOIN CARTAO C_DEPENDENTE ON C_DEPENDENTE.ID_DEPENDENTE = D.ID
WHERE C.STATUS = 'A' --impressão apenas para ativos
AND (
--inicio tratamento de cliente
(C_CLIENTE.ID_DEPENDNETE IS NULL AND
(C_CLIENTE.ID_CLIENTE IS NULL OR C.ID NOT IN(
SELECT ID_CLIENTE FROM CARTAO
WHERE ID_CLIENTE = C.ID
AND STATUS = 'A'
AND ID_DEPENDENTE IS NULL
)))
--fim tratamento de cliente
OR
--inicio tratamento de dependente
(D.ATIVO = 'A' AND (
C_DEPENDENTE.ID_DEPENDENTE IS NULL OR D.ID NOT IN (
SELECT ID_DEPEDENTE FROM CARTAO
WHERE ID_DEPENDENTE = D.ID
AND STATUS = 'A'
)))
--fim tratamento de dependente
)
GROUP BY CC.ID;
The parts of NOT IN, is a check because the dependent/client may have one or more inactive cards, and I believe that is where the loss of performance in the query is occurring.
It may help to post the complete table structure, and to change the query structure some data may be indexed as well.
– Guilherme Nascimento
Paste the EXPLAIN of this query into the question.
– Rodrigo Rigotti