SQL Server e Azure SQL: Como apagar ou atualizar dados em tabelas grandes
Visualizações: 1.732 views
Tempo de Leitura: 4minutos
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.
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.
SET@Msg=CONCAT('Processando dados no intervalo ',@LimiteInferior,'-',@LimiteSuperior,'...')
RAISERROR(@Msg,1,1)WITHNOWAIT
END
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.
SET@Msg=CONCAT('Processando dados no intervalo ',CONVERT(VARCHAR(10),@LimiteInferior,103),'-',CONVERT(VARCHAR(10),@LimiteSuperior,103),'...')
RAISERROR(@Msg,1,1)WITHNOWAIT
END
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
Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DECLARE
@MsgVARCHAR(MAX),
@Qt_LinhasINT
WHILE(1=1)
BEGIN
DELETETOP(10)PERCENT
FROMdbo.Tabela
WHERE[Status]=6
SET@Qt_Linhas=@@ROWCOUNT
IF(@Qt_Linhas=0)
BREAK
SET@Msg=CONCAT('Quantidade de Linhas Apagadas: ',@Qt_Linhas)
RAISERROR(@Msg,1,1)WITHNOWAIT
END
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
Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DECLARE
@MsgVARCHAR(MAX),
@Qt_LinhasINT
WHILE(1=1)
BEGIN
DELETETOP(100000)
FROMdbo.Tabela
WHERE[Status]=6
SET@Qt_Linhas=@@ROWCOUNT
IF(@Qt_Linhas=0)
BREAK
SET@Msg=CONCAT('Quantidade de Linhas Apagadas: ',@Qt_Linhas)
RAISERROR(@Msg,1,1)WITHNOWAIT
END
Resultado:
E é isso aí, pessoal!
Espero que tenham gostado e um grande abraço!
Boas dicas. Mas no caso do delete eu sempre uso o TRUNCATE TABLE. Já que vai deletar tudo mesmo! Inclsuive a perfomance é bem maior, utiliza menos recurso de log, não aciona trigger e ainda limpa todas as páginas da tabela.
Oi Colli, boa ideia, mas a minha ideia no post é pra expurgo de dados, onde não seriam todos os dados apagados, aí no truncate não iria funcionar.
Boas dicas. Mas no caso do delete para não perder tempo fazendo count, eu gosto de no while passar 1=2, e após o delete passar
if @@rowcount = 0 break
Os primeiros counts podem demorar muito caso a tabela seja muito grande, ou se tiver uma cláusula where para apagar apenas parte da tabela.
Boa ideia! Realmente fica mais rápido mesmo.