Recentemente, me deparei com um problema em que uma procedure estava causando timeout na aplicação. Ao investigar, percebi que a origem do problema estava relacionada à escrita da consulta. Quando a procedure foi inicialmente criada, a tabela era pequena, e o uso do operador OR no WHERE não causava grandes impactos.
A particularidade dessa consulta é que ela possui diferentes condições de filtro, baseadas em um parâmetro @TIPO que varia de 1 a 7. À medida que o volume de dados cresceu, o uso do OR passou a afetar significativamente o desempenho, resultando em timeouts e consumo elevado de recursos.
Uma das maneiras de resolver o problema é transformar a procedure em uma procedure dinâmica, fazendo com que o filtro adotado na query seja adaptado de acordo com o parâmetro passado, otimizando a consulta para cada situação específica.
Criei a procedure pr_organizationlevel2 para simular uma consulta que encontrei em um ambiente de produção. Nessa consulta, temos o parâmetro @TIPO, que pode receber 4 tipos de entrada diferentes, e cada tipo aplica um filtro específico na consulta.
PROBLEMA INICIAL
Na versão original da procedure, utilizávamos a cláusula OR para identificar qual tipo de parâmetro o @TIPO recebeu. Essa abordagem resultava em uma execução lenta pois por conta dos OR o plano ignorava índices e sempre fazia scan, chegando a 35 segundos de tempo de execução, e frequentemente causava timeouts na aplicação. Além disso, a consulta fazia a leitura de 10 milhões de linhas, ou seja, varria a tabela inteira para retornar somente 1 linha.
SOLUÇÃO
Uma maneira eficiente de solucionar esse problema foi transformar a procedure em uma procedure dinâmica. Com essa abordagem, a procedure adota apenas o filtro referente ao @TIPO fornecido, sem a necessidade de varrer a tabela inteira devido ao uso excessivo do OR.
Utilizamos um CASE WHEN para que cada valor de @TIPO adicione um filtro específico à consulta executada dentro da procedure.
Observação: É importante notar que, após o EXEC sp_executesql @sql, é necessário especificar o tipo da variável e, em seguida, passar a variável, conforme mostrado na imagem acima (mais informações no link de apoio).
RESULTADOS
Ao aplicar a procedure dinâmica, obtivemos um ganho significativo em tempo de execução e utilização de recursos do sistema. A consulta ficou muito mais rápida, passou a utilizar o índice e a consumir menos recursos. De fato lendo e retornando somente uma linha.
SCRIPT
LINK DE APOIO