Index Seek em Where com função. Como?
Certa vez, em um projeto, 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,<br /> Valor1 decimal(10,2),<br /> Valor2 decimal(10,2),<br /> Nome varchar(100) Collate Latin1_General_CS_AS<br /> )<br />go - Criar o Indice na tabela, campo Nome
create index idx_TesteComputed_Nome on TesteComputed(nome)- Popular a tabela
;WITH ComputedCte (Id, CteValor1, CteValor2)<br />AS<br />(<br />SELECT 1,<br /> ABS(CheckSum(NEWID()) / 1000000.8764),<br /> ABS(CheckSum(NEWID()) / 2000000.7652)<br />UNION ALL<br />SELECT id+1,<br /> ABS(CheckSum(NEWID()) / 1000000.8764 ) ,<br /> ABS(CheckSum(NEWID()) / 2000000.7652) <br />FROM ComputedCte<br />where id < 31000<br />)<br />insert into TesteComputed(Valor1,Valor2,Nome)<br />Select Ctevalor1,<br /> Ctevalor2,<br /> Case <br /> when Ctevalor1 between 1 and 999 Then 'Campo2'<br /> when Ctevalor1 between 1000 and 10000 Then 'Campo1'<br /> when Ctevalor1 between 12000 and 20000 Then 'Campo2'<br /> end <br />from ComputedCte<br />OPTION (MAXRECURSION 31000)Agora, vamos aos testes
Vamos ver como fica se eu montar esta consulta sem o upper:
select nome <br />from TesteComputed<br />where Nome = 'Campo1' <br /><br />(16632 row(s) affected) <br /><br />Table 'TesteComputed'. Scan count 1, logical reads 45, physical<br />reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0,<br />lob read-ahead reads 0. <br /><br />SELECT [nome] FROM [TesteComputed] WHERE [Nome]=@1<br /> |--Index<br />Seek(OBJECT:([DBA].[dbo].[TesteComputed].[idx_TesteComputed_Nome]),<br />SEEK:([DBA].[dbo].[TesteComputed].[Nome]=CONVERT_IMPLICIT(varchar(8000),[@1],0))<br />ORDERED FORWARD)Além do que, se eu não colocar o ‘Campo’ com a primeira letra em maiúsculo como está na tabela, não me retornará nada, pois a coluna é case sensitive.
Agora usando o upper :
select nome<br />from TesteComputed<br />where upper(Nome) = 'CAMPO1'<br />(16632 row(s) affected)<br />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.<br />select nome from TesteComputed where upper(Nome) = 'CAMPO1'<br /> |--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 será um campo calculado. Ele terá o upper do campo nome:
Alter table TesteComputed add UpperNome as upper(nome)- Crie o índice neste campo e atualize as estatísticas com fullscan (não é necessário, mas é altamente recomendável – vai demorar um pouquinho)
create index idx_TesteComputed_uppernome on TesteComputed(uppernome)<br />include (nome)<br />go<br />update statistics TesteComputed with fullscanAgora rode a mesma consulta novamente:
(16632 row(s) affected)<br />Table 'TesteComputed'. Scan count 1, logical<br />reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0,<br />lob physical reads 0, lob read-ahead reads 0. <br /><br />select nome from TesteComputed where upper(Nome) = 'CAMPO1'<br /> <br />|--Index<br />Seek(OBJECT:([DBA].[dbo].[TesteComputed].[idx_TesteComputed_uppernome]),<br />SEEK:([DBA].[dbo].[TesteComputed].[UpperNome]='CAMPO1') ORDERED FORWARD)Tá aí… temos um index seek.
Bom, isso foi extrema valia para mim, pois consegui resolver sem precisar acionar o suporte da empresa numa sexta à noite.
Desta 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 um minuto e meio.
Nos próximos artigos, falarei sobre alguns outros casos de sucesso que tive usando colunas computadas, neste mesmo projeto.
Um abraço!
é DBA SQL SERVER SÊNIOR e atua no mercado a mais de 20 anos, sendo 10 com SQL Server e tem certificação MCDBA SQL 2000. Tem estudado muito o gerenciamento de múltiplos servidores SQL SERVER com o powershell e é fã de scripts e códigos.
