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 =
BEGIN
    DELETE TOP (256)  FROM Projeto_Usuario
    IF @@ROWCOUNT = 0
        BREAK
END
               
                Achei que seria útil compartilhar.

                Have fun ;)

Comentários

  1. Pow Vitor, a matéria é antiga mas me quebrou um galhão. O esquema dos blocos de 256 caíram como uma luva pra mim.
    Obrigado por compartilhar.

    Abs.

    ResponderExcluir

Postar um comentário

Postagens mais visitadas deste blog

Detectar o encoding de um arquivo para não corromper ao transformá-lo

erro "ora-12154: tns: não foi possível resolver o identificador de conexão especificado"

Quebras de linha no Delphi 2010