DATEDIFF, CONVERT, SUBSTRING e funções sobre colunas na cláusula WHERE e JOINs

Boa Noite,

Já há muito tempo vejo uma prática recorrente na elaboração, manutenção e otimização de consultas presente em fóruns, aulas, softwares de terceiros enfim… Trata-se do uso de funções sobre colunas em cláusula WHERE que normalmente leva a consulta com desempenho abaixo do esperado. Para ilustrar, imaginemos a seguinte pergunta que demanda um instrução SELECT.

– Quais são os clientes que foram cadastrados há menos de trinta dias ?

Por intuição e tradução literal da pergunta para uma cláusula SQL, é bem comum que essa pergunta seja respondida com a seguinte consulta:

SELECT * FROM Clientes WHERE DateDIFF(dd,DataCadastro,GETDATE()) < 30

Se executássemos essa hipotética consulta, ela certamente retornaria os dados necessários. A função DATEDIFF calculará a diferença entre a data de cadastro e a data atual e aqueles registros que tiverem menos de trinta dias de diferença irão ser retornados. Entretanto, retornar pode ser suficiente para aplicações com poucos usuários e tabelas com pouco volume, mas aplicações críticas e alta concorrência, não basta “retornar”. É preciso fazer isso com eficiência e a construção apresentada não tem essa característica. Vejamos o porquê.

O uso do DATEDIFF

O uso do DATEDIFF é muito comum para calcular diferenças entre datas e não há nada errado em necessitar do resultado desse cálculo. Podemos calcular a diferença entre a data em que um determinado pedido foi feito e sua entrega, a diferença em horas que um empregado começou a trabalhar e encerrou o seu expediente ou ainda a diferença em segundos do início e do término de um treino de um maratonista. Ainda assim, há situações em que seu uso é indevido, especialmente em algumas situações na cláusula WHERE.

— Cria uma tabela com 2.000.000 registros
CREATE TABLE Clientes (
    ID INT NOT NULL,
    Nome VARCHAR(100) NOT NULL,
    Sexo CHAR(1) NOT NULL,
    DataNascimento DATETIME NOT NULL,
    CPF CHAR(11) NOT NULL,
    Cidade VARCHAR(100) NOT NULL,
    UF CHAR(2) NOT NULL,
    CEP CHAR(9) NOT NULL,
    Renda MONEY NOT NULL,
    DataCadastro DATETIME NOT NULL)

DECLARE @i INT
SET @i = 1

WHILE (@i < 2000000)
BEGIN
    INSERT INTO Clientes VALUES (
        @i,‘João da Cunha Costa e Silva Mendes’,‘M’,
        ‘19780101’,‘79985421240’,‘Brasília’,‘DF’,
        ‘70360-123’,4570.32,‘20110106’)
    SET @i = @i + 1
END

— Insere o cliente 2.000.000
INSERT INTO Clientes VALUES (
    2000000,‘Flávia Gomides Arnaldo Ferreira Costa’,‘F’,
    ‘19790331’,‘70034267890’,‘Brasília’,‘DF’,
    ‘70231030’,7832.46,GETDATE())

— Cria um índice sobre a data de cadastro
CREATE INDEX IX_DataCadastro ON Clientes (DataCadastro)

Esse script cadastrou 2.000.000 de clientes sendo que apenas um desses clientes foi cadastrado no dia de hoje. Se fosse perguntado quais os clientes que foram cadastrados a menos de 30 dias, esse seria o único registro retornado. Vejamos então o desempenho da consulta SQL sugerida anteriormente.

SET STATISTICS IO ON
SELECT * FROM Clientes WHERE DateDIFF(dd,DataCadastro,GETDATE()) < 30

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta com DATEDIFF

Como podemos perceber, para a recuperação do único registro que foi cadastrado a menos de trinta dias, foi necessário ler 28170 blocos o que em outras palavras nos levou a um TABLE SCAN inevitável. À primeira vista, alguns irão perguntar porque ler todos esses registros se existia um índice sobre a coluna Data ? Não seria melhor usar o índice para localizar o registro ao invés de fazer um SCAN nessa tabela ? Com certeza ler alguns poucos blocos seria muito melhor do que ler 28170 blocos especialmente se houvesse muita concorrência nessa tabela, mas se há um índice porque ele não foi usado ?

O índice IX_DataCadastro foi criado sobre a coluna DataCadastro produzindo uma lista ordernada por essa coluna muito semelhante à tabela abaixo:

DataCadastro ID da Linha Hash
2011-01-06 00:00:00.000 0x002A000001000000 (048da76b633b)
2011-01-06 00:00:00.000 0x002A000001000100 (6fa7aeb4ed68)
2011-01-06 00:00:00.000 0x002A000001000200 (393826e39435)
2011-07-02 11:30:25.390 0x7491000001000000 (3782542520bc)

