Bom, com a situação acima um 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 (agendada para execução 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!
Para vocês entenderem melhor a solução implementada, 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 usando uma CTE
;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)
Abaixo pode-see ver o plano de execução no modo texto
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 terá o UPPER do campo nome.
Alter table TesteComputed add UpperNome as UPPER(nome)
Crie o indice neste campo calculado 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, rodando a mesma consulta temos o seguinte…
(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.
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, reduzindo o tempo da consulta para 1 minuto e meio.
Em breve estarei postando sobre alguns outros casos de sucesso que tive usando colunas computadas, neste mesmo projeto.
Um abraço !!!!
Laerte Poltronieri Junior
$hell Your Experience !!!
www.laertejuniordba.spaces.live.com