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.732 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!