How to improve the performance of this SQL query?

Question

How to improve the performance of this SQL query?

Navigation

#1 by (4 votes)

3

Hello, I'll first 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 clients and dependents can own a card and there may also be clients that do not have associated dependents, I need to make a select that returns the id of the contracts that will have new card inserts, these inserts will be all dependent / clients that do not have a 'A' status card, so I currently 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 there is a loss of performance in the query.

sql join

asked by anonymous 09.03.2015 / 06:00

1 answer

You can block the Arrow in the DATE field Get the key pressed with minimized or unfocused application C #

score 4 · Accepted Answer

@chavesfop, try moving 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;