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

[T-SQL Tip #01] Como filtrar colunas datetime?

Nesse primeiro post da série T-SQL Tips iremos abordar como realizar filtros em colunas do tipo datetime. Esse é um tema relativamente simples, porém pode causar muitos problemas de performance na execução das queries se não forem feitos da forma correta.

É muito comum o uso do campo datetime na modelagem das tabelas para armazenar registros com data e hora como “2019-01-01 23:59:59.997”, porém também é muito comum o desenvolvedor não querer utilizar a hora armazenada na coluna para realizar o filtro e para isso ele acaba realizando algum tipo de conversão para remover/truncar a hora da coluna em tempo de execução da query e esse é o principal ponto que iremos abordar, pois isso pode causar alguns problemas de performance.

Antes de ir para os exemplos, é necessário entender que qualquer tipo de conversão em uma coluna que é utilizada em where ou join, pode fazer com que o predicado de busca se torne um predicado non-sarg(non search argument) e com isso o SQL Server pode optar por um Index Scan ao invés de um Index Seek ,o que normalmente é o mais indicado para ler menos páginas do índice/tabela.
O recomendado é que sempre tenhamos predicados de busca SARG(Search Argument) e para isso devemos evitar ao máximo as conversões ou uso de funções nas buscas. Falaremos um pouco mais sobre SARG/Nonsarg em um post futuro, mas durante os exemplos abaixo ficará mais claro o que é um predicado SARG e um Non-sarg e eu usarei esses termos para referenciar uma query “boa” de uma query “ruim”.

Tabela com coluna datetime

Abaixo uma tabela modelada com coluna datetime.

Tabela: Sales.SalesOrderHeaderEnlarged

A tabela acima faz parte do banco de dados AdventureWorks e foi criada utilizando o script de enlarged para deixá-la com uma massa de dados maior. Abaixo link para download do script:
https://www.sqlskills.com/blogs/jonathan/enlarging-the-adventureworks-sample-databases/

A tabela está com pouco mais de 1.290.000 de registros. Abaixo pequena amostragem de distribuição dos registros.

Amostra de dados

Para uma melhor performance eu criei um índice na tabela acima pela coluna OrderDate e adicionei a coluna CustomerID como include, pois serão essas colunas utilizadas nos exemplos.

CREATE NONCLUSTERED INDEX IX_SalesOrderHeaderEnlarged_OrderDate 
ON Sales.SalesOrderHeaderEnlarged(OrderDate) INCLUDE(CustomerID)

Vamos aos exemplos

Filtrando registros de um dia específico

Já presenciei em muitos casos onde o desenvolvedor deseja filtrar os dados de um determinado dia e a coluna por estar armazenando a hora, o desenvolvedor resolveu o problema da seguinte maneira:

SET STATISTICS IO, TIME ON
SELECT SalesOrderID, CustomerID, OrderDate 
FROM Sales.SalesOrderHeaderEnlarged
WHERE CONVERT(CHAR(8),OrderDate,112) = '20110531'

--Ou assim:

SET STATISTICS IO, TIME ON
SELECT SalesOrderID, CustomerID, OrderDate 
FROM Sales.SalesOrderHeaderEnlarged
WHERE YEAR(OrderDate) = 2011 
AND MONTH(OrderDate) = 5 
AND DAY(OrderDate) = 31

OK! zero problemas com relação a essa abordagem, ela FUNCIONA mas qual o problema dela? Vamos analisar o plano de execução dessa query.

Execution plan – Queries non-sarg

Como podemos notar em ambos os casos o operador com maior custo foi um Index Scan no índice criado.
Abaixo vamos ver como ficaram as estatísticas de leitura e tempo da query. Para isso eu utilizei SET STATISTICS IO, TIME ON antes da execução das queries.

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(41 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.

 SQL Server Execution Times:
   CPU time = 281 ms,  elapsed time = 371 ms.

(41 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.

 SQL Server Execution Times:
   CPU time = 94 ms,  elapsed time = 116 ms.

Como podemos ver em ambos os casos tivemos 3522 páginas lidas e tempos de 371ms e 116ms para apenas uma execução. Considerando que cada página de dados equivale a 8KB então tivemos 21,51MB de páginas lidas em cada execução.

Reescrevento a query e melhorando a performance

Conforme citamos acima, temos que evitar ao máximo a conversão da coluna que será utilizada como predicado de busca, para evitar que esse predicado se torne um predicado non-sarg, portanto o ideal é executarmos o filtro sempre respeitando o tipo do dado que foi definido para a coluna como no exemplo abaixo, onde eu farei a comparação de data acrescentando a hora das 00:00:00.000 até 23:59:59.997 para que eu possa retornar todos os registros do dia 31/05/2011.

SET STATISTICS IO, TIME ON
SELECT SalesOrderID, CustomerID, OrderDate 
FROM Sales.SalesOrderHeaderEnlarged
WHERE OrderDate BETWEEN '20110531 00:00:00.000' AND '20110531 23:59:59.997'

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

Execution Plan – Query SARG
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(41 rows affected)
Table 'SalesOrderHeaderEnlarged'. Scan count 1, logical reads 3, 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 = 0 ms,  elapsed time = 0 ms.

Analisando o plano de execução podemos notar que o operador de leitura do índice mudou para o Index Seek o que de cara pode me indicar uma performance melhor e a leitura de página caiu de 3522 para apenas 3 páginas lidas, ou seja, estamos falando de 27,51 MB contra apenas 24 KB e o tempo de 116 ms no melhor caso do exemplo anterior para 0 ms.
Bom…116 ms no melhor caso para 0 ms fica claro que tivemos 100% de melhor no tempo de execução da query, mas vamos continuar com mais alguns exemplos e comparativos abaixo.

Filtrando registros de um determinado mês

Abaixo alguns exemplos de abordagens que eu presencio constantemente de como desenvolvedores normalmente fazem para filtrar os registros de um determinado mês.

SET STATISTICS IO, TIME ON
SELECT SalesOrderID, CustomerID, OrderDate
FROM Sales.SalesOrderHeaderEnlarged
WHERE CONVERT(CHAR(8),OrderDate,112) BETWEEN '20110701' and '20110731'

-- ou assim quando o dev não quer se preocupar com quantos dias tem o mês (y)
-- meu coração chega explode de alegria quando eu vejo isso!!!

SET STATISTICS IO, TIME ON
SELECT SalesOrderID, CustomerID, OrderDate 
FROM Sales.SalesOrderHeaderEnlarged
WHERE LEFT(CONVERT(CHAR(8),OrderDate,112),6) = '201107'

Vejamos como ficaram os planos de execução e estatísticas de leitura e tempo dos exemplos acima.

Exectution Plan – Queries non-sarg
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(609 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.

 SQL Server Execution Times:
   CPU time = 719 ms,  elapsed time = 746 ms.

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

(609 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.

 SQL Server Execution Times:
   CPU time = 437 ms,  elapsed time = 488 ms.

Mais uma vez como podemos ver o Index Scan foi o operador com o maior custo nos planos acima e a leitura de páginas assim como nos exemplos non-sarg anteriores foi de 3522 páginas lidas, ou seja, 27,52 MB de dados lidos da memória.

Reescrevento a query e melhorando a performance

Mais uma vez vamos reescrever a query utilizando todo o range possível de hora para filtramos todos os registros que estiverem em um determinado range de data.

SET STATISTICS IO, TIME ON
SELECT SalesOrderID, CustomerID, OrderDate 
FROM Sales.SalesOrderHeaderEnlarged
WHERE OrderDate BETWEEN '20110701 00:00:00.000' AND '20110731 23:59:59.997'

Vejamos como ficaram os planos de execução e estatísticas de leitura e tempo dos exemplos acima.

Execution Plan – Query SARG
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(609 rows affected)
Table 'SalesOrderHeaderEnlarged'. Scan count 1, logical reads 6, 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 = 0 ms,  elapsed time = 0 ms.

Como podemos ver acima, mais uma vez o operador de leitura do índice mudou de Index Scan para Index Seek, o tempo de execução caiu de 488 ms no melhor caso para 0 ms e as leituras de páginas de 3522 páginas para apenas 6 páginas lidas(estamos lendo um mês inteiro, por isso o aumento é natural).

Estressando o ambiente

Vocês podem estar pensando: ok, mas 488 ms é um tempo baixo para a execução de uma query. Eu poderia dizer que sim é um tempo relativamente baixo para a execução de uma query, dependendo de sua importância dentro de um sistema. Agora imagine se essa query é responsável por capturar o valor das ações em tempo real da bovespa? Eu posso afirmar que 488 ms para o retorno do valor de uma ação é um problema para um sistema.

Abaixo vamos fazer uma pequena comparação de tempo utilizando o aplicativo SQL Query Stress com 100 execuções de cada query.

100 execuções da query non-sarg retornando dados de um mês
Query non-sarg
100 execuções da query Sarg retornando dados de um mês
Query sarg

Agora ficou claro, provar que uma query com tempo na casa dos 400 ms é um problema. Com apenas 100 execuções da query utilizando conversão levou-se 42 segundos, contra apenas 593 ms das mesmas 100 execuções da query escrita de forma correta.

Conclusão

Podemos ver que é muito mais fácil filtrar as datas utilizando a conversão para truncar a hora ou funções como DAY, MONTH e YEAR para não ter que se preocupar se o dia tem 28, 30 ou 31 dias, mas toda a facilidade tem seu preço.

Eu gosto sempre de pensar que não é porque eu posso fazer algo, que eu devo fazer algo e isso eu aplico nas minhas análises de performance do dia a dia.

Evitem ao máximo o uso das conversões ou funções para facilitar você chegar a um objetivo por mais cômodo/fácil que seja e tenham sempre em mente que:

O jeitinho/gambiarra de hoje será seu problema de amanhã.