SQL Server e Azure SQL: Como apagar ou atualizar dados em tabelas grandes
Visualizações: 1.274 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.
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
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.