[T-SQL Challenge #01] Solução: Filtro em coluna datetime

Fala pessoal tudo bem?
Na semana passada eu postei o [T-SQL Challenge #01] que foi o primeiro post da série de desafios aqui do nosso blog. Eu estou muito feliz com a repercussão que essa série teve, pois houve bastante devolutiva sobre a resolução do desafio e todos acertaram, inclusive um amigo meu que é DBA Oracle.

Antes da resolução do problema, segue abaixo o nome de todos que colaboraram com a resolução do problema.

Fábio Martinho, Tiago Balabuch, Rodrigo Mateus, Sinval Pereira, Lincoln Borges, Bianca Castro, Logan Merazzi, Hugo Torralbo, Marcio Junior, Vitor Gonçalves, Iago Lourenço, Wolney Marconi e Paulo Roberto Correia.

Galera, muito obrigado pela colaboração e parabéns por terem acertado o desafio.

Resolução

A resolução para esse problema onde não podemos alterar a procedure é relativamente simples. Basta criarmos uma coluna computada com a expressão YEAR(OrderDate) na tabela Sales.SalesOrderHeaderEnlarged marcando-a com a opção PERSISTED e após isso indexarmos a coluna computada. Pronto! Problema resolvido.

ALTER TABLE Sales.SalesOrderHeaderEnlarged ADD YearOrderDate AS YEAR(OrderDate) PERSISTED
GO
CREATE INDEX IDX_YearOrderDate ON Sales.SalesOrderHeaderEnlarged(YearOrderDate) INCLUDE(CustomerID, OrderDate)
GO

O Query Optimizer do SQL Server identifica que há uma coluna computada e indexada com a expressão utilizada na procedure sp_getSalesOrder e utiliza a coluna computada e índice para criar o plano de execução e com isso fazer um Index Seek no índice criado.

Galera, isso é SENSACIONAL! Aplausos para o Query Optimizer.

Caso queiram saber mais sobre colunas computadas segue o link oficial da documentação.
https://docs.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-ver15

Vamos ao teste de execução da procedure.

Execution Plan – Procedure sargable
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(7853 rows affected)
Table 'SalesOrderHeaderEnlarged'. Scan count 1, logical reads 29, 
physical reads 0, read-ahead reads 0, lob logical reads 0, 
lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
  CPU time = 16 ms,  elapsed time = 169 ms.

Como podemos notar, tivemos um ótimo ganho de performance. Na execução do desafio tivemos 3522 páginas lidas contra apenas 29 e 419 ms contra 169 ms.

Analisando de forma geral com o intuito de resolver o problema do Index Scan do desafio, podemos dizer que esso é um problema resolvido e que venha o próximo desafio.

Agora, imagine que esse seja um problema do seu mundo real. Ambiente de produção sendo altamente impactado por uma procedure executando uma função para tratar o ano, ou qualquer outro tipo de expressão que a torne uma procedure non-sarg.

Como forma PALIATIVA eu daria essa solução para o time de desenvolvimento com intuito de estabilizar o ambiente, porém eu iria salientar com todos os responsáveis pelo sistema que essa é uma medida PALIATIVA e que a forma DEFINITIVA de solucionar o problema é alterar o código da procedure aplicando as boas práticas para torná-la uma procedure com uma expressão sargable, pois uma coluna calculada persistida e indexada pode impactar em um maior tempo de escrita.

Caso vocês tenham alguma outra solução para esse tipo de problema, por favor, compartilhe conosco. Eu só conheço essa solução.

Pessoal, espero que tenham gostado do primeiro desafio. Mais uma vez nosso muito obrigado a todos que desprenderam seu tempo resolvendo a questão. É isso que nos motiva a escrever e compartilhar um pouco do nosso dia a dia.

Fiquem ligados no nosso blog e canal do youtube.

Até o próximo!

[T-SQL Challenge #01] Desafio: Filtro em coluna datetime

Olá pessoal,
No post anterior [T-SQL Tip #01] Como filtrar coluna datetime? demos início a série de dicas sobre T-SQL e Performance, onde eu explico um pouco sobre como devemos realizar filtros em coluna datetime para termos sempre a melhor performance possível e evitarmos futuros problemas.

Hoje iniciamos a série T-SQL Challenge onde postaremos alguns desafios de T-SQL baseado nos posts do T-SQL Tips. Essa série será sempre composta por dois posts onde o primeiro é o desafio e o segundo será a resolução do desafio. Caso o desafio seja resolvido por um leitor do blog, a resolução dada pelo leitor será postada no blog caso ele permita.

Baseado no problema discutido no [T-SQL Tip #01] vamos ao desafio.

Imagine que você tenha uma procedure que realiza filtro por uma coluna datetime, essa procedure está com uma performance degradada pois no plano de execução o operador de leitura do índice da tabela é um Index Scan.
Você precisa resolver esse problema de performance, fazendo com que o operador de leitura do índice mude para um Index Seek, mas você não pode alterar o código da procedure.

Como você resolveria esse problema?

Procedure de exemplo

Vamos criar uma procedure de exemplo onde o desenvolvedor quer retornar todos os registros de venda de um determinado ano, utilizando a função YEAR para facilitar o trabalho dele.

CREATE PROCEDURE dbo.sp_getSalesOrder
@YEAR INT
AS
BEGIN
  SELECT SalesOrderID, CustomerID, OrderDate 
  FROM Sales.SalesOrderHeaderEnlarged
  WHERE YEAR(OrderDate) = @YEAR
END

Sensacional essa abordagem acima, se não fosse a utilização da funçao YEAR em uma coluna que está indexada.

Abaixo o plano de execução e estatísticas de leitura e tempo.

Execution Plan – Procedure non-sarg
SQL Server Execution Times:
CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
CPU time = 0 ms, elapsed time = 0 ms.
(7853 rows affected)
Table 'SalesOrderHeaderEnlarged'. Scan count 1, logical reads 3522, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 234 ms,  elapsed time = 419 ms.

Como podemos notar o SQL Server fez um Index Scan no índice devido o uso da função YEAR e realizou 3522 logical reads com 419 ms de tempo.

Solução(alterando o código)

Em uma análise de performance de query eu sempre começo analisando o código da query em busca de um ponto de melhoria e somente depois eu parto para a criação/manutenção de índice e em alguns casos modelagem da tabela.
No exemplo acima a resolução seria remover a função YEAR do where e fazer um filtro na coluna OrderDate que contemple todo o período do ano desejado. É uma resolução fácil, mas um pouco mais trabalhosa do que simplesmente fazer YEAR(OrderDate) = @YEAR.
Veja uma possível solução abaixo.

CREATE PROCEDURE dbo.sp_getSalesOrder
@YEAR INT  
AS  
BEGIN  
  SELECT SalesOrderID, CustomerID, OrderDate   
  FROM Sales.SalesOrderHeaderEnlarged  
  WHERE OrderDate BETWEEN 
  CAST(@YEAR AS VARCHAR(4)) +'0101 00:00:00.000' AND 
  CAST(@YEAR AS VARCHAR(4)) +'1231 23:59:59.997'
END  

DESAFIO

Levando em consideração que você NÃO PODE(em hipótese alguma) alterar a procedure como eu alterei acima, como você resolveria o problema do Index Scan causado pelo predicado YEAR(OrderDate) = @YEAR e faria essa procedure executar um Index Seek e ter uma performance aceitável?

Seguem as regras:
1. Não é permitido alterar a procedure;
2. Criar outra procedure não é uma solução válida;
3. É permitido alterar a tabela, exceto a coluna OrderDate;
4. É permitido criar/modificar índice;

Favor enviar as possíveis soluções para marcel@datatuning.com.br e se alguém me der uma solução eu publicarei os créditos no próximo post e PAGAREI um almoço no próximo SQL Saturday que nos encontrarmos.

O desafio está lançado.
Boa sorte e bons estudos.