terça-feira, 19 de junho de 2012

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 ;)

Postagens populares

Marcadores

delphi (60) C# (31) poo (21) Lazarus (19) Site aos Pedaços (15) sql (13) Reflexões (10) .Net (9) Humor (9) javascript (9) ASp.Net (8) api (8) Básico (6) Programação (6) ms sql server (5) Web (4) banco de dados (4) HTML (3) PHP (3) Python (3) design patterns (3) jQuery (3) livros (3) metaprogramação (3) Ajax (2) Debug (2) Dicas Básicas Windows (2) Pascal (2) games (2) linguagem (2) música (2) singleton (2) tecnologia (2) Anime (1) Api do Windows (1) Assembly (1) Eventos (1) Experts (1) GNU (1) Inglês (1) JSON (1) SO (1) datas (1) developers (1) dicas (1) easter egg (1) firebird (1) interfaces (1) introspecção (1) memo (1) oracle (1) reflexão (1)