[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.

2 thoughts on “[T-SQL Challenge #01] Desafio: Filtro em coluna datetime

  1. Fábio Martinho de Almeida (Fabão) January 13, 2020 / 10:41 am

    Fala meu amigo!
    Poderia ser criada uma coluna computada, usando o ano da data e depois criar o índice com esse novo atributo computado?
    Grade abraço!

    Liked by 1 person

    • Marcel Inowe January 14, 2020 / 7:17 am

      Fala meu amigo, como vai? A resposta é essa mesmo! Meus parabéns e muito obrigado pela sua colaboração.
      Quando eu estiver pelo RJ combinamos para eu pagar seu almoço.
      Abs!

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.