How to improve the performance of this SQL query?

Question

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.

Answer 1

@chavesfop, try to move the subquery that is in your Where to the joins.

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
LEFT JOIN (
    SELECT DISTINCT ID_CLIENTE
    FROM CARTAO 
    WHERE STATUS = 'A' AND ID_DEPENDENTE IS NULL
) as C_CLIENTE_AUX ON C.ID = C_CLIENTE_AUX.ID_CLIENTE
LEFT JOIN (
    SELECT DISTINCT ID_DEPEDENTE 
    FROM CARTAO 
    WHERE STATUS = 'A'
) as C_DEPENDENTE_AUX ON D.ID = C_DEPENDENTE_AUX.ID_CLIENTE
WHERE C.STATUS = 'A' AND
    (
        (C_CLIENTE.ID_DEPENDNETE IS NULL AND 
            (C_CLIENTE.ID_CLIENTE IS NULL OR C_CLIENTE_AUX.ID_CLIENTE IS NULL)
        )
        OR
        (D.ATIVO = 'A' AND 
            (C_DEPENDENTE.ID_DEPENDENTE IS NULL OR C_DEPENDENTE_AUX.ID_DEPEDENTE IS NULL)
        )
    )
GROUP BY CC.ID;