Index Seek em um Where com função ? Como ?


 

Pessoal,

Neste projeto também me deparei com uma situação bem interessante. Tínhamos um banco de um software de terceiro que controlava a frente de caixa da rede. Em determinada tabela, o collate dela era Case Sensitive.

Uma das consultas campeãs de reads era feita nela, com um “where upper(campo) = “.  A consulta era feita ad-hoc e por motivos que não me cabem não se podia (ou não tinha tempo hábil), para alterar a consulta tirando o upper.

O index scan era feito na consulta e tinha um custo altíssimo de IO (logical reads altos e physical reads também) e realmente travava  a galera quando rodava , que era de 1 em 1 hora por um job e demorava 20 minutos. OU seja. 20 minutos que parava.

Como foi resolvido ?

Usando uma computed column e um índice nesta computed column.

Vamos lá ..criando o ambiente :

  • Criar uma tabela com um campo Case Sensitive

create table TesteComputed (    codigo int identity Primary key Clustered,
                                               Valor1 decimal(10,2),
                                               Valor2 decimal(10,2),
                                                Nome varchar(100) Collate Latin1_General_CS_AS
                                        )
go                       

 

  • Criar o Indice na tabela , campo Nome
create index idx_TesteComputed_Nome on TesteComputed(nome)

 

  • Popular a tabela

;WITH ComputedCte (Id, CteValor1, CteValor2)
AS
(
SELECT    1,
        ABS(CheckSum(NEWID()) / 1000000.8764),
        ABS(CheckSum(NEWID()) / 2000000.7652)
UNION ALL
SELECT    id+1,
        ABS(CheckSum(NEWID()) / 1000000.8764 ) ,
        ABS(CheckSum(NEWID()) / 2000000.7652)
FROM ComputedCte
where    id < 31000
)
insert into TesteComputed(Valor1,Valor2,Nome)
Select    Ctevalor1,
             Ctevalor2,
             Case
            when Ctevalor1 between  1 and 999              Then    ‘Campo2’
            when Ctevalor1 between  1000 and 10000     Then    ‘Campo1’
            when Ctevalor1 between  12000 and 20000   Then    ‘Campo2’
        end       
from    ComputedCte
OPTION (MAXRECURSION 31000)       

 

Agora vamos aos testes :

Se eu montar esta consulta sem o upper vamos ver como fica :

 

select    nome
from    TesteComputed
where    Nome = ‘Campo1’

(16632 row(s) affected)

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

SELECT [nome] FROM [TesteComputed] WHERE [Nome]=@1
  |–Index Seek(OBJECT:([DBA].[dbo].[TesteComputed].[idx_TesteComputed_Nome]), SEEK:([DBA].[dbo].[TesteComputed].[Nome]=CONVERT_IMPLICIT(varchar(8000),[@1],0)) ORDERED FORWARD)

 

Além do que se eu não colocar o ‘Campo1′ com a primeira letra em maiúsculo como está na tabela, não me retornará nada, pois a coluna é case sensistive.

Agora usando o upper :

select    nome
from    TesteComputed
where    upper(Nome) = ‘CAMPO1’

(16632 row(s) affected)
Table ‘TesteComputed’. Scan count 1, logical reads 81, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
select nome   from TesteComputed  where upper(Nome) = ‘CAMPO1’
  |–Index Scan(OBJECT:([DBA].[dbo].[TesteComputed].[idx_TesteComputed_Nome]),  WHERE:(upper([DBA].[dbo].[TesteComputed].[Nome])=’CAMPO1′))

 

Podemos ver bem que o logical reads aumentou e ao invés de um seek estamos fazendo scan agora.

Vamos transformar esse scan em um seek ainda usando o upper no where ?

  • Crie mais um campo na tabela , este campo sera um campo calculado. Ele tera o upper do campo nome :
Alter table TesteComputed add UpperNome as upper(nome)

 

  • Crie o indice neste campo e atualiza as estatisticas com fullscan ( não é necessário, mas é altamente recomendável – vai demorar um pouquinho)

create index idx_TesteComputed_uppernome on TesteComputed(uppernome)
include (nome)
go
update statistics TesteComputed with fullscan

 

Agora rode a mesma consulta novamente :

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

