Otimizando DELETE TOP 1000

No último post, comentei do desafio do DELETE de 1000 linhas, que consistia em resolver um problema bastante comum:

Problema: Apagar os 1000 registros mais antigos da tabela.

A fim de tornar a situação mais real, criei uma tabela e inseri 1 milhão de registros.

Frequentemente, vejo que os programadores executam o seguinte SELECT para eliminar as linhas:

Existe uma incoerência nesse comando, que fica visível ao expor o plano de execução.

Note que estamos acessando a tabela DUAS vezes e, em ambas ocasiões, são leituras para identificar quais os registros que devem ser apagados. O ideal seria minimizar o acesso à tabela durante o comando de DELETE.

Rodando a consulta o resultado não foi ruim: apenas 374 milissegundos de execução.

 

Resolvendo o problema

Antes de mostrar a solução, vamos começar com um problema mais simples:

Problema: Identificar os 1000 registros mais antigos da tabela

Assim, temos o comando e seu plano de execução:

Note que, fazemos um único Table Scan na tabela de Logs (bom sinal). Criamos uma VIEW para essa visualização:

Acredite se quiser, mas a solução é tão simples quanto um DELETE sem WHERE.

O plano de execução é semelhante ao SELECT:

O passo final é criar um índice para suportar a consulta. No caso, podemos evitar a operação de Sort (responsável por 88% do custo) através de um índice clustered sobre a coluna “dtHorario”.

O plano de execução agora ficou perfeito: uma única leitura na tabela, não há operação de HASH ou SORT, sem paralelismo.

 

Comparação

Como ficou o tempo após a otimização? Usando os comandos SET STATISTICS TIME e SET STATISTICS IO, observamos o seguinte resultado: remover os 1000 registros forçou um scan completo na tabela tbLogs, gerou uma tabela temporária para SORT e consumiu 1812ms de CPU. Graças ao paralelismo de execução, foi possível concluir o comando em apenas 374ms.

Por outro lado, após criar o índice clustered e reescrever a consulta, temos o seguinte resultado:

Antes de concluir, precisamos falar sobre o custo das queries.

ANTES:

DEPOIS:

 

Conclusão

A criação do índice certo é fundamental para garantir um bom desempenho de banco de dados. Entretanto, em muitos casos, observam-se construções INCORRETAS e que devem ser reescritas. Nesse artigo, demonstrei um dos erros mais comuns encontrados nos sistemas: acessar a tabela duas vezes.

A forma mais fácil de resolver esse tipo de problema é usar Views ou CTE. Nesse problema poderia ter reescrito a query da seguinte forma:

No próximo artigo, vamos fazer um outro desafio. Não perca!