Início / dba / Perguntas / 48830
Accepted
jcalfee314
Asked: 2013-08-28 08:59:04 +0800 CST

Melhore o desempenho com a cláusula de subseleção WHERE NOT IN

  • 772

Na consulta a seguir, preciso contar as transações de cada cliente. No entanto, tenho que excluir totalmente do conjunto de resultados os clientes que possuem uma transação com mais de um ano.

O otimizador de consulta não deveria ser inteligente o suficiente para avaliar a existência apenas uma vez para cada cliente?

--Count transactions on customers that are less than 1 year old

SELECT t1.CUSTID, COUNT(*)
FROM CUST_TRX t1
WHERE NOT EXISTS ( 
  SELECT FIRST 1 1 
  FROM CUST_TRX t2 
  WHERE 
    t2.CUSTID = t1.CUSTID AND
    t2.DATED < CURRENT_DATE - 365
  GROUP BY t2.CUSTID
)
GROUP BY t1.CUSTID

Não há recursos naturais em meu plano de consulta. Essa consulta está sendo executada como se o banco de dados estivesse executando a cláusula de existência para cada transação, em vez de executá-la para cada cliente. O desempenho é o mesmo se eu remover o GROUP BYna subconsulta.

Existe uma maneira melhor de fazer isso para que eu possa obter um melhor desempenho do banco de dados? Esperançosamente, uma consulta simples SELECTfuncionará evitando um CTE, se possível (isso apresentaria outros desafios).

Devido a outros GROUP BYcritérios (não mostrados aqui) não estou conseguindo simplesmente verificar MIN(DATED), preciso muito realizar outra consulta.

performance subquery

2 respostas

  1. Best Answer

    Com consultas como esta, muitas vezes é mais eficiente executar uma verificação LEFT OUTER JOINem vez da NOT EXISTSverificação de estilo, geralmente implica uma varredura de índice completa (ou varredura de tabela sem os índices corretos no lugar), mas com muitas linhas na(s) tabela(s) principal(is) isso é menos caro do que o grande número de buscas de índice (uma na tabela de referência para cada linha retornada da tabela principal) que, de outra forma, resultaria em . Alguns planejadores de consulta são bastante inteligentes em identificar essa equivalência e usar o plano alternativo onde é a melhor escolha, mas não parece que isso tenha acontecido no seu caso.

    Tente algo como:

    SELECT t1.CUSTID, COUNT(*)
FROM   CUST_TRX t1
LEFT OUTER JOIN
       CUST_TRX t2 
ON     t2.CUSTID=t1.CUSTID 
AND    t2.DATED<CURRENT_DATE-365
WHERE  t2.CUSTID IS NULL
GROUP BY t1.CUSTID

    (nota: não estou familiarizado com o firebird, então a sintaxe acima pode precisar de ajustes, mas deve ilustrar o ponto)

    Sem WHERE t2.CUSTID IS NULLtodas as linhas de t1com correspondências em t2será exibida uma vez para cada correspondência encontrada em t2e aquelas sem correspondências t2serão exibidas uma vez, mas com quaisquer colunas selecionadas desse objeto definido como NULL. A WHEREcláusula então filtra as correspondências.

    Dependendo das habilidades do mecanismo de banco de dados, especialmente se a quantidade de dados no objeto de referência ( CUST_TRXcom um filtro aplicado aqui) for grande, isso pode ser significativamente menos eficiente do que as opções WHERE <something> NOT INou WHERE NOT EXISTS, portanto, compare primeiro conjuntos de dados realistas antes de usar o método. Muitas vezes, funciona muito mais eficientemente com o MS SQL Server nos casos em que o planejador de consultas não percebe que o WHERE NOT INarranjo pode ser executado dessa maneira com mais eficiência.

    Além disso, se você fizer isso dessa maneira, deixe um comentário no código (e/ou na documentação de suporte) para dizer que está fazendo isso como um equivalente WHERE <something> NOT INou WHERE NOT EXISTSque espera ser mais eficiente. Você se lembrará disso e uma pessoa experiente em SQL reconhecerá o padrão, mas outras pessoas que olham para o código podem não entender imediatamente a intenção/razão e voltar a usá-lo WHERE NOT EXISTSpara maior clareza, pois é melhor lido do que na frase em inglês.

    • 5

  2. Quando você diz "contar transações em clientes com menos de 1 ano", você quer dizer:

    1. Contar todas as transações de clientes com menos de um ano?
    2. Contar todas as transações de novos clientes com menos de um ano?

    Pelo código de exemplo, entendo que o número 1 é o que você deseja. Nesse caso, você realmente precisa de um WHERE NOT EXISTS? Você poderia apenas fazer algo como:

    SELECT t1.CUSTID, COUNT(*)
FROM CUST_TRX t1
WHERE t1.DATED>=CURRENT_DATE-365
GROUP BY t1.CUSTID
HAVING COUNT(*) > 0

    Não sou usuário do Firebird, mas pesquisei a sintaxe GROUP BY / HAVING.

    [EDIT]Excluir do conjunto de resultados os clientes que possuem uma transação com mais de um ano.

    OK, aqui está outra abordagem para agregar as linhas para eliminar o cliente da seleção.

    SELECT A.CUSTID, A.HowMany
FROM (SELECT t1.CUSTID, COUNT(*) HowMany, MIN(t1.DATED) OldestTran
    FROM CUST_TRX t1
    GROUP BY t1.CUSTID
    HAVING COUNT(*) > 0 AND MIN(t1.DATED) >=CURRENT_DATE-365) AS A

    [EDIT] OK, então a consulta é mais complexa que pode ser incorporada em uma única consulta.

    Isso significa que você provavelmente precisará usar um padrão muito parecido com o que você postou primeiro. Observe que EXISTS implica um DISTINCT e geralmente é mais rápido que um JOIN de um SELECT DISTINCT. Mas você pode tentar abordagens diferentes e comparar o comportamento, tempo, etc. Em seguida, escolha a que você mais gosta.

    • 0

relate perguntas