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,
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
Vamos ver como fica se eu montar esta consulta sem o upper:
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 ‘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
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 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)
include (nome)
go
update statistics TesteComputed with fullscanAgora 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)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!
De 0 a 10, o quanto você recomendaria este artigo para um amigo?