Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server e Azure SQL: Como apagar ou atualizar dados em tabelas grandes

Visualizações: 1.274 views
Tempo de Leitura: 4 minutos

IntroduĂ§Ă£o

Fala pessoal!
Nesse artigo eu gostaria de compartilhar com vocĂªs um pequeno cĂ³digo que eu precisei utilizar hoje para fazer um UPDATE numa tabela relativamente grande (55M+ de registros) numa base Azure SQL Database e, depois de 1h e 30 mins esperando, deu erro de conexĂ£o e tive que fazer tudo novamente.

NĂ£o apenas nesse caso, mas pode acontecer de estourar o log e dar erro na operaĂ§Ă£o tambĂ©m, e, como sabemos, quando ocorre um erro durante UPDATE ou DELETE, o rollback automĂ¡tico Ă© iniciado e nenhuma linha Ă© alterada de fato. Quebrando essa operaĂ§Ă£o Ăºnica em operações menores e segmentadas vai permitir que o log seja liberado e assim, minimize possĂ­veis estouros de log.

VocĂª tambĂ©m pode querer quebrar essas operações grandes e demoradas em pequenas partes para conseguir acompanhar o progresso ou a sua janela de manutenĂ§Ă£o nĂ£o Ă© suficiente para processar o UPDATE/DELETE em todas as linhas necessĂ¡rias e vocĂª quer continuar em outra janela.

JĂ¡ vi casos tambĂ©m de tabelas com triggers e que podem acabar gerando um overhead muito grande ao executar um UPDATE/DELETE que altere muitas linhas. E temos que lembrar tambĂ©m de locks na tabela, que ao quebrar em partes menores, podem ser liberados rapidamente enquanto o prĂ³ximo lote estĂ¡ iniciando o processamento.

SĂ£o vĂ¡rios os motivos que podem influenciar na decisĂ£o de participar um UPDATE/DELETE grande e nesse artigo vou mostrar algumas formas fĂ¡ceis de fazer isso.

Importante: Caso vocĂª queira apenas apagar ou alterar os primeiros ou os Ăºltimos registros de uma tabela, vocĂª pode usar a soluĂ§Ă£o que compartilhei no artigo SQL Server – Como realizar UPDATE e DELETE com TOP x registros.

Como apagar ou atualizar dados em tabelas grandes

UPDATE particionado por campo inteiro
No exemplo abaixo, eu estou utilizando uma coluna autoincremento do tipo inteiro para montar os intervalos dos valores que vou atualizar. A variĂ¡vel @Aumento define a quantidade de linhas de cada lote que serĂ£o atualizadas e eu defini nesse exemplo que a quantidade de linhas atualizadas por vez serĂ¡ de 1 milhĂ£o de linhas.

UPDATE particionado por campo inteiro

Resultado:

UPDATE particionado por campo data
No exemplo abaixo, eu estou utilizando uma coluna de data para montar os intervalos dos valores que vou atualizar. A variĂ¡vel @Aumento define a quantidade de dias de cada lote que serĂ£o atualizadas e eu defini nesse exemplo que essa quantidade de dias que serĂ£o atualizados por cada bloco serĂ¡ de 30 dias.

UPDATE particionado por campo data

Resultado:

DELETE TOP(N) particionado usando porcentagem
No exemplo abaixo, eu estou apagando 10% da minha tabela a cada iteraĂ§Ă£o. Como os dados sĂ£o apagados, nĂ£o preciso controlar intervalos, apenas vou apagando de 10 em 10%.

DELETE TOP(N) particionado usando porcentagem

Resultado:

ObservaĂ§Ă£o: Essa soluĂ§Ă£o pode apresentar problemas em tabelas muito grandes, pois 10% pode representar um volume muito grande de linhas. E quando poucos registros vĂ£o restando, os 10% podem exigir muitas iterações para serem apagados.

DELETE TOP(N) particionado usando quantidade de linhas
No exemplo abaixo, eu estou apagando 500 mil linhas da minha tabela a cada iteraĂ§Ă£o. Como os dados sĂ£o apagados, nĂ£o preciso controlar intervalos, apenas vou apagando de 500 em 500 mil linhas atĂ© nĂ£o restarem mais linhas que atendam aos critĂ©rios dos filtros.

DELETE TOP(N) particionado usando quantidade de linhas

Resultado:

E Ă© isso aĂ­, pessoal!
Espero que tenham gostado e um grande abraço!