Lentidão ao executar Delete

Estava executando uma procedure pra atualizar os relacionamentos entre projetos e funcionários do meu sistema e a procedure estava demorando mais de um minuto para executar. Abri o fonte da procedure e executei os comandos dela internamente, e todos eles demoravam menos de um segundo para executar, exceto um delete que demorava mais de um minuto.

                O comando delete from Projeto_Usuario estava demorando mais de um minuto para executar, mas apenas 7000 linhas não deveria demorar tudo isso. (isso causa timeout na maioria das aplicações porque o timeout default de um comando é 30 segundos, e o de uma conexão é de 15 segundos). O mais correto a se fazer não é aumentar esse timeout, e sim otimizar a query.

               

                A tabela Projeto_Usuario tem uma chave primária composta pelos campos IDprojeto e IDusuario. Como esses dois campos são PK, eles formam um índice clustered (significa que é um índice físico, que opera diretamente I/O no disco).

                Índices tem um papel fundamental em, se bem construídos, aumentar a velocidade de um select. Porém isso tem um preço. Tanto índices clustered como nonclustered degradam um pouco a performance durante inserts, updates e deletes, pois nessas operações eles precisam ser reconstruídos ou reorganizados. Os índices clustered, como das pk’s, são 10x mais lentos que os nonclustered. Essa lentidão é imperceptível ao se deletar um único registro, mas uma operação de delete massivo de 7000 registros percebe-se o custo.

Para cada tupla deletada, não executadas n+1 operações de delete, sendo n o número de índices que a tabela tem. Ou seja, a informação é primeiro deletada dos índices para depois ser deletada da tabela. 

                Solução para fazer delete from Projeto_Usuario mais rápido:

1)      Remover a pk, deletar e recolocar a pk è embora funcione eu detesto essa abordagem porque é um trabalho burro, trabalhoso e sujeito a erro ou esquecimento.

2)      Comando truncate è truncate table Projeto_Usuario  meu 2° preferido, não demora nada, mas não cria log (o que numa operação massiva não interessa) e eu não sei quanto aos índices, mas o truncate deve largar a bagunça pra alguém limpar mais tarde. (no SQL Server 2008 dá para fazer rollback de uma operação de truncate, nos outros eu não sei)

3)      Apagar a tabela em pedacinhos de 256 registro cada è talvez por questões de cache de IO, memória ou swap apagar em pedacinhos de 256 demorou menos de um segundo, em 19 pedacinhos. Esse 256 arbitrário pode ser mudado conforme a configuração do servidor. Um benchmark deve ser feito.

WHILE 1 = 1 

BEGIN

    DELETE TOP (256)  FROM Projeto_Usuario

    IF @@ROWCOUNT = 0

        BREAK

END

               

                Achei que seria útil compartilhar.

                Have fun ;)