Como o índice é sobre a coluna DataCadastro, todos os valores possíveis de DataCadastro virão ordenados e os respectivos ponteiros (Id da linha) são referenciados logo em seguida. Podemos ver que a data 06/01/2011 vem repetida diversas vezes, pois, temos quase 2.000.000 de ocorrências para esse valor. A última data 02/07/2011 (que foi o GETDATE() quando escrevi esse artigo) aparece apenas uma única vez.

A pesquisa pelo único registro que foi cadastrado a menos de trinta dias (2011-07-02) deveria pesquisar essa lista, encontrar o última entrada e com base no RowID (Id da linha) localizar o registro correspondente (Flávia Gomides). Entretanto, se observarmos a cláusula WHERE “DateDIFF(dd,DataCadastro,GETDATE()) < 30” podemos observar que nossa pesquisa não refere-se exatamente a DataCadastro, mas sim ao resultado de uma função aplicada sobre a Data de Cadastro. Nesse caso, nossa lista é praticamente inútil, pois, ela é baseada na DataCadastro e não no resultado da função DateDIFF. Teríamos aqui duas possibilidades. A primeira é converter toda a lista e fazer o cálculo produzindo uma nova lista ou simplesmente ignorar a lista e fazer a varredura de tabela. O otimizador optou por ignorar a lista, pois, possivelmente o trabalho de conversão é mais dispendioso que efetuar o TABLE SCAN. Há dois pontos de perda de desempenho aqui. O primeiro é que a lista foi ignorada e um SCAN foi realizado. O segundo é que temos uma lista que precisa ser mantida a cada INSERT, UPDATE (na DataCadastro) e DELETE. Resumidamente, temos um lista que tem um ônus para ser mantida, mas que não trás nenhum benefício para a consulta.

Se for observada a natureza da consulta, é perfeitamente possível reescrevê-la para usufruir da lista (índice) que foi criado sendo muito mais eficiente. O que se deseja saber é que clientes foram cadastrados a menos de trinta dias. Ao invés de aplicarmos uma função sobre a data e assim desprezarmos a lista, podemos simplesmente calcular a data de trinta dias atrás e fazer uma comparação da DataCadastro com esse valor.

SELECT * FROM Clientes WHERE DateDIFF(dd,DataCadastro,GETDATE()) < 30
SELECT * FROM Clientes WHERE DataCadastro > DateADD(dd,-30,GETDATE())

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta com DATEADD

Nesse caso é muito visível a diferença de desempenho. O uso do DATEDIFF produz um TABLE SCAN que lê 28170 páginas e o uso do DATEADD produz uma pesquisa de índice que lê apenas 4 páginas para trazer o registro. Não é a toa que 100% do esforço foi atribuído ao DATEDIFF já que o DATEADD representa um parcela irrisória do custo do total. Quando utilizamos o DATEADD, aplicamos essa função sobre o GETDATE() para produzir uma data que represente 30 dias atrás. Isso é feito uma única vez, pois, a função não foi aplicada sobre nenhuma coluna. Após obtermos a data de trinta dias atrás, basta compará-la com a coluna DataCadastro. Uma vez que a coluna DataCadastro não sofreu nenhuma modificação, o índice será naturalmente avaliado no plano de execução e nesse caso foi utilizado retornando a consulta de forma muito mais eficiente. A razão do desempenho não é que o DATEADD é mais rápido que DATEDIFF, mas simplesmente o fato de que a coluna DataCadastro não teve nenhuma função aplicada sobre si.

O uso do CONVERT

É bem comum o uso do CONVERT em conjunto com datas na cláusula WHERE especialmente em situações que envolvam horas. Me parece que há uma certa resistência, medo ou alguma razão muito forte para que a maioria dos códigos evite consultas com o uso do BETWEEN, <=, >= em prol de uma igualdade. Talvez seja o fato de que o padrão DMY não é o mundialmente aceito além do que normalmente durante os primeiros contatos com programação é comum o uso e abuso de funções de formatação. Bem, o fato é que o CONVERT em cláusulas WHERE padece do mesmo mal que o DATEDIFF, ou seja, o não aproveitamento pleno dos índices existentes.

No meu cadastro, o último cliente foi cadastrado no dia 02/07/2011. Vejamos se a consulta abaixo consegue recuperá-lo

SELECT * FROM Clientes WHERE DataCadastro = ‘2011-07-02’

