Início / dba / Perguntas / 118655
Accepted
Juan Velez
Asked: 2015-10-21 11:12:28 +0800 CST

Por que usar um CTE é muito mais rápido do que usar uma tabela #Temp nesta consulta?

  • 772

Eu tenho 2 consultas diferentes para identificar dups (abaixo). A única diferença entre as 2 consultas é que uma usa um CTE e a outra usa uma tabela #Temp.

Alguém sabe porque o CTE é tão mais rápido que a tabela #Temp (0:20 segundos VS. 1:22)?

Prefiro usar o CTE mas preciso rodar 2 STATEMENTS usando o CTE (DELETE from CTE e depois INSERT INTO table FROM CTE) mas o SQL Server só permite escrever um STATEMENT no CTE.

Consulta 1:

WITH DUPS AS(
   Id,
   Column1,
   Column2,
   Column3,
   RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY id)
   FROM mytable
)
  Select top 1 * FROM DUPS WHERE RN > 1

Consulta 2:

 SELECT    
           Id,
           Column1,
           Column2,
           Column3,
           RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY id)
       INTO #DUPS
       FROM mytable

       Select top 1 * FROM #DUPS WHERE RN > 1
sql-server

2 respostas

  1. Best Answer

    Acredito que se você olhar os planos de execução verá que o CTE te dá um plano melhor porque consegue tirar melhor proveito do TOP. A tabela Temp terá que criar a tabela no TempDB, manter todos os dados no disco e, em seguida, selecionar o registro superior. Se você remover o TOPde ambas as consultas, aposto que obterá um desempenho muito mais próximo. Dito isto, eu geralmente esperaria que o CTE superasse a tabela temporária em um cenário de uso único como você tem aqui, pois o CTE está fazendo menos trabalho do que criar/inserir em uma tabela temporária.

    Provavelmente, este é apenas um código de amostra, mas vale a pena mencionar que seus resultados não são determinados porque você está usando TOPsem uma ORDER BYcláusula.

    Para ler mais, você deve verificar esta questão . Esta resposta a essa pergunta parece se aplicar à sua situação em particular.

    Para a segunda metade implícita da sua pergunta:

    Como você diz que precisa usar os dados do CTE pelo menos duas vezes, você tem quatro opções:

    1. Copie e cole seu CTE nos dois lugares
    2. Use o CTE para inserir dados em uma variável de tabela e use os dados na variável de tabela para executar as próximas duas operações.
    3. Use o CTE para inserir dados em uma tabela temporária e use os dados na tabela temporária para executar as próximas duas operações.
    4. Escreva um CTE melhor adaptado. Presumo que você esteja fazendo coisas diferentes, portanto as consultas devem ser um pouco diferentes.

    Costumo preferir a abordagem da opção 2 (variável de tabela) ou da opção 4 (CTE sob medida). Não gosto da duplicação e manutenção extra de CTEs copiados/colados. Também gosto do escopo explicitamente reduzido da variável de tabela em uma tabela temporária. Para um tratamento confiável sobre as diferenças entre variáveis ​​de tabela e tabelas temporárias, confira esta resposta . Quanto à escolha entre as opções 2 e 4; isso dependerá do que seu perfil ditar e/ou das restrições com as quais você terá que trabalhar.

    • 7

  2. Prefiro usar o CTE mas preciso rodar 2 STATEMENTS usando o CTE (DELETE from CTE e depois INSERT INTO table FROM CTE) mas o SQL Server só permite escrever um STATEMENT no CTE.

    Você pode excluir e inserir em uma única instrução, usando a cláusula OUTPUT:

    with cte as (
 select ...
 )
delete from cte
output deleted.id into #deleted_ids;
    • 3

relate perguntas