Exclusão ou Atualização de registros em lote

Neste artigo o dataholic Felipe Aguiar compartilha seus conhecimentos para facilitar os processos ao se trabalhar com uma grande quantidade de registros. Vamos conferir?

Olá comunidade! Quero trazer para vocês neste artigo, uma procedure que ficará encarregada de realizar DELETE ou UPDATE por LOTE.

Por exemplo, poderemos definir para que a procedure exclua 10.000 linhas por vez.

Isso se torna muito eficaz quando existe uma tabela com grande quantidade de registros a serem excluídos ou atualizados, e caso fossem excluídos ou atualizados de uma só vez, teríamos uma transação aberta por mais tempo, ocasionando um longo LOCK na tabela em questão.

Realizando esta tarefa em LOTES, evitamos que outros processos fiquem aguardando por longos períodos até que a exclusão ou atualização completa seja finalizada, uma vez que, após o processamento de UM LOTE, o SQL vai retirar o lock da tabela, permitindo que outros processos acessem essa tabela.



Sumário

  1. Vamos ao que interessa

  2. Colocando em prática

  3. Consideração final

Vamos ao que interessa

  1. Como utilizar a procedure

Temos os seguintes parâmetros de entrada nesta procedure:

  1. @COMANDO

  2. Informar o tipo de procedimento a ser realizado, sendo UPDATE / DELETE

  3. @tabela_UPDATE_OR_DELETE_LOTE

  4. Deverá ser informado o NOME da tabela que você deseja realizar a EXCLUSÃO ou ATUALIZAÇÃO das linhas

  5. @tabela_aux

  6. Aqui deve ficar o NOME da tabela auxiliar, ou seja, uma tabela em que você tenha os “IDs” (primary key – em caso de tabela clustered) da tabela que deseja realizar o DELETE ou UPDATE

  7. @colunas_chave

  8. Esse campo, deverá conter o nome da coluna primary key da tabela que terá as linhas excluídas ou atualizadas, caso esta seja uma tabela clustered.

  9. Em caso de tabela heap (sem primary key), informar o NOME da coluna que você está considerando como uma chave, ou seja, a ligação entre a @tabela_UPDATE_OR_DELETE_LOTE e a @tabela_aux

  10. @colunas_update

  11. Deixar vazio em caso de @comando = ‘delete’

  12. Informar o NOME das COLUNAS que serão atualizadas (a @tabela_aux deverá ter o mesmo nome da coluna da @tabela_UPDATE_OR_DELETE_LOTE )

  13. @backup

  14. Deixar vazio em caso de @comando = ‘delete’

  15. Preencher com 1 caso você queira que a procedure crie uma tabela de backup, contendo a coluna chave e os valores que foram atualizados com ANTES / DEPOIS

  16. @lote

  17. Informar a quantidade de linhas que serão processadas por vez

  18. @executar

  19. Aqui temos 2 opções:

  20. Se informado valor 0 (zero) será retornado para o usuário, o script final para que seja executado de forma manual

  21. Se informado o valor 1, a PROCEDURE já começará a realizar o processamento dos lotes conforme configurado

Colocando em prática

  1. Estou criando duas tabelas, chamadas [tabela1] e [tabela1_aux]. A [tabela1_aux] será uma “Cópia” da [tabela1], ou seja, contendo todas as primary_keys da [tabela1]. Para o nosso teste, eu estou querendo excluir todas as 10.000 linhas da [tabela1]

  1. Vamos informar os parâmetros da procedure de acordo, para este teste, especifiquei o lote de 1000 (Mil) linhas.

  1. Após a execução da procedure, temos o Result set com a informação de sucesso no procedimento, assim como um resumo abaixo do que foi executado e o tempo de execução. Na coluna [QUERY], temos exatamente os scripts que foram executados pela procedure

  1. Já na aba Messages, temos o tempo de exclusão de cada lote, assim como a quantidade de registros restantes, essa informação é bastante útil em caso de exclusão de grande volume de linhas

  1. Como resultado temos a tabela1 com todos os registros excluídos, e a tabela1_aux com todos os registros, essa tabela em questão, poderá ser excluída manualmente após a realização do procedimento, para liberação de espaço, caso não haja necessidade de mantê-la.

Passando o valor 0 no parâmetro @executar:

  1. Será exibido no Result a coluna QUERY. Deveram ser copiadas todas as linhas para realização do processo

  1. Depois de copiadas as queries e executadas, teremos como retorno o script final de exclusão por lote

  1. Depois de colado em uma nova aba, e executado, temos a exclusão com sucesso dos registros

  1. Vamos realizar agora um teste com o comando de UPDATE?

Vou considerar as mesmas tabelas do teste anterior:

– Vamos atualizar as colunas [VALOR] e [codigo]

– Então, definido os parâmetros desta forma:

– Update realizado com sucesso e resumo exibido.

Podemos ainda consultar a tabela de backup:

  1. Procedure disponível em: https://github.com/aguiarfpmr/scripts_publicos/blob/main/UPDATE_OR_DELETE_LOTE.sql

Consideração final

Este script já foi testado em diversos ambientes, entretanto, é de inteira responsabilidade do DBA a utilização desta procedure e de realizar testes antes, em um ambiente controlado.

Gostou desse conteúdo? Curta, comente e compartilhe. 💙