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
Vamos ao que interessa
Como utilizar a procedure
Temos os seguintes parâmetros de entrada nesta procedure:
@COMANDO
Informar o tipo de procedimento a ser realizado, sendo UPDATE / DELETE
@tabela_UPDATE_OR_DELETE_LOTE
Deverá ser informado o NOME da tabela que você deseja realizar a EXCLUSÃO ou ATUALIZAÇÃO das linhas
@tabela_aux
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
@colunas_chave
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.
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
@colunas_update
Deixar vazio em caso de @comando = ‘delete’
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 )
@backup
Deixar vazio em caso de @comando = ‘delete’
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
@lote
Informar a quantidade de linhas que serão processadas por vez
@executar
Aqui temos 2 opções:
Se informado valor 0 (zero) será retornado para o usuário, o script final para que seja executado de forma manual
Se informado o valor 1, a PROCEDURE já começará a realizar o processamento dos lotes conforme configurado
Colocando em prática
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]
Vamos informar os parâmetros da procedure de acordo, para este teste, especifiquei o lote de 1000 (Mil) linhas.
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
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
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:
Será exibido no Result a coluna QUERY. Deveram ser copiadas todas as linhas para realização do processo
Depois de copiadas as queries e executadas, teremos como retorno o script final de exclusão por lote
Depois de colado em uma nova aba, e executado, temos a exclusão com sucesso dos registros
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:
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. 💙