Embora o registro “Flávia Gomides” tenha sido cadastrado no dia 02/07/2011, ele foi cadastrado às 11:30 e por isso a consulta acima não o recuperou. O sufixo do horário às vezes é bem inconveniente. Uma forma que é bem utilizada para eliminá-lo é o uso do CONVERT.

SELECT * FROM Clientes WHERE CONVERT(CHAR(10),DataCadastro,103) = ’02/07/2011′

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta com CONVERT

O TABLE SCAN dispensa muitos comentários. O registro dessa vez foi retornado, mas de forma bem ineficiente, pois, foi necessário ler 2.000.000 de linhas para conseguir retornar um único registro. Toda vez que vejo uma consulta no fórum ou algum aluno sugerindo o uso do CONVERT em uma situação dessas é exatamente nesse plano de execução que eu penso. Vejamos como fazer para não incorrer em tanta ineficiência.

Se a data é acrescentada do horário, podemos tentar uma comparação com base em um intervalo e não em uma data específica. Supondo que o registro tenha sido cadastrado no dia 02/07/2011 em algum horário desconhecido, temos a certeza de que esse registro está em algum momento entre a 0h do dia 02/07/2011 e a 0h do dia 03/07/2011. Só que não podemos incluir a 0h do dia 03/07/2011 para não retonar os registros desse dia. Duas boas sugestões para compararmos com o CONVERT.

SELECT * FROM Clientes WHERE CONVERT(CHAR(10),DataCadastro,103) = ’02/07/2011′
SELECT * FROM Clientes WHERE DataCadastro >= ‘20110702’ And DataCadastro < ‘20110703’
SELECT * FROM Clientes WHERE DataCadastro BETWEEN ‘20110702’ And ‘20110702 23:59.999’

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta com BETWEEN e intervalos

Como pode-se observar o uso de intervalos >= e < ou o BETWEEN são muito mais eficientes, pois, utilizam o índice corretamente enquanto que o CONVERT opta por um SCAN. Enquanto o CONVERT precisou ler 28710 blocos, as outras construções ficaram com apenas 4 páginas cada uma. Sem dúvida uma incomparável eficiência em relação ao CONVERT.

Uma dúvida é comum é como usar o GETDATE() para capturar os clientes que foram cadastrados no mesmo dia. Eu utilizei o dia 02/07/2011 porque era o meu GETDATE() na época desse artigo, mas como montar uma consulta que varia de acordo com o dia em que for executada sem a necessidade de extrair o dia previamente ? Bem, dá um pouquinho mais de trabalho, mas podemos fazê-lo sem evitar o CONVERT.

SELECT * FROM Clientes
WHERE
    DataCadastro >= CONVERT(CHAR(8),GETDATE(),112) And
    DataCadastro < CONVERT(CHAR(8),GETDATE()+1,112)

O CONVERT com o formato 112, colocará a data atual no formato YYYYMMDD sem a parte do horário retornando no meu caso ‘20110702’. O GETDATE() + 1 retorna a data atual com um dia a mais e com o uso do CONVERT no formato 112, teríamos ‘20110703’. Essa forma de consulta também fará uso eficiente do índice Seek e dispensa o TABLE SCAN.

Em alguns posts, aulas e consultorias já vi algumas outras soluções criativas para resolver esse problema que também merecem ser comentadas: CONVERT para datas, YEAR, MONTH e DAY.

SELECT * FROM Clientes
WHERE
    YEAR(DataCadastro) = YEAR(GETDATE()) And
    MONTH(DataCadastro) = MONTH(GETDATE()) And
    DAY(DataCadastro) = DAY(GETDATE())

(1 row(s) affected)
Table ‘Clientes’. Scan count 3, logical reads 4771, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta com YEAR, MONTH e DAY

É uma alternativa criativa, mas não necessariamente performática. Ao contrário do CONVERT que gerou um SCAN com 28710 blocos, o uso das funções YEAR, MONTH e DAY de forma combinada fez uma leitura de 4771 blocos (pouco mais de 25% da tabela). Esse é o resultado de uma varredura e conversão da lista (índice). Considerando que varrer o índice e convertê-lo para consulta posterior foi mais performático, o otimizador optou por fazê-lo e por isso vimos um Index Scan (varredura do índice) ao invés de um TABLE SCAN (varredura de tabela). Ainda assim é quase 1200 vezes mais lento que a leitura de 4 páginas quando o índice foi usado com eficiência e para pesquisa (Seek). Outros dois pontos a comentar sobre o uso dessas três funções é que foi necessário um tabela temporária (Worktable) e o uso de paralelismo para que ela pudesse funcionar. Em um ambiente com muita concorrência, essas condições podem tornar o resultado um pouco menos performático. Embora interessante, a melhor opção ainda é evitar funções sobre colunas.

