Escolha entre variáveis de tabela e tabelas temporárias (ST011, ST012)

As pessoas podem, e fazem, discutir muito sobre os méritos relativos das variáveis de tabela e tabelas temporárias. Às vezes, como ao escrever funções, você não tem escolha; mas, ao fazer isso, você descobrirá que ambos têm seus usos e é fácil encontrar exemplos em que qualquer um deles é mais rápido. Neste artigo, explicarei os principais fatores envolvidos na escolha de um ou outro e demonstrarei algumas regras simples para obter o melhor desempenho.

Supondo que você siga as regras básicas de engajamento , então você deve considerar as variáveis de tabela como primeira escolha ao trabalhar com conjuntos de dados relativamente pequenos. Eles são mais fáceis de trabalhar e desencadeiam menos recompilações nas rotinas em que são usados, em comparação com o uso de tabelas temporárias. Variáveis de tabela também requerem menos recursos de bloqueio, pois são “privadas” para o processo e lote que as criou. O SQL Prompt implementa esta recomendação como uma regra de análise de código, ST011 – Considere o uso de variável de tabela em vez de tabela temporária.

Se você estiver fazendo um processamento mais complexo em dados temporários, ou precisar usar mais do que quantidades razoavelmente pequenas de dados neles, então as tabelas temporárias locais provavelmente serão uma escolha melhor. O SQL Code Guard inclui uma regra de análise de código, com base em sua recomendação, ST012 – Considere o uso de tabela temporária em vez de variável de tabela, mas ela não está implementada no Prompt SQL.

Prós e contras de variáveis de tabela e tabelas temporárias

Variáveis de tabela tendem a ter má impressão, porque consultas que as usam ocasionalmente resultam em planos de execução muito ineficientes. No entanto, se você seguir algumas regras simples, elas são uma boa escolha para tabelas intermediárias de trabalho e para passar resultados entre rotinas, onde os conjuntos de dados são pequenos e o processamento necessário é relativamente simples.

As variáveis de tabela são muito simples de usar, principalmente porque são “zero manutenção”. Elas têm como escopo o lote ou a rotina em que são criadas e são removidas automaticamente quando a execução é concluída e, portanto, são utilizadas em uma conexão de longa duração não corre o risco de problemas de esgotamento de recursos em tempdb. Se uma variável de tabela for declarada em um procedimento armazenado, ela é local para esse procedimento armazenado e não pode ser referenciada em um procedimento aninhado. Também não há recompilações baseadas em estatísticas para variáveis de tabela e você não pode ALTER um, então as rotinas que os usam tendem a incorrer em menos recompilações do que aquelas que usam tabelas temporárias. Eles também não são totalmente registrados, portanto, criá-los e preenchê-los é mais rápido e requer menos espaço no t registro de transação. Quando são usados em procedimentos armazenados, há menos contenção nas tabelas do sistema, sob condições de alta simultaneidade. Resumindo, é mais fácil manter as coisas organizadas e organizadas.

Ao trabalhar com conjuntos de dados relativamente pequenos, eles são mais rápidos do que a mesa temporária comparável. No entanto, conforme o número de linhas aumenta, além de aproximadamente 15 mil linhas, mas variando de acordo com o contexto, você pode encontrar dificuldades, principalmente devido à falta de suporte para estatísticas. Mesmo os índices que impõem restrições PRIMARY KEY e UNIQUE às variáveis da tabela não têm estatísticas . Portanto, o otimizador usará uma estimativa embutida em código de 1 linha retornada de uma variável de tabela e, portanto, tenderá a escolher os operadores ideais para trabalhar com pequenos conjuntos de dados (como o operador de Loops aninhados para junções). Quanto mais linhas na variável da tabela, maiores serão as discrepâncias entre a estimativa e a realidade, e mais ineficientes se tornam as escolhas do plano do otimizador. O plano resultante às vezes é assustador.

O desenvolvedor experiente ou DBA estará à procura desse tipo de problema e estará pronto para adicionar o OPTION (RECOMPILE) dica de consulta para a instrução que usa a variável de tabela. Quando enviamos um lote contendo uma variável de tabela, o otimizador primeiro compila o lote em que a variável de tabela está vazia. Quando o lote começa a ser executado, a dica fará com que apenas aquela única instrução seja recompilada, momento em que a variável da tabela será preenchida e o otimizador pode usar a contagem de linha real para compilar um novo plano para essa instrução. Às vezes, mas raramente, mesmo isso não vai ajudar. Além disso, o excesso de confiança nesta dica negará até certo ponto a vantagem que as variáveis de tabela têm de causar menos recompilações do que tabelas temporárias.

