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?

Anúncios

Um pensamento sobre “Query Tuning: Set-based Thinking

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