select nome   from TesteComputed  where upper(Nome) = ‘CAMPO1’
  |–Index Seek(OBJECT:([DBA].[dbo].[TesteComputed].[idx_TesteComputed_uppernome]), SEEK:([DBA].[dbo].[TesteComputed].[UpperNome]=’CAMPO1′) ORDERED FORWARD)

 

Ta aí..temos um index seek.

Bom, isso foi extrema valia para mim, pois consegui resolver sem precisar acionar o suporte da empresa numa sexta a noite.

Dsta maneira, aquela consulta que demorava 20 minutos e tinha aproximadamente 800.000 logical reads e 180 physical,

caiu para 130.000 reads (movimentava uma quantidade grande de linhas) e 0 physical reads.

Sem falar que caiu para 1 minuto e meio.

Estarei postando sobre alguns outros casos de sucesso que tive usando colunas computadas, neste mesmo projeto.

Um abraço !!!!

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in SQL SERVER EM GERAL. Bookmark the permalink.

10 Responses to Index Seek em um Where com função ? Como ?

  1. Rodrigo says:

    Muito bom post Laerte, prova que o Optimizer está ficando esperto! 🙂 Parabéns pelos conteúdos e pelo visto nada melhor desafios para aprendermos não é?Show velhinho!Abraço!

  2. Laerte says:

    Pô..se tu não aparecesse para dar o seu parecer eu ia ficar chateado. Sei o quanto é criterioso e se não estivesse bom teria falado. Meu amigo PFE ..é muito bom ler isso de um profissional como você. Um grande a braço e sucesso !!!

  3. Gustavo says:

    Olá Laerte (e não Laércio (rs))Legal o artigo. Não sei de onde você andou tirando tanta motivação para postar um por dia.Só lembrando nem sempre o SQL Server vai "sacar" essa construção (o próprio MOC diz que o SQL Server pode ignorar o índice (2005 e anteriores)). Essa possibilidade é bem menor na Enterprise em relação às demais edições.Abs,

  4. Laerte says:

    Grande Gustavo. HAHAHA..pô Laércio é duro né hahahah. Meu amigo, estou com um tempinho agora….voltei do projeto lá e to me dedicando a escrever as aventuras…senão esqueço tudo..não lembro nem que comi no almoço rs..rs.rs. Velho..pra ser sincero, eu tinha visto essa dica num post do optmizer team a um tempo. Na hora que vi no cliente pensei : se funciona com lower (post original), funciona com upper. E fiz o teste. O SQL lá era o Enterprise 2005. Fiz varios testes e funcionou. Mas valeu pela observação. E brigado por FINALMENTE, depois de conversarmos tanto tempo tu acertar meu nome ..rs..rs..rs. Abraços meu amigo

  5. Fabiano Neves says:

    Fala Laércio… :-)Cara, nessa vou ter que dizer que o nosso concorrente é melhor… não seria muito mais facil poder fazer isso?create index idx_TesteComputed_Nome2 on TesteComputed(UPPER(nome)) No Oracle, funciona 😦 …

  6. Laerte says:

    Hahaha..até tu Fabiano !!!!.. Depois de meia hora , consegui entrar no blog..!!!..Decepciona esse live as vezes !!!Fabiano..fala baixo isso rs.rs..rs..Mas é verdade..além do que nao precisa criar uma coluna a mais !!!

  7. Thiago says:

    No pgSQL também temos uma solução parecida (estilo a que o Fabiano já comentou). Mas fico feliz que podemos ver que estamos evoluindo nesta área. Fiquei um pouco intrigado agora com o que o Gustavo disse, e já que tem tempo :P, poderia fazer alguns teste com edições diferentes do SQL, o que acha?Abraço!

  8. Heberton says:

    Olá Laerte,Cara, muito bom mesmo o artigo, pelo visto vamos ter vários outros pela frente, como mesmo o Gustavo falou, de onde vem tanta expiração?Abraços.

  9. Laerte says:

    Intaum Thiago, estava pensando justamente nisso…aproveitar esse "gap" entre um projeto e outro pra testar. Já sei que no 2005 e 2008 Enterprise funcionam !!!! Vou baixar outras versões para testar !!!

  10. Laerte says:

    Grande Heberton !!!!… Cara..não é que estou inspirado não ..é problema de veia. Veieira…Se eu não postar esqueço..hhehhehe…Tu vai ver daqui a pouco fico um tempo sem postar, num outro projeto…depois venho e posto.

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