Tenho apresentado aqui na minha coluna vários aspectos sobre a exclusão de dados em bancos de dados relacionais. E conversando sobre este assunto com meu colega Livio Mondin, ele me sugeriu o tema para mais este artigo.
Expurgar a maior parte dos dados de uma tabela é uma operação que acontece com certa frequência e é comum termos problemas com estas operações, particularmente por causa da falta de um planejamento adequado.
A realidade é que uma operação de DELETE nem sempre é a melhor abordagem quando se exclui grandes volumes de dados. Isso depende do percentual de registros que será excluído da tabela. Um DELETE pode causar, por exemplo, o crescimento exagerado do arquivo de log e o aumento da fragmentação dos índices desta tabela.
Neste artigo eu apresento um comparativo entre a forma tradicional usando DELETE e outra abordagem que copia dados para uma tabela temporária e faz um TRUNCATE da tabela principal. O objetivo é verificar se esta abordagem alternativa tem vantagens sobre o DELETE e a partir de qual percentual de expurgo ela se torna vantajosa.
Solução sugerida
Como eu havia comentado, outra forma de lidar com expurgo é truncar a tabela e mover de volta apenas os dados desejados. O processo seguiria as seguintes etapas:
- Criamos uma tabela temporária;
- Movemos para a tabela temporária apenas os dados que serão mantidos na tabela principal;
- Desabilitamos as chaves estrangeiras (se houverem);
- Truncamos a tabela principal;
- Movemos de volta os dados da tabela temporária para a tabela principal;
- Reabilitamos as chaves estrangeiras (se houverem);
- Excluímos a tabela temporária.
Esta opção pode ser vantajosa, mas é importante avaliar quando usar esta alternativa.
Estudo de caso
Eu defini três cenários para comparar o DELETE simples com o método do TRUNCATE. Escolhi a tabela de inventário da base demo do SQL Server AdventureWorksDW2012 (tabela dbo.FactProductInventory), que possui mais de 770 mil registros e apenas um único índice, que é clusterizado e definido sobre a chave primária da tabela. A exclusão de dados foi feita com base na data de movimento da transação (campo MovementDate).
Os três cenários avaliados são os seguintes:
- Exclusão de 90% dos dados da tabela: data de corte 02/04/2007
- Exclusão de 70% dos dados da tabela: data de corte 14/12/2007
- Exclusão de 50% dos dados da tabela: data de corte 26/08/2008
Eu chamei a operação usando o DELETE de “Operação Tradicional” e a usando TRUNCATE de “Operação Sugerida”. A Listagem 1 mostra os scripts para estas duas operações:
Listagem 1: scripts SQL para os diferentes cenários e operações
declare @data_corte as date -- cenario 1: 90% --set @data_corte = '2008-08-26' -- cenario 2: 70% --set @data_corte = '2007-12-14' -- cenario 3: 50% --set @data_corte = '2007-04-02' -- Operacao Tradicional DELETE FROM dbo.FactProductInventory WHERE MovementDate < @data_corte -- Operacao Sugerida SELECT * INTO #tmpMovimentacao FROM dbo.FactProductInventory WHERE MovementDate >= @data_corte TRUNCATE TABLE dbo.FactProductInventory INSERT INTO dbo.FactProductInventory SELECT * FROM #tmpMovimentacao DROP TABLE #tmpMovimentacao
Em cada cenário, eu anotei resultados de seis indicadores:
- Tamanho do arquivo de log;
- Custo total reportado no plano de execução;
- Tempo total de execução;
- Fragmentação percentual da tabela (no caso, do índice clusterizado);
- Número total de fragmentos;
- Número total de páginas.
Uma vez que eu precisava garantir que os valores iniciais de todos os indicadores avaliados seriam idênticos antes de cada teste, eu mantive um backup do estado original da base e restaurei este backup após cada teste. O rollback da operação não resolveria, visto que ele não impediria que a transação desfeita fosse gravada no arquivo de log.
Para verificar o tamanho do log, eu usei a consulta da Listagem 2.
Listagem 2: script para identificar tamanho do arquivo de log
select type_desc , name, size * 8.0 / 1024 as sizeMb from AdventureWorksDW2012.sys.database_files
O plano de execução de cada teste e o tempo de execução da consulta também foram registrados. Para coletar dados de fragmentação da tabela e detalhes físicos do índice, eu usei a consulta da Listagem 3.
Listagem 3: script para verificar características físicas do índice da tabela
USE AdventureWorksDW2012
GO
EXEC sp_updatestats
USE master
GO
-- características físicas do índice
DECLARE @db_id SMALLINT
DECLARE @object_id INT
DECLARE @TABELA NVARCHAR(100)
SET @TABELA = 'dbo.FactProductInventory'
SET @db_id = DB_ID('AdventureWorksDW2012');
PRINT DB_NAME() + '.' + @TABELA
SET @object_id = OBJECT_ID(DB_NAME(@db_id) + '.' + @TABELA);
IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
SELECT *
FROM sys.dm_db_index_physical_stats
(@db_id, @object_id, NULL, NULL , 'DETAILED');
END;
Observe na Listagem 3 que estou usando a função de gerenciamento dinâmico sys.dm_db_index_physical_stats para identificar características físicas do índice da tabela. Como a função depende de estatísticas de uso do índice, é fundamental que elas sejam atualizadas após a exclusão de grandes volumes de dados (como é o caso aqui), do contrário a função trará informações inconsistentes! Por esta razão eu chamei o procedimento sp_updatestats no início do script.
Resultados
A Tabela 1 mostra os resultados obtidos para todos os seis indicadores nas duas operações (tradicional e sugerida) dentro dos três cenários definidos (50% de exclusão, 70% e 90%).
Os números da Tabela 1 são contundentes. O cenário 1, onde 50% dos dados são excluídos, obviamente é aquele onde se esperava os resultados menos positivos. Mesmo assim, quase todos os indicadores neste cenário são favoráveis à operação com TRUNCATE.
A exceção significativa é que o custo de execução da operação com TRUNCATE é quase quatro vezes maior do que o da operação com DELETE (287% maior). Porém o tempo de execução da consulta é ligeiramente menor (8%): crescimento do log é 38% menor e a fragmentação após a execução do expurgo é 99%. Portanto, eu considero que a operação com TRUNCATE foi vantajosa neste cenário (exclusão de 50% dos dados).
O cenário 2, onde 70% de dados excluídos, os resultados são ainda melhores. O custo de execução continua muito alto (78% maior do que a operação com DELETE), mas a operação com TRUNCATE é 25% mais rápida, muito provavelmente por não haver gravação de log. Todos os demais indicadores são muito favoráveis à operação com TRUNCATE, que novamente sai vencedora.
Quando se exclui 90% dos dados, caso do cenário 3, todos os indicadores mostram que a operação com TRUNCATE é vantajosa em relação à operação com DELETE. O arquivo de log é 92% menor, custo de execução 45% menor, tempo de execução 84% menor e fragmentação final de apenas 1% contra 73% no caso do DELETE.
Portanto, considerando todos os números observados nestes testes, em qualquer expurgo que envolva 50% ou mais dos dados da tabela, a operação sugerida com uso de TRUNCATE deve ser considerada.
Observe que eu deliberadamente escolhi uma tabela que não tivesse nenhum índice além do índice clusterizado para não favorecer nenhuma das duas operações.
Para quaisquer fins práticos, o “catálogo” do índice clusterizado é formado pelas próprias páginas de dados da tabela, que são organizadas conforme os valores deste índice. Porém as páginas de dados dos “catálogos” de quaisquer outros índices se baseiam nas páginas de dados da tabela. Sendo assim, o fato da tabela ser “limpada e reconstruída” no caso do TRUNCATE sugere que os índices não-clusterizados também serão reconstruídos e, portanto, terão fragmentação bem mais baixa do que teriam caso se executasse um DELETE.
Conclusão
Nos cenários que eu considerei neste estudo, a operação com TRUNCATE se mostrou muito melhor do que a operação com DELETE para qualquer expurgo envolvendo 50% dos dados ou mais.
Este resultado parece intuitivo, mas evidentemente ele é afetado por uma série de fatores, como tamanho da tabela, existência de um índice clusterizado, existência de outros índices, concorrência com outras transações, etc.
De qualquer modo, eu recomendo que você sempre avalie a operação com TRUNCATE quando planejar expurgo de grandes volumes de dados.
Até a próxima!
De 0 a 10, o quanto você recomendaria este artigo para um amigo?