Em segundo lugar, certas limitações de índice com variáveis de tabela tornam-se um fator mais importante ao lidar com grandes conjuntos de dados. Embora agora você possa usar a sintaxe de criação de índice sequencial para criar índices não agrupados em uma variável de tabela, existem algumas restrições e ainda não há estatísticas associadas.

Mesmo com contagens de linhas relativamente modestas, você pode encontrar problemas de desempenho de consulta se tentar executar uma consulta que seja uma junção e se esquecer de definir um PRIMARY KEY ou UNIQUE restrição na coluna que você está usando para a junção. Sem os metadados que eles fornecem, o otimizador não tem conhecimento da ordem lógica dos dados ou se os dados na coluna de junção contêm valores duplicados e provavelmente escolherá operações de junção ineficientes, resultando em consultas lentas. Se você estiver trabalhando com um heap de variável de tabela, só poderá usá-lo em uma lista simples que provavelmente será processada em um único gole (verificação de tabela). Se você combinar o uso da sugestão OPTION (RECOMPILE), para estimativas precisas de cardinalidade, e uma chave na coluna de junção para dar ao otimizador útil metadados, então, para conjuntos de dados menores, você geralmente pode atingir velocidades de consulta semelhantes ou melhores do que usar uma tabela temporária local.

Uma vez que as contagens de linha aumentam além da zona de conforto de uma variável de tabela, ou você precisa fazer dados mais complexos processamento, então é melhor mudar para usar tabelas temporárias. Aqui, você tem todas as opções disponíveis para indexação, e o otimizador terá o luxo de usar estatísticas para cada um desses índices. Obviamente, a desvantagem é que as tabelas temporárias têm um custo de manutenção mais alto. Você precisa ter certeza de limpar depois de você mesmo, para evitar o congestionamento do tempdb. Se você alterar uma tabela temporária ou modificar seus dados, poderá incorrer em recompilações da rotina pai.

As tabelas temporárias são melhores quando há um requisito para um grande número de exclusões e inserções (compartilhamento de conjunto de linhas ) Isso é especialmente verdadeiro se os dados devem ser totalmente removidos da tabela, já que apenas tabelas temporárias suportam truncamento. Os compromissos no design de variáveis de tabela, como a falta de estatísticas e recompilações, funcionam contra eles se os dados forem voláteis.

Quando vale a pena usar variáveis de tabela

Nós Vou começar com um exemplo em que uma variável de tabela é ideal e resulta em melhor desempenho. Vamos produzir uma lista de funcionários da Adventureworks, em qual departamento eles trabalham e os turnos em que trabalham. Estamos lidando com um pequeno conjunto de dados (291 linhas).

Colocaremos os resultados em uma segunda tabela temporária, como se estivéssemos passando o resultado para o próximo lote. A Listagem 1 mostra o código.

E aqui está um resultado típico em minha máquina de teste lenta:

Usar uma tabela temporária é consistentemente mais lento, embora as execuções individuais possam variar muito.

Os problemas de escala e esquecimento de fornecer uma chave ou uma dica

Qual é o desempenho se juntarmos duas variáveis de tabela? Vamos experimentar. Para este exemplo, precisamos de duas tabelas simples, uma com todas as palavras comuns do idioma inglês (CommonWords) e a outra com uma lista de todas as palavras do Drácula de Bram Stoker (WordsInDracula). O download do TestTVsAndTTs inclui o script para criar essas duas tabelas e preencher cada uma a partir de seu arquivo de texto associado. Existem 60.000 palavras comuns, mas Bram Stoker usou apenas 10.000 delas. O primeiro está bem fora do ponto de equilíbrio, onde se começa a preferir tabelas temporárias.

Usaremos quatro consultas de junção externa simples, testando o resultado para NULL values, para descobrir as palavras comuns que não estão no Drácula, palavras comuns que estão no Drácula, palavras no Drácula que são incomuns e, finalmente, outra consulta para encontrar palavras comuns no Drácula, mas se juntando na direção oposta. Você verá as consultas em breve, quando eu mostrar o código da plataforma de teste.

A seguir estão os resultados dos testes iniciais. Na primeira execução, ambas as variáveis de tabela têm chaves primárias e, na segunda, ambas são pilhas, apenas para ver se estou exagerando os problemas de não declarar um índice em uma variável de tabela. Finalmente, executamos as mesmas consultas com tabelas temporárias. Todos os testes foram executados, deliberadamente, em um servidor de desenvolvimento lento, para fins de ilustração; você obterá resultados muito diferentes com um servidor de produção.