SUBSTRING, LEFT, LTRIM e funções textuais

Não raras às vezes é necessário fazer comparações com base em pedaços de uma string quando não o tratamento de dados durante a execução de uma query (como se fosse papel de uma consulta fazer tratamento de dados a cada execução). Para demonstrar que essas funções também não são uma boa idéia, vamos modificar alguns registros e criar novos índices.

— Cria um índice sobre a coluna Nome
CREATE INDEX IX_Nome ON Clientes (Nome)

— Consulta os clientes que começam com a letra F
SELECT * FROM Clientes WHERE SUBSTRING(Nome,1,1) = ‘F’
SELECT * FROM Clientes WHERE LEFT(Nome,1) = ‘F’

Table ‘Clientes’. Scan count 3, logical reads 12543, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Clientes’. Scan count 3, logical reads 12543, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta com SUBSTRING e LEFT

As funções SUBSTRING e LEFT tiveram o mesmo papel nesse caso, ou seja, apenas retirar a primeira letra de cada nome para descobrir os clientes que iniciam o nome com a letra F. A pesquisa não efetuou um SCAN na tabela, mas sim no índice. Embora o SCAN no índice seja mais eficiente que um SCAN na tabela, isso não significa que a consulta foi eficiente. Para retornar cada uma leu 12543 páginas que representa quase 50% do total dos 28710 blocos da tabela. Vejamos como otimizar essa pesquisa:

— Consulta os clientes que começam com a letra F
SELECT * FROM Clientes WHERE SUBSTRING(Nome,1,1) = ‘F’
SELECT * FROM Clientes WHERE Nome LIKE ‘F%’

Desempenho - Consulta com LIKE

Como o LIKE não aplicou nenhuma função sobre a coluna, o índice pode ser utilizado eficientemente já que não há necessidade de conversão. O índice IX_Nome é uma lista ordenada de nomes e embora o LIKE não seja uma correspondência exata, nesse caso ele pode localizar as entradas da lista que começam com F e varrê-las até que haja um troca de letra (digamos G), pois, a lista não sofreu nenhuma conversão e o LIKE possui um string antes do % (se o LIKE começasse com % não seria possível usar o índice).

Algumas funções textuais costumam ser utilizadas para limpeza de dados como o LTRIM e o RTRIM. A seguir, farei uma atualização para que possámos utilizar essas funções:

— Atualiza o registro “Flávia Gomides”
UPDATE Clientes SET Nome = ‘ Flávia Gomides Arnaldo Ferreira Costa’
WHERE ID = 2000000

— Faz a pesquisa retirando os espaços em branco
SELECT * FROM Clientes WHERE LTRIM(Nome) LIKE ‘F%’

Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta LTRIM

Dessa vez foi feita uma varredura (TABLE SCAN) lendo 28170 blocos. A varredura não deve-se ao LIKE, mas ao uso do LTRIM sobre a coluna nome. Não há otimização a ser feita na query, pois, de fato o nome está com um espaço antes e normalmente não sabemos quantos espaços existem o que nos leva ao uso do LTRIM. Isso só mostra que se temos problemas de dados de má qualidade, temos que corrigí-los sempre na entrada e não a cada consulta. Um cadastro com milhares de clientes iria sofrer um bocado se cada consulta usasse um LTRIM porque a aplicação não retirou os espaços antes de efetivar o cadastro. Ainda que haja sujeira na base, é muito melhor efetuar um UPDATE para corrigir uma única vez do que usar o LTRIM a cada consulta.

User Defined Functions

A partir da versão 2000, o SQL Server permite a construção de funções próprias ao invés das funções já existentes. Será que essas funções vão utilizar da mesma lógica ?

— Cria uma função para verificar se a renda é maior que 5.000
CREATE FUNCTION dbo.FnRendaMaior5000 (@Renda MONEY)
RETURNS TINYINT
As
BEGIN
    DECLARE
@bRendaMaior5000 TINYINT
    SET @bRendaMaior5000 = 0
    IF @Renda > 5000
        SET @bRendaMaior5000 = 1
    RETURN(@bRendaMaior5000)
END
GO

— Cria um índice sobre a coluna Renda
CREATE INDEX IX_Renda ON Clientes (Renda)

— Consulta os clientes com Renda superior a 5000
SELECT * FROM Clientes WHERE Renda > 5000
SELECT * FROM Clientes WHERE dbo.FnRendaMaior5000(Renda) = 1

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - User Defined Functions

O uso de funções escalares teve um péssimo desempenho nesse caso. O TABLE SCAN ocorreu inevitavelmente pelas mesmas razões que as anteriores.

Expressões e colunas

