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.

Query Tuning: Set-based Thinking

Quando começamos a trabalhar com o processo de optimização de consultas, ou Query Tuning, logo descobrimos que existem várias “camadas” a serem estudadas. Cada um tem sua própria metodologia de tuning e não pretendo descrever uma hoje. No entanto, em sua maioria, o processo envolve a análise dos Waits a nível de instância, relacionar os principais Waits com os gargalos para definir os recursos sob pressão, encontrar o processo responsável e então, analisá-lo para optar por uma indexação adequada, uma revisão do código ou ambos. Neste artigo irei abordar apenas a revisão do código da consulta, me baseando numa solução orientada a conjuntos (set-based solution).

“Mas o que é uma solução orientada a conjuntos?” Esta é uma solução que realiza as operações necessárias (consultas, modificações de dados, etc) tratando o conjunto de dados como um todo, ao invés de processar os dados através de uma lógica iterativa (linha a linha). Se por “lógica iterativa” você entendeu cursores e loops, você está certo.

“Qual o problema com os cursores?” Basicamente, cursores vão contra a ideia do modelo relacional. O modelo relacional trabalha com conjuntos ao invés de linhas e registros. E o SQL Server é muito eficiente ao lidar com conjuntos. Já os cursores no SQL Server têm um overhead por iteração. Então, se o custo de varrer N linhas com uma consulta for dado por C, é possível expressar o custo da mesma consulta feita através de um cursor por C + N*O, onde O é o overhead por iteração. Existe uma forma prática de se medir o overhead de seu servidor que pode ser encontrado no capítulo 08 do livro Inside Microsoft SQL Server 2008: T-SQL Programming do Itzik Ben-Gan, mas deixo isso a título da curiosidade do leitor.

É claro que existem situações em que os cursores serão a solução mais performática, mas esses casos são tão poucos que sugiro que os deixem como última opção. Confesso que quando comecei a trabalhar com banco de dados tinha o hábito de usar cursores, hábito adquirido com o tempo que trabalhei como programador. Afinal de contas, é muito fácil para um programador lidar com ferramentas conhecidas como os cursores. Mas insisto: aprender a moldar suas soluções a uma lógica orientada a conjuntos é dar passos largos em direção à eficiência e à escalabilidade. É tempo muito bem investido.

Deixando a conversa de lado, vamos a um caso real que enfrentei recentemente onde trabalho. A situação é a seguinte: Após a conversão para a última versão de uma ferramenta de terceiros, o usuário responsável por um processo de importação de dados do fechamento contábil anual informou que o processo que antes rodava em até 20 minutos, agora estava levando o dia todo. Assim, a equipe de banco de dados foi acionada.

Depois de encontrar a stored procedure responsável pelo desespero do usuário, repetimos o processo na base de teste para identificar onde doía mais. Assim que iniciamos o processo, utilizei a sp_whoisactive do Adam Machanic para acompanhar a execução. Eis que percebo uma demora num UPDATE. Abro o comando (adaptado, para não me dar dores de cabeça ou processos futuros =) que está sendo executado no momento e vejo:

UPDATE Table1
SET NR_LINHA = @LINHA_INICIAL
WHERE ID       = @ID
AND ID_USUARIO = @ID_USUARIO
AND EXISTS( SELECT 1 FROM #Table2 R (NOLOCK)
             WHERE R.ID_LINHA   = @LINHA_INICIAL
               AND R.ID         = Table1.ID
               AND R.CONTA      = Table1.CONTA
               AND R.CUSTO      = Table1.CUSTO
               AND R.SALDO      = Table1.SALDO
               AND R.PERIODO    = Table1.PERIODO
               AND R.ID_USUARIO = Table1.ID_USUARIO)

Curioso com a utilização de uma tabela temporária para fazer o UPDATE, abro o plano de execução para ver o custo deste comando.

PlanoExecucao_UpdateCost

Para minha surpresa, o custo desta operação era muito baixo (0,1456960), mas executando novamente a sp_whoisactive, vi que o mesmo UPDATE ainda estava sendo executado, enquanto o número de leituras e tempo de CPU apenas aumentavam. Então, abri o código da stored procedure e busquei o trecho de código onde estava localizado este UPDATE. Segue o trecho com o verdadeiro problema.

SELECT IDENTITY(INT,1,1) AS ID_LINHA,* INTO #Table2
FROM Table1 (NOLOCK)
WHERE ID       = @ID
AND ID_USUARIO = @ID_USUARIO;
--
SELECT @LINHA_INICIAL = MIN(ID_LINHA)
      ,@LINHA_FINAL   = MAX(ID_LINHA)
FROM #ROWID (NOLOCK);
--
WHILE @LINHA_INICIAL <= @LINHA_FINAL
BEGIN
--
UPDATE Table1
SET NR_LINHA = @LINHA_INICIAL
WHERE ID       = @ID
AND ID_USUARIO = @ID_USUARIO
AND EXISTS( SELECT 1 FROM #Table2 R (NOLOCK)
             WHERE R.ID_LINHA   = @LINHA_INICIAL
               AND R.ID         = Table1.ID
               AND R.CONTA      = Table1.CONTA
               AND R.CUSTO      = Table1.CUSTO
               AND R.SALDO      = Table1.SALDO
               AND R.PERIODO    = Table1.PERIODO
               AND R.ID_USUARIO = Table1.ID_USUARIO)
--
SET @LINHA_INICIAL = @LINHA_INICIAL + 1
--
END

Tentei por um bom tempo entender o que o desenvolvedor deste trecho quis fazer, mas acho que até agora não tenho certeza. No entanto, o que este trecho faz é gerar uma tabela temporária (#Table2) com registros enumerados com base num subconjunto da Table1 e depois entra num loop e atualiza cada registro da Table1 que tenha um correspondente na #Table2, uma linha de cada vez. Adivinhe quantos registros eram carregados na #Table2 para o menor período de fechamento contábil? Nada menos que 92 MIL registros. Então, lembrando-se da formula para calcular o custo de um cursor, C + N*O, você já imagina a monstruosidade que custava esta parte do processo.

A solução foi mudar a lógica de iterativa para orientada a conjuntos. Procurando manter as regras de negócio deste trecho, depois de alguns minutos escrevi o trecho de código abaixo e substituí pelo trecho acima.

;WITH Table2 AS
(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ID_LINHA,*
FROM Table1
WHERE ID       = @ID
AND ID_USUARIO = @ID_USUARIO
)
UPDATE Table1
SET NR_LINHA = R.ID_LINHA
FROM Table2 R
WHERE R.ID       = Table1.ID
AND R.CONTA      = Table1.CONTA
AND R.CUSTO      = Table1.CUSTO
AND R.SALDO      = Table1.SALDO
AND R.PERIODO    = Table1.PERIODO
AND R.ID_USUARIO = Table1.ID_USUARIO;

A função da tabela temporária foi substituída por uma CTE e a lógica de atualização da Table1 foi mantida. E você acha que o plano de execução ficou caro? Olha só o resultado:

PlanoExecucao_UpdateSetBasedCost

O custo que era de 0,1456960 por iteração, foi para 0,6156450 para todo o conjunto. Qual o resultado final após a alteração? O processo que demorava o dia todo (em média 6 horas) foi executado em menos de 1 minuto.

Após a validação da equipe de desenvolvimento da ferramenta (pois é… ainda tivemos que esperar a validação para não perdermos o suporte deles) a alteração foi implantada em produção e o usuário teve um final feliz. Mais uma vez, a solução orientada a conjuntos salva o dia! E você, tem algum caso em que o tuning salvou o dia?