Os resultados mostram que, quando as variáveis da tabela são empilhadas, você corre o risco de a consulta ser executada por dez minutos em vez de 100 milissegundos. Isso é um ótimo exemplo do desempenho medonho que você pode experimentar se não conhecer as regras. Mesmo quando usamos chaves primárias, porém, o número de linhas com as quais estamos lidando significa que o uso de tabelas temporárias agora é duas vezes mais rápido.

Não vou me aprofundar nos detalhes dos planos de execução por trás disso métricas de desempenho, além de fornecer algumas explicações gerais das principais diferenças. Para as consultas da tabela temporária, o otimizador, munido de um conhecimento completo da cardinalidade e dos metadados das restrições de chave primária, escolhe um operador Merge Join eficiente para executar a operação de junção.Para a variável de tabelas com chaves primárias, o otimizador sabe a ordem das linhas na coluna de junção e que elas não contêm duplicatas, mas assume que está lidando apenas com uma linha, e então escolhe uma junção de Loops Aninhados. Aqui, ele verifica uma tabela e, para cada linha retornada, realiza buscas individuais na outra tabela. Isso se torna menos eficiente quanto maiores os conjuntos de dados e é especialmente ruim nos casos em que verifica a variável de tabela CommonWords, porque resulta em mais de 60 mil buscas de Dracula variável de tabela. A junção de Loops aninhados atinge o ‘pico de ineficiência’ para duas consultas de dez minutos usando pilhas de variáveis de tabela, porque envolve milhares de varreduras de tabela de CommonWords. Curiosamente, as duas consultas de “palavras comuns no Drácula” têm um desempenho muito melhor e isso ocorre porque, para essas duas, o otimizador escolheu uma junção Hash Match.

No geral, as tabelas temporárias parecem ser a melhor escolha , mas ainda não terminamos! Vamos adicionar a OPTION (RECOMPILE) dica às consultas que usam as variáveis de tabela com chaves primárias e execute novamente os testes para essas consultas, e as consultas originais usando as tabelas temporárias. Deixamos de lado as pilhas ruins por enquanto.

Como você pode ver, a vantagem de desempenho da tabela temporária desaparece. contagens de linhas corretas e entradas ordenadas, o otimizador escolhe a união de mesclagem muito mais eficiente.

O que, você se pergunta, aconteceria se você desse a esses pobres heaps o OPTION (RECOMPILE) dica também? Veja, a história muda para eles, de modo que os três tempos estão muito mais próximos.

Curiosamente, as duas “palavras comuns no Drácula” questionam que estavam rápido, mesmo em pilhas, agora é muito mais lento. Armado com as contagens de linha corretas, o otimizador muda sua estratégia, mas como ainda não tem nenhum dos metadados úteis disponíveis quando definimos restrições e chaves, ele faz uma escolha ruim. Ele verifica o CommonWords heap e, em seguida, tenta uma “agregação parcial”, estimando que agregará de 60 mil linhas a algumas centenas. Não sabe que não há duplicatas, portanto na verdade, ele não agrega de forma alguma, e a agregação e subsequente spill de junção para tempdb.

A plataforma de teste

Observe que esta é a plataforma de teste em sua forma final mostrando desempenho praticamente igual para os três tipos diferentes de tabela. Você precisará remover as OPTION (RECOMPILE) dicas para voltar ao original.

Listagem 2

Conclusões

Não há nada de imprudente em usar variáveis de tabela. Eles têm um melhor desempenho quando usados para os fins a que se destinam e fazem a sua própria limpeza. Em um determinado ponto, os compromissos que fornecem a eles um melhor desempenho (não acionando recompilações, não fornecendo estatísticas, sem reversão, sem paralelismo) tornam-se sua queda. o tamanho do resultado que causará problemas para uma variável de tabela. Os resultados que mostrei neste artigo irão sugerir que isso simplifica demais as questões. Existem dois fatores importantes: se você tiver um resultado de mais de, digamos, 1000 linhas (e este número depende do contexto), então você precisa ter um PRIMARY KEY ou UNIQUE chave para qualquer consulta que se junte a uma variável de tabela. Em um determinado ponto, você também precisará acionar uma recompilação para obter um plano de execução decente, que tem sua própria sobrecarga.

Mesmo assim, o desempenho pode sofrer muito, especialmente se você estiver executando um processamento mais complexo , porque o otimizador ainda não tem acesso às estatísticas e, portanto, não tem conhecimento da seletividade de qualquer predicado de consulta. Nesses casos, você precisará passar a usar tabelas temporárias.

Leitura adicional