Não é apenas o uso de funções sobre colunas que pode levar a ineficiência. Muitas vezes, utiliza-se colunas em expressões para efetuar um determinado cálculo. Vamos supor que o Marketing está avaliando a possibilidade de selecionar clientes que possuam uma renda capaz de pagar uma parcela de R$ 2.800,00 e ainda possuam R$ 2.000,00 de sobra. Há duas formas de estruturar essa consulta:

— Clientes que possuam uma renda capaz de pagar uma parcela de R$ 2.800,00
— e ainda possuam R$ 2.000,00 de sobra
SELECT * FROM Clientes WHERE Renda – 2800 >= 2000
SELECT * FROM Clientes WHERE Renda >= 4800

(1 row(s) affected)
Table ‘Clientes’. Scan count 3, logical reads 4771, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

20110701_10_Expressoes

Na primeira opção, apenas traduzimos o enunciado da consulta, ou seja, é preciso subtrair da renda 2.800 reais e o resultado tem de ser maior que 2.000 (Renda – 2800 > 2000). Na segunda possibilidade, apenas modificamos essa inequação repassando o 2.800 para o lado direito e invertendo o seu sinal somando-o a 2.000 e obtendo 4.800. Ambas as consultas retornam o mesmo resultado, mas a segunda consulta leu apenas 4 blocos (Seek) contra 4771 blocos (SCAN) da primeira consulta além de dispensar a tabela temporária (Work Table) e o paralelismo.

Os impactos na concorrência

Até agora demonstrei os devastadores efeitos das funções em atividades de consulta, mas será que há impactos nas operações de escrita ? E se existem quais seriam eles ? Vejamos então como o uso de funções em uma cláusula WHERE pode afetar atividades concorrentes em operações de escrita. Utilizarei três sessões (cada uma representa uma janela separada do SSMS). A primeira sessão deve ter sua consulta executada antes da segunda que deve ser executada antes da terceira.

— Sessão 1
— Muda o valor da renda do primeiro registro
UPDATE Clientes SET Renda = 1000 WHERE ID = 1

— Sessão 2
— Abre uma transação e não fecha
BEGIN TRAN
UPDATE
Clientes SET Nome = ‘Flávia Gomides Arnaldo Ferreira Costa’
WHERE DateDIFF(dd,DataCadastro,GETDATE()) < 30

— Sessão 3
SELECT * FROM Clientes WHERE Renda = 1000

Sabemos claramente que a segunda sessão irá atualizar o registro com ID 2.000.000 durante uma transação e que a terceira sessão irá ler o registro de ID 1, pois, é o único que possui renda igual a 1.000 logo após o UPDATE da primeira sessão. Entretanto, curiosamente a terceira sessão não conclui e parece estar bloqueada.

De fato a terceira sessão está bloqueada e não é preciso sequer consultar o SQL Server para verificar. Se um UPDATE é uma operação de leitura seguida de uma operação de escrita não é difícil de descobrir porque a terceira sessão ficou bloqueada. Vejamos as operações de leitura associadas às sessões dois e três.

— Sessão 2
SELECT * FROM Clientes WHERE DateDIFF(dd,DataCadastro,GETDATE()) < 30

— Sessão 3
SELECT * FROM Clientes WHERE Renda = 1000

20110701_11_Sessoes

Como a sessão 2 fez uma consulta usando o DATEDIFF sobre a coluna DataCadastro, foi necessário efetuar um TABLE SCAN sobre toda a tabela. Isso significa que toda a tabela foi lida (ainda que uma única linha tenha sido retornada). Da mesma forma que toda a tabela foi lida, toda a tabela foi bloqueada impedindo que a sessão 3 conseguisse fazer o SELECT (ainda que sobre uma linha que nada tinha a ver com a linha da sessão 2). Isso significa que consultas mal escritas que fazem TABLE SCAN são muito danosas não só às operações de consulta, mas também às operações de escrita. Para provar que a sessão 2 de fato bloqueia a sessão 3, basta efetuar um ROLLBACK na transação da sessão 2 que a sessão 3 retorna de imediato.

Há muitos softwares de prateleira medíocres que resolvem isso com o NOLOCK generalizado ao invés de se concentram no real problema e solucioná-lo de fato. O NOLOCK apenas permite que a sessão 3 funcione sem ficar bloqueada pela sessão 2, mas não iria evitar o SCAN ineficiente provocado pelo DATEDIFF. Vejamos como seria uma implementação correta (certifique-se que o ROLLBACK foi feito na sessão 2).

— Sessão 2
— Abre uma transação e não fecha
BEGIN TRAN
UPDATE
Clientes SET Nome =
‘Flávia Gomides Arnaldo Ferreira Costa’
WHERE DataCadastro > DateADD(dd,-30,GETDATE())

— Sessão 3
SELECT * FROM Clientes WHERE Renda = 1000

Como já vimos no início do artigo, a consulta de clientes com base na data de cadastro e no DateADD faz uso eficiente do índice e a consulta com base na renda também. Essa eficiência permite que as sessão 2 e a 3 possam rodar em paralelo sem que a sessão 3 seja bloqueada pela sessão 2. A implementação correta gera vários benefícios como evitar que a sessão 2 faça um SCAN e que a sessão 3 rode em paralelo. O uso do NOLOCK não proporciona os dois benefícios e seria a saída padrão para aqueles que não podem ou não querem elaborar consultas de qualidade. Agora que o efeito na concorrência foi demonstrado, não esqueça de efetuar um COMMIT na sessão 2, pois, esse registro será utilizado mais adiante.

Situações inevitáveis

Acredito que a essa altura do artigo já tenha ficado muito claro que o uso de funções sobre colunas em uma cláusula WHERE pode facilitar a escrita da consulta, mas quase sempre tem efeitos indesejados. Entretanto, algumas situações podem ser realmente difíceis de escapar do uso de funções sobre colunas. Vamos supor que exista a necessidade de consultar todas os clientes cadastrados no mesmo dia do mês da data atual para fazer alguma promoção.

— Consultar todos os cadastros efetuados no mesmo dia do mês
SELECT * FROM Clientes WHERE DAY(DataCadastro) = DAY(GETDATE())

(1 row(s) affected)
Table ‘Clientes’. Scan count 3, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Função DAY

Essa é uma situação em que apenas a revisão da consulta não é suficiente. No meu exemplo, o dia da data atual é 02 e a consulta precisa retornar todos os clientes cadastrados no dia 02 independente de que mês ou ano que seja. Nesse caso não há como fazer com que a consulta por si só seja eficiente. Ainda assim, há soluções para resolver o problema com a solução de COMPUTED COLUMNS.

— Altera a estrutura da tabela
ALTER TABLE Clientes ADD Dia As DAY(DataCadastro)

— Cria um índice sobre a coluna Dia
CREATE INDEX IX_Dia ON Clientes (DataCadastro)

— Consultar todos os cadastros efetuados no mesmo dia do mês
SELECT * FROM Clientes WHERE Dia = DAY(GETDATE())
SELECT * FROM Clientes WHERE DAY(DataCadastro) = DAY(GETDATE())

(1 row(s) affected)
Table ‘Clientes’. Scan count 3, logical reads 4771, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - COMPUTED COLUMNS

A coluna DIA é uma coluna baseada em um cálculo (COMPUTED COLUMNS) e é mantida automaticamente quando há qualquer mudança na coluna DataCadastro. Após a criação da coluna, podemos materializá-la criando um índice sobre ela. Após a criação do índice, pudemos ver a redução de 28710 para 4771 páginas trocando um TABLE SCAN por um Index SCAN. Seria bom mesmo ver a eliminação dos SCANs, mas o otimizador optou por não fazê-lo nessa situação (embora haja várias situações que ele consiga fazer um Seek sobre uma coluna calculada). Um destaque é para que a criação do índice permitiu inclusive que não alterássemos a consulta. Mesmo usando a função DAY sobre a coluna DataCadastro, o otimizador percebeu que poderia utilizar a coluna calculada para otimizar a consulta reduzindo a quantidade de leituras. O uso de colunas calculadas pode otimizar consultas que inclusive não podem ser reescritas :) Um ponto positivo do ORACLE em relação ao SQL Server é que ele permitiria a criação do índice dispensando a criação da coluna calculada com a seguinte sintaxe: CREATE INDEX IX_Dia ON Clientes (DAY(DataCadastro)).

Analogias a JOINs

Embora o artigo tenha focado o uso de funções sobre colunas na cláusula WHERE até aqui, não significa que os JOINs estejam livres desses problemas. Pelo contrário, tudo o que foi dito aplica-se a eles sem exceção. Converter colunas, alterar collation ou ainda colocar expressões em JOINs também fará mal uso dos índices podendo levar a Index Scan ou ainda Table Scans. Vejamos um exemplo:

— Cria uma tabela com a coluna DataCadastro e um código promocional
CREATE TABLE Promocoes (DataCadastro CHAR(10), Desconto DECIMAL(5,2))

— Insere um registro para a promoção de hoje
INSERT INTO Promocoes VALUES (CONVERT(CHAR(10),GETDATE(),103),0.1)

— Verifica os clientes elegíveis para o desconto de hoje
SELECT C.*, P.Desconto FROM Clientes As C
INNER JOIN Promocoes As P
ON CONVERT(CHAR(10),C.DataCadastro,103) = P.DataCadastro

(1 row(s) affected)
Table ‘Promocoes’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 3, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

20110701_14_JoinsConvert

A tabela de Promoções colocou a data como CHAR(10) no formato DD/MM/YYYY enquanto que a tabela de clientes tem a data com o tipo DATETIME. Não consigo imaginar os reais motivos pelos quais alguém colocaria uma data como CHAR(10), mas enfim… Como as tabelas têm tipos de dados diferentes, para uní-las será necessário usar o CONVERT. Como pudemos perceber, foi um desastre que resultou em um TABLE SCAN na tabela de clientes. O correto mesmo seria efetuar um trabalho de limpeza e igualar os tipos de dados, mas admitindo que isso não seja possível, a recomendação é não converter os dados da maior tabela (ou daquelas que farão filto na cláusula WHERE) e sim das menores tabelas. Como ainda temos a questão do horário, o JOIN fica mais complexo porém muito mais performático (muito menos I/O).

— Verifica os clientes elegíveis para o desconto de hoje
SELECT C.*, P.Desconto FROM Clientes As C
INNER JOIN Promocoes As P
ON CONVERT(CHAR(10),C.DataCadastro,103) = P.DataCadastro

SELECT C.*, P.Desconto FROM Clientes As C
INNER JOIN Promocoes As P
ON C.DataCadastro >=
    CAST(
        RIGHT(P.DataCadastro,4) +
        SUBSTRING(P.DataCadastro,4,2) +
        LEFT(P.DataCadastro,2) As DateTime) And
    C.DataCadastro <
    CAST(
        RIGHT(P.DataCadastro,4) +
        SUBSTRING(P.DataCadastro,4,2) +
        LEFT(P.DataCadastro,2) As DateTime) + 1

(1 row(s) affected)
Table ‘Promocoes’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 3, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table ‘Promocoes’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Como a tabela Clientes possui muito mais registros que a tabela de promoções e um índice sobre a coluna DataCadastro não é uma boa idéia efetuar a conversão na tabela Clientes e sim na tabela de promoções. O problema é que a tabela de clientes não tem só a data, mas também o horário o que atrapalharia o resultado mesmo que convertermos a data da tabela de promoções. Nesse caso, efetuei um comparação com base em um intervalo composto pela conversão da data e do seu dia subsequente de forma semelhante ao início do artigo. O resultado já fala por si próprio. Apenas 5 páginas lidas contra 28710 páginas da primeira implementação. Essa é a diferença entre uma consulta eficiente e uma que retorna um TABLE SCAN ainda que ambas tragam o mesmo resultado.

Por fim, uma última consulta envolvendo JOINs que é uma situação comum quando há collations diferentes no banco de dados.

— Cria uma tabela de Clientes VIPs
CREATE TABLE ClientesVIPs (
    Nome VARCHAR(100)
    COLLATE Latin1_General_CS_AS NOT NULL,
    Desconto DECIMAL(5,2))

— Insere um registro
INSERT INTO ClientesVIPs VALUES (‘Flávia Gomides Arnaldo Ferreira Costa’,0.1)

— Retorna os dados dos Clientes VIPs
SELECT * FROM Clientes As C
INNER JOIN ClientesVIPs As CV ON C.Nome = CV.Nome

Como a collation das colunas é diferente, a consulta não conseguirá ser executada

Msg 468, Level 16, State 9, Line 3
Cannot resolve the collation conflict between “Latin1_General_CS_AS” and “Latin1_General_CI_AI” in the equal to operation.

Aos que quiserem descobrir mais sobre como resolver problemas de Collation eu recomendo o artigo “Como resolver problemas relacionados a conflitos de Collation”, mas vejamos algumas possibilidades de retornar.

— Retorna os dados dos Clientes VIPs (converte a tabela Clientes)
SELECT * FROM Clientes As C
INNER JOIN ClientesVIPs As CV ON C.Nome COLLATE Latin1_General_CS_AS = CV.Nome

— Retorna os dados dos Clientes VIPs (converte a tabela ClientesVIP)
SELECT * FROM Clientes As C
INNER JOIN ClientesVIPs As CV ON C.Nome = CV.Nome COLLATE Latin1_General_CI_AI

— Retorna os dados dos Clientes VIPs (converte a tabela ClientesVIP)
— Força o índice
SELECT * FROM Clientes As C WITH (INDEX=IX_Nome)
INNER JOIN ClientesVIPs As CV ON C.Nome = CV.Nome COLLATE Latin1_General_CI_AI

— Subquery
SELECT * FROM Clientes As C
WHERE Nome IN (
    SELECT CAST(Nome As VARCHAR(100)) COLLATE Latin1_General_CI_AI
    FROM ClientesVIPs)

— Subquery com índice forçado
SELECT * FROM Clientes As C WITH (INDEX=IX_Nome)
WHERE Nome IN (
    SELECT CAST(Nome As VARCHAR(100)) COLLATE Latin1_General_CI_AI
    FROM ClientesVIPs)

Table ‘ClientesVIPs’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 3, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘ClientesVIPs’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘ClientesVIPs’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘ClientesVIPs’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 3, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘ClientesVIPs’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Como podemos ver dessa vez a recomendação de converter a tabela menor sozinha não deu efeito. Mesmo o uso de JOINs contra subqueries não surtiu diferença e foi necessário fazer um SCAN na tabela de clientes (28710 páginas). Se a gente der uma pequena mão para o otimizador e forçarmos o índice podemos reduzir a quantidade de leituras de 28710 páginas para apenas 5 páginas e assim obter muito mais eficiência. Não quero dizer com isso que forçar o índice deva ser prática comum no uso de JOINs com colunas convertidas e collations diferentes, mas sim que quando há funções sobre colunas há muitos efeitos colaterais indesejados. Se todas as colunas tivessem a mesma collation sequer teríamos de fazer essas avaliações. Bastaria que alguém atualizasse a estrutura da tabela uma única vez e a collation estaria corrigida. É muito mais eficiente do que corrigir a collation a cada consulta efetuada.

Conclusão

O uso de funções sobre colunas é prática comum em construção de consultas SQL. Quando há poucos usuários, baixo volume de dados e pouca concorrência, tais práticas podem passar muito desapercebidas principalmente no ambiente de desenvolvimento onde essas condições normalmente são encontradas. No entanto, para que aplicações tenham condições de escalar para um ambiente de missão crítica, tais práticas são proibitivas. Funções em cláusula WHERE e JOINs devem ser uma prática descartável e evitada sempre que possível. Utilizá-las é praticamente anular os benefícios de uma indexação eficiente. Boas consultas podem se beneficiar de bons índices, consultas ruins dificilmente irão fazê-lo. O comportamento aqui apresentado não restringe-se de forma nenhuma ao SQL Server. Mesmo bancos como o ORACLE irão sofrer dos mesmos problemas com o uso de funções como to_char tão utilizada para converter datas na cláusula WHERE.

[ ]s,

Gustavo

6 Respostas para “DATEDIFF, CONVERT, SUBSTRING e funções sobre colunas na cláusula WHERE e JOINs

  1. Mais uma aula. Muito bom mesmo!

  2. Glayson Sergio da Silva

    Parabéns pela matéria, já assinei o Feed do seu site.

    Dúvida com relação ao uso de funções com a cláusula where:

    Tenho um campo que armazena data/hora, e utilizo funções para conversão de data, conforme você mencionou, porém, ao reconfigurar a cláusula e comparar com a forma atual, o plano de execução considerou 50% de custo pra cada instrução, assim:

    — forma antiga
    select *
    from tabela
    where (convert(datetime, floor(convert(float, campodatahora))) between ‘2012-01-01’ and ‘2012-01-31’)

    — reformulado
    select *
    from tabela
    where (campodatahora between ‘2012-01-01 00:00:00’ and ‘2012-01-31 23:59:59:999’)

    Após a execução, verifiquei que não havia índice, então criei, verifiquei novamente o plano de execução e o SQL não utilizou o índice criado.

    Qual seria a explicação?

  3. Glayson Sergio da Silva

    Olá,

    Peço perdão, a análise do Post anterior foi feita num banco com 482 registros. Fiz a verificação num outro banco de dados com um volume maior de dados e ele fez um tratamento diferente, porém, num banco de dados com 859.491 registros, ele considerou os custos de 64% [forma antiga] e 36% [reformulado] com um índice criado e considerou os custos de 52% [forma antiga] e 48% [reformulado] sem a existência de índices.

    O que me encuca, é que num banco igual, porém de outro cliente, no mesmo servidor, menor mas muito próximo em volume, com 815.284 registros, com um índice criado ele considerou um custo de 100% para a forma antiga, e os mesmos 52% e 48% encontrados acima.

    Qual a explicação?

    • Boa Noite Glayson,

      Acredito que possivelmente as estatísticas não estejam atualizada.
      Ainda assim, é bom dar uma verificada no uso do IO com o SET STATISTICS IO ON.
      Acredito que ele diga mais por si só do que propriamente o plano

      [ ]s,

      Gustavo

  4. Cara, parabéns!
    Artigo extremamente abrangente no quesito indices.
    Me ajudou muito!

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s