Tabelas Temporárias X Variáveis de Tabela

Venho compartilhar com vocês uma situação que geralmente é motivo de dúvidas. O que devo usar para materializar temporariamente meus dados: uma variável de tabela (i.e. @tabela) ou uma tabela temporária (i.e. #tabela)? Hoje mostrarei um caso onde a melhor escolha foi uma tabela temporária, explicitando as razões que me fizeram optar por ela ao invés de uma variável de tabela. Antes de começarmos, vamos falar um pouco sobre nossas duas opções.

TABELAS TEMPORÁRIAS

Tabelas temporárias tem o símbolo “#” como prefixo. As mais comumente utilizadas são as tabelas temporárias locais que possuem apenas um “#” (i.e. #tabela). No entanto, também é possível cria tabelas temporárias globais, utilizando dois “#” como prefixo (i.e. ##tabela). A diferença entre as duas está no escopo e na visibilidade. Enquanto a tabela local só pode ser acessada pelo criador e apenas dentro da sessão onde foi criada, a tabela temporária global pode ser acessada por qualquer sessão, ou seja, todos podem acessar e alterar seu conteúdo e até remover a tabela.

Tabelas temporárias são criadas e possuem uma representação física no tempdb, apesar de que quando são pequenas – e o SQL Server tem memória sobrando –, suas páginas permanecem na memória. Quando a memória livre é pouca, o SQL Server persiste a tabela em disco.

Em minha opinião, o fato mais importante para escolher entre tabelas temporárias e variáveis de tabela é o SQL Server criar e manter estatísticas de distribuição de dados para as tabelas temporárias da mesma forma como faz para tabelas permanentes. Isso é especialmente importante quando se indexa uma tabela temporária, pois a informação de distribuição permite que se obtenham planos de execução mais eficientes. Também como consequência deste fato, consultas nas tabelas temporárias serão recompiladas caso as estatísticas sejam atualizadas, atinjam o limiar de recompilação (recompilation treshold), etc. Isto não ocorre para variáveis de tabela – a não ser com a opção RECOMPILE –, pois nenhuma estatística é criada ou mantida.

VARIÁVEIS DE TABELA

Variáveis de tabelas geralmente causam confusões por serem pouco compreendidas. Um erro comum é acreditar que essas tabelas residirão apenas em memória, sem representação física. Outro erro comum é achar que se deve sempre optar por variáveis de tabela ao invés de tabelas temporárias.

Assim como tabelas temporárias, variáveis de tabela possuem representação física no tempdb e, quando pequenas – e o SQL Server tendo memória de sobra –, essas tabelas residirão em memória. Já o escopo e a visibilidade dessas tabelas são bem mais limitados. As variáveis de tabelas são apenas acessíveis dentro de um único lote (batch), como as demais variáveis.

Como mencionei antes, o SQL Server não cria ou mantém estatísticas para variáveis de tabela como faz para as tabelas temporárias. Por um lado, este fato pode ser especialmente problemático para tabelas grandes, onde o plano de execução pode se tornar impreciso. Por outro lado, isso também acarreta em menos – ou nenhuma – recompilações.

ENTÃO, QUAL OPÇÃO DEVO ESCOLHER?

Depois de entendermos melhor nossas opções, devemos levantar algumas questões:

• O SQL Server precisará de estatísticas de distribuição ou estimativas precisas de cardinalidade para gerar planos de execução eficientes? Se precisar, qual é o custo de utilizar planos ineficientes quando não tivermos estatísticas disponíveis?

• Qual é o custo das recompilações se utilizarmos tabelas temporárias?

No caso de estarmos trabalhando com tabelas grandes, o custo de utilizarmos planos ineficientes pode ser muito elevado. Já para tabelas pequenas, este custo geralmente é aceitável ou até mesmo desprezível. Como foi citado [1] no Books Online da Microsoft:

“Do not use table variables to store large amounts of data (more than 100 rows). Plan choices may not be optimal or stable when a table variable contains a large amount of data”.

Em outras palavras, não utilize variáveis de tabela para armazenar grandes quantidades de dados (mais de 100 linhas), pois os planos de execução podem não ser eficientes ou estáveis.

Recompilações são caras, então se tivermos uma tabela criada dentro de uma stored procedure, quanto mais vezes esta SP for executada, maior o seu custo. É claro que em algumas situações precisaremos que nossos dados sejam persistidos num determinado escopo em que apenas uma opção atenderá.

Vemos, então, que é necessário avaliar bem nossos requisitos e a massa de dados com a qual iremos trabalhar. Com isso trago o tuning realizado numa SP que utilizava uma variável de tabela e, quando o número de linhas aumentava muito, seu tempo de execução subia drasticamente.

ESTUDO DE CASO

Após a alteração de uma dada SP, observou-se que esta variava muito em tempo de execução. Este variava de poucos segundos até pouco mais de 2 horas e 15 minutos. Começamos a observá-la capturando os tempos de execução e o número de linhas ao longo de alguns dias. Verificamos que mais de 95% das execuções envolviam menos de 1.000 linhas e que dos 5% restantes o número de linhas variava de 1.000 até 100 mil linhas. O comportamento desta SP seguia o esperado até um determinado número de linhas – por volta de 40 mil – e após este ponto, seu tempo de execução aumentava seguindo uma tendência exponencial. Logo abaixo segue um gráfico de dispersão com as medições realizadas relacionando o tempo de execução da SP (duração) com o número de linhas.

Hmm... nice curve!

Hmm… nice curve!

Ao analisarmos o código, identificamos um JOIN utilizando uma variável de tabela que estava causando o tempo de execução inesperado quando o número de linhas era grande. Isso se dá por não existir estatísticas na variável de tabela, desta forma o SQL Server sempre estimava que esta tabela teria apenas um linha. Logo, utilizando nosso primeiro questionamento, ficou evidente que o custo de utilizar planos de execução ineficientes era astronômico. Isto já seria suficiente para optar por uma tabela temporária, mas também analisamos se o custo das recompilações impactaria nesta escolha. Esta SP era executada menos de 90 vezes por dia, um número pequeno que pouco seria influenciado pelas recompilações. Desta forma, alteramos a SP e substituímos a variável de tabela por uma tabela temporária.

No interesse de garantirmos o mesmo desempenho para os 95% dos casos (com poucas linhas), examinamos o antes e depois da alteração. Abaixo estão dois gráficos mostrando as medições realizadas para cada uma das soluções.

imagem02
imagem03
Com estas medições, comprovamos que para a maioria dos casos (95%), o desempenho permaneceu praticamente o mesmo, ou seja, menos de 40 segundos. Já para os casos atípicos (5%) com muitas linhas, o desempenho fui muito superior, como mostra o gráfico com as medições após as alterações.

imagem04
Aqui fica clara a diferença no comportamento da SP em relação ao número de linhas. Enquanto na versão anterior (utilizando variável de tabela) a SP tinha uma duração de aproximadamente 2 horas e 15 minutos para 75 mil linhas, após a alteração (utilizando tabela temporária) a SP possui um tempo de execução de aproximadamente 5 minutos para mais de 100 mil linhas!

CONCLUSÃO

Neste artigo aprendemos um pouco mais sobre variáveis de tabela e tabelas temporárias e também a escolher mais precisamente qual é a melhor opção para cada caso. Vimos, através de um caso real, o impacto que esta decisão pode ter caso seja mal tomada. Seu usuário agradece!

REFERÊNCIAS

[1] Books Online – table (Transact-SQL)
[2] Itzik Ben-gan – Inside Microsoft SQL Server 2008: Programming. Chapter 7: Temporary Tablesand Table Variables.

Anúncios

Um pensamento sobre “Tabelas Temporárias X Variáveis de Tabela

  1. Pingback: Retrospectiva 2016 – Comunidade SQL Server

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s