Início / dba / Perguntas / 13112
Accepted
Rachel
Asked: 2012-02-16 08:47:27 +0800 CST

Qual é a diferença entre uma CTE e uma Tabela Temp?

  • 772

Qual é a diferença entre uma expressão de tabela comum (CTE) e uma tabela temporária? E quando devo usar um sobre o outro?

CTE

WITH cte (Column1, Column2, Column3)
AS
(
    SELECT Column1, Column2, Column3
    FROM SomeTable
)

SELECT * FROM cte

Tabela Temp

SELECT Column1, Column2, Column3
INTO #tmpTable
FROM SomeTable

SELECT * FROM #tmpTable
sql-server cte

7 respostas

  1. Best Answer

    Isso é bastante amplo, mas vou lhe dar uma resposta tão geral quanto possível.

    CTEs...

    • Não são indexáveis ​​(mas podem usar índices existentes em objetos referenciados)
    • Não pode ter restrições
    • VIEWSão essencialmente descartáveis
    • Persistir apenas até que a próxima consulta seja executada
    • Pode ser recursivo
    • Não tem estatísticas dedicadas (confie nas estatísticas dos objetos subjacentes)

    #TabelasTemporárias...

    • São tabelas materializadas reais que existem no tempdb
    • Pode ser indexado
    • Pode ter restrições
    • Persista durante a vida da CONEXÃO atual
    • Pode ser referenciado por outras consultas ou subprocedimentos
    • Tenha estatísticas dedicadas geradas pelo mecanismo

    Quanto a quando usar cada um, eles têm casos de uso muito diferentes. Se você tiver um conjunto de resultados muito grande ou precisar consultá-lo mais de uma vez, coloque-o em uma #temptabela. Se precisa ser recursiva, é descartável ou é apenas para simplificar algo logicamente, a CTEé preferível.

    Além disso, a nuncaCTE deve ser usado para desempenho . Você quase nunca vai acelerar as coisas usando um CTE, porque, novamente, é apenas uma visão descartável. Você pode fazer algumas coisas legais com eles, mas acelerar uma consulta não é realmente um deles.

    • 215

  2. EDITAR:

    Por favor, veja os comentários de Martin abaixo:

    O CTE não é materializado como uma tabela na memória. É apenas uma maneira de encapsular uma definição de consulta. No caso do OP, ele será embutido e o mesmo que apenas fazer SELECT Column1, Column2, Column3 FROM SomeTable. Na maioria das vezes eles não se materializam logo de cara, e é por isso que isso não retorna nenhuma linha WITH T(X) AS (SELECT NEWID())SELECT * FROM T T1 JOIN T T2 ON T1.X=T2.X, verifique também os planos de execução. Embora às vezes seja possível hackear o plano para obter um carretel. Há um item de conexão solicitando uma dica para isso. – Martin Smith 15 de fevereiro '12 às 17:08

    Resposta original

    CTE

    Leia mais no MSDN

    Um CTE cria a tabela que está sendo usada na memória, mas é válida apenas para a consulta específica que a segue. Ao usar recursão, isso pode ser uma estrutura eficaz.

    Você também pode querer considerar o uso de uma variável de tabela. Isso é usado como uma tabela temporária é usada e pode ser usada várias vezes sem precisar ser rematerializada para cada junção. Além disso, se você precisar persistir alguns registros agora, adicionar mais alguns registros após a próxima seleção, adicionar mais alguns registros após outra operação e retornar apenas esses poucos registros, isso pode ser uma estrutura útil, pois não ' não precisa ser descartado após a execução. Principalmente apenas açúcar sintático. No entanto, se você mantiver a contagem de linhas baixa, ela nunca se materializará no disco. Consulte Qual é a diferença entre uma tabela temporária e uma variável de tabela no SQL Server? para mais detalhes.

    Tabela Temp

    Leia mais no MSDN - Role para baixo cerca de 40% do caminho

    Uma tabela temporária é literalmente uma tabela criada em disco, apenas em um banco de dados específico que todos sabem que pode ser deletado. É responsabilidade de um bom desenvolvedor destruir essas tabelas quando elas não forem mais necessárias, mas um DBA também pode limpá-las.

    As tabelas temporárias vêm em duas variedades: local e global. Em termos de MS Sql Server, você usa uma #tableNamedesignação para local e ##tableNamedesignação para global (observe o uso de um único ou duplo # como característica de identificação).

    Observe que com tabelas temporárias, ao contrário de variáveis ​​de tabela ou CTE, você pode aplicar índices e similares, pois são tabelas legítimas no sentido normal da palavra.

    Geralmente, eu usaria tabelas temporárias para consultas mais longas ou maiores e CTEs ou variáveis ​​de tabela se já tivesse um pequeno conjunto de dados e quisesse apenas criar rapidamente um script de código para algo pequeno. A experiência e os conselhos de outras pessoas indicam que você deve usar CTEs onde você tem um pequeno número de linhas sendo retornadas. Se você tiver um número grande, provavelmente se beneficiará da capacidade de indexar na tabela temporária.

    • 30

  3. A resposta aceita aqui diz que "um CTE nunca deve ser usado para desempenho" - mas isso pode enganar. No contexto de CTEs versus tabelas temporárias, acabei de remover uma porção de lixo de um conjunto de procs armazenados porque alguns idiotas devem ter pensado que havia pouca ou nenhuma sobrecarga no uso de tabelas temporárias. Enfiei tudo nos CTEs, exceto aqueles que legitimamente seriam reutilizados ao longo do processo. Ganhei cerca de 20% de desempenho em todas as métricas. Em seguida, comecei a remover todos os cursores que estavam tentando implementar o processamento recursivo. Foi aí que eu vi o maior ganho. Acabei reduzindo os tempos de resposta por um fator de dez.

    CTEs e tabelas temporárias têm casos de uso muito diferentes. Eu só quero enfatizar que, embora não seja uma panacéia, a compreensão e o uso correto de CTEs podem levar a algumas melhorias realmente estelares na qualidade/manutenção e velocidade do código. Desde que eu tenho um controle sobre eles, vejo tabelas temporárias e cursores como os grandes males do processamento SQL. Eu posso me dar bem com variáveis ​​de tabela e CTEs para quase tudo agora. Meu código é mais limpo e mais rápido.

    • 19

  4. Um CTE pode ser chamado repetidamente dentro de uma consulta e é avaliado toda vez que é referenciado - esse processo pode ser recursivo. Se for referenciado apenas uma vez, ele se comportará como uma subconsulta, embora os CTEs possam ser parametrizados.

    Uma tabela temporária é mantida fisicamente e pode ser indexada. Na prática, o otimizador de consulta também pode persistir resultados intermediários de junção ou subconsulta nos bastidores, como em operações de spool, portanto, não é estritamente verdade que os resultados de CTEs nunca são persistidos em disco.

    As variáveis ​​da tabela IIRC (por outro lado) são sempre estruturas na memória.

    • 16

  5. A tabela temporária é um objeto real em tempdb, mas cte é apenas um tipo de wrapper em torno de consulta complexa para simplificar a sintaxe de organizar a recursão em uma etapa.

    • 12

  6. A principal razão para usar CTEs é acessar funções de janela como row_number()e várias outras.

    Isso significa que você pode fazer coisas como obter a primeira ou a última linha por grupo MUITO, MUITO, MUITO rápida e eficientemente – mais eficientemente do que outros meios na maioria dos casos práticos .

    with reallyfastcte as (
select *, 
row_number() over (partition by groupingcolumn order by sortingcolumn) as rownum
from sometable
)
select *
from reallyfastcte
where rownum = 1;

    Você pode executar uma consulta semelhante à acima usando uma subconsulta correlacionada ou usando uma subconsulta, mas o CTE será mais rápido em quase todos os cenários.

    Além disso, os CTEs podem realmente ajudar a simplificar seu código. Isso pode levar a ganhos de desempenho porque você entende mais a consulta e pode introduzir mais lógica de negócios para ajudar o otimizador a ser mais seletivo.

    Além disso, os CTEs podem aumentar o desempenho se você entender sua lógica de negócios e souber quais partes da consulta devem ser executadas primeiro - normalmente, coloque suas consultas mais seletivas primeiro que levam a conjuntos de resultados que podem usar um índice em sua próxima junção e adicionar a option(force order)consulta dica

    Por fim, os CTEs não usam tempdb por padrão, portanto, você reduz a contenção nesse gargalo por meio de seu uso.

    As tabelas temporárias devem ser usadas se você precisar consultar os dados várias vezes ou, alternativamente, se você medir suas consultas e descobrir que, inserindo em uma tabela temporária e adicionando um índice, seu desempenho é aprimorado.

    • 10

  7. Parece haver um pouco de negatividade aqui em relação aos CTEs.

    Meu entendimento de um CTE é que é basicamente um tipo de visão ad hoc. SQL é uma linguagem declarativa e baseada em conjunto. CTEs são uma ótima maneira de declarar um set! Não ser capaz de indexar um CTE é realmente uma coisa boa porque você não precisa! É realmente um tipo de açúcar sintático para tornar a consulta mais fácil de ler/escrever. Qualquer otimizador decente elaborará o melhor plano de acesso usando índices nas tabelas subjacentes. Isso significa que você pode acelerar efetivamente sua consulta CTE seguindo o conselho de índice nas tabelas subjacentes.

    Além disso, só porque você definiu um conjunto como CTE, isso não significa que todas as linhas do conjunto devem ser processadas. Dependendo da consulta, o otimizador pode processar linhas "apenas o suficiente" para satisfazer a consulta. Talvez você só precise dos primeiros 20 ou mais para sua tela. Se você construiu uma tabela temporária, você realmente precisa ler/gravar todas essas linhas!

    Com base nisso, eu diria que os CTE's são um ótimo recurso do SQL e podem ser usados ​​em qualquer lugar que facilitem a leitura da consulta. Eu só pensaria em uma tabela temporária para um processo em lote que realmente precisaria processar todos os registros. Mesmo assim, afaik não é realmente recomendado porque em uma tabela temporária é muito mais difícil para o banco de dados ajudá-lo com cache e índices. Pode ser melhor ter uma tabela permanente com um campo PK exclusivo para sua transação.

    Eu tenho que admitir que minha experiência é principalmente com DB2, então estou assumindo que os CTEs funcionam de maneira semelhante em ambos os produtos. Eu ficarei feliz em ser corrigido se os CTEs forem de alguma forma inferiores no SQL Server. ;)

    • 7

relate perguntas