Adicionar um índice para ajudar nos updates e deletes pode não ajudar

Com o tempo vêm-se coisas muito giras e assunções muito elaboradas. Já me ouviram dizer que a Assunção é a mãe de todas as trapalhadas?

Temos um “delete que demora muito tempo”. Normalmente é um delete “à bruta”, todo feito de uma vez em que vemos o nosso log a crescer até ao infinito e mais além. Ou temos um update a uma certa coluna e resolvemos indexá-la para que essa operação fique mais rápida.

Cada caso é um caso e há casos em que realmente ajuda. Mas nem sempre.

Como sempre, “todos temos direito às nossas opiniões mas não aos nossos factos”. Vamos lá testar isto.

create table DeleteDemo 
(
		id int identity(1,1)
	,	valor bigint
)
go
insert into DeleteDemo
select ABS(CHECKSUM(NEWID())%1000)
from sys.objects a

set statistics time, io on

select *
from DeleteDemo
where valor  > 500

Nem pediu índice… porque será?

Vamos lá a ver com full optimization

select *
from DeleteDemo
where 1 = (select 1)
	and valor  > 500
	

Ainda nada. Também com tão poucas linhas só temos de ir buscar uma página.

Vamos lá inserir um pouco mais de linhas.

insert into DeleteDemo
select ABS(CHECKSUM(NEWID())%1000)
from sys.objects a
cross join sys.objects b

para vermos com e sem trivial optimisation

select *
from DeleteDemo
where valor  > 500


select *
from DeleteDemo
where 1 = (select 1)
	and valor  > 500

Agora já nos pede um índice.
Vamos manter isso na ideia, ou melhor ainda, criá-lo sem demoras.

CREATE NONCLUSTERED INDEX nci_deletedemo_valor
ON [dbo].[DeleteDemo] ([valor])
INCLUDE ([id])


select *
from DeleteDemo
where 1 = (select 1)
	and valor  > 1000000

Agora tenho o desejado seek, com menos reads.

Este índice vai-me ajudar imenso se eu quiser fazer, por exemplo, um update à coluna valor…

Para testarmos isso vamos criar outra tabela, exactamente igual mas sem índice.

select *
into DeleteDemoSemIndex
from DeleteDemo

E agora vamos fazer os nossos updates nas duas tabelas. Na com índice vai correr muito melhor…

update DeleteDemo
set valor = valor*2
where valor  > 500


update DeleteDemoSemIndex
set valor = valor*2
where valor  > 500

Como é que correu?

Definitivamente não correu melhor. Mais reads apesar de usar um índice para limitar as linhas retiradas da tabela porque precisou de um table spool para evitar um problema conhecido por Halloween Effect ou Halloween Problem.

É simples de perceber que temos um índice que estamos a percorrer e a alterar ao mesmo tempo. Bem, pensaríamos nós que seria ao mesmo tempo, mas nota-se bem que com o table spool estamos a colocar um passo intermédio (visível também na Worktable que nos aparece no IO).

No caso em que não temos índice o SQL Server não parece preocupar-se com isso. Simplesmente não é problema, porque não tem índice nenhum em cima da coluna que está a ser alterada. Depois de passar por aquela linha não a vai ver mais.

No caso da coluna indexada é fácil perceber que se temos a coluna ordenada ascendentemente e multiplicarmos um valor por 2 ele acaba por “ir mais para a frente” no índice e depois acabaríamos por multiplicar esse valor de novo por 2.

E se fosse um delete?

delete 
from DeleteDemo
where valor  > 500


delete 
from DeleteDemoSemIndex
where valor  > 500

E se fosse um update colocando os valores “para trás” no índice?

update DeleteDemo
set valor = valor/3
where valor  > 200


update DeleteDemoSemIndex
set valor = valor/3
where valor  > 200

Não temos grande sorte também.

Então vamos “criar um índice para ajudar”. Só pode ajudar, não é?

Não é.