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

5 thoughts on “[T-SQL Tip #01] Como filtrar colunas datetime?

  1. José D i z January 6, 2020 / 6:33 pm

    Olá Marcel.
    O assunto de como criar predicados sargable é algo que realmente deve ser divulgado ao máximo, pois predicado non sargable pode se tornar fator de baixa performance se houver índice que atenda ao respectivo predicado sargable.

    Like

    • Marcel Inowe January 6, 2020 / 6:41 pm

      Olá José obrigado pelo seu comentário. Concordo com você. Exploraremos o assunto nos próximos posts. A ideia nesse foi justamente falar de algo relativamente simples, mas que causa muitos problemas.
      Até mais.

      Like

  2. Mariana Serni Sampaio January 7, 2020 / 7:38 pm

    Vou usar seu post para corrigir o meu = )

    Like

    • Marcel Inowe January 8, 2020 / 6:39 am

      Oi Mari, que bom que você gostou do conteúdo.
      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.