Início / dba / Perguntas / 1750
Accepted
tuseau
Asked: 2011-03-16 05:53:12 +0800 CST

Métodos para acelerar um enorme DELETE FROM <table> sem cláusulas

  • 772

Usando SQL Server 2005.

Estou executando um DELETE FROM enorme sem cláusulas where. É basicamente equivalente a uma instrução TRUNCATE TABLE - exceto que não tenho permissão para usar TRUNCATE. O problema é que a tabela é enorme - 10 milhões de linhas e leva mais de uma hora para ser concluída. Existe alguma maneira de torná-lo mais rápido sem:

  • Usando Truncar
  • Desativando ou descartando índices?

O t-log já está em um disco separado.

Qualquer sugestão é bem-vinda!

sql-server performance

5 respostas

  1. Best Answer

    O que você pode fazer é excluir em lote assim:

    SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
    DELETE TOP (xxx) MyTable

    Onde xxx é, digamos, 50.000

    Uma modificação disso, se você deseja remover uma porcentagem muito alta de linhas ...

    SELECT col1, col2, ... INTO #Holdingtable
           FROM MyTable WHERE ..some condition..

SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
    DELETE TOP (xxx) MyTable WHERE ...

INSERT MyTable (col1, col2, ...)
           SELECT col1, col2, ... FROM #Holdingtable
    • 41

  2. Você pode usar a cláusula TOP para fazer isso facilmente:

    WHILE (1=1)
BEGIN
    DELETE TOP(1000) FROM table
    IF @@ROWCOUNT < 1 BREAK
END
    • 22

  3. Concordo com as sugestões de agrupar suas exclusões em partes gerenciáveis ​​se você não puder usar TRUNCATE e gosto da sugestão de soltar/criar por sua originalidade, mas estou curioso sobre o seguinte comentário em sua pergunta:

    É basicamente equivalente a uma instrução TRUNCATE TABLE - exceto que não tenho permissão para usar TRUNCATE

    Acho que o motivo dessa restrição tem a ver com a segurança que precisa ser concedida para truncar diretamente uma tabela e o fato de permitir truncar tabelas diferentes daquela com a qual você está preocupado.

    Supondo que seja esse o caso, estou querendo saber se ter um procedimento armazenado criado que usa TRUNCATE TABLE e usa "EXECUTE AS" seria considerado uma alternativa viável para conceder direitos de segurança necessários para truncar a tabela diretamente.

    Felizmente, isso lhe daria a velocidade de que você precisa, além de abordar as preocupações de segurança que sua empresa pode ter ao adicionar sua conta à função db_ddladmin.

    Outra vantagem de usar um procedimento armazenado dessa maneira é que o próprio procedimento armazenado pode ser bloqueado para que apenas contas específicas tenham permissão para usá-lo.

    Se por algum motivo esta não for uma solução aceitável e sua necessidade de remover os dados desta tabela for algo que precisa ser feito uma vez por dia/hora/etc, eu solicitaria que um trabalho do SQL Agent fosse criado para truncar a tabela em um horário agendado todos os dias.

    Espero que isto ajude!

    • 7

  4. Exceto truncar.. apenas excluir em lotes pode ajudá-lo.

    Você pode descartar a tabela e recriá-la, com todas as restrições e índices, é claro. No Management Studio, você tem a opção de criar um script de uma tabela para descartar e criar, portanto, deve ser uma opção trivial. Mas isso apenas se você tiver permissão para fazer ações DDL, o que vejo que não é realmente uma opção.

    • 5

  5. Como essa pergunta é uma referência tão importante, estou postando este código que realmente me ajudou a entender a exclusão com loops e também as mensagens dentro de um loop para acompanhar o progresso.

    A consulta é modificada a partir desta pergunta duplicada. Crédito para @RLF para a base de consulta.

    CREATE TABLE #DelTest (ID INT IDENTITY, name NVARCHAR(128)); -- Build the test table
INSERT INTO #DelTest (name) SELECT name FROM sys.objects;  -- fill from system DB
SELECT COUNT(*) TableNamesContainingSys FROM #deltest WHERE name LIKE '%sys%'; -- check rowcount
go
DECLARE @HowMany INT;
DECLARE @RowsTouched INT;
DECLARE @TotalRowCount INT;
DECLARE @msg VARCHAR(100);
DECLARE @starttime DATETIME 
DECLARE @currenttime DATETIME 

SET @RowsTouched = 1; -- Needs to be >0 for loop to start
SET @TotalRowCount=0  -- Total rows deleted so far is 0
SET @HowMany = 5;     -- Variable to choose how many rows to delete per loop
SET @starttime=GETDATE()

WHILE @RowsTouched > 0
BEGIN
   DELETE TOP (@HowMany)
   FROM #DelTest 
   WHERE name LIKE '%sys%';

   SET @RowsTouched = @@ROWCOUNT; -- Rows deleted this loop
   SET @TotalRowCount = @TotalRowCount+@RowsTouched; -- Increment Total rows deleted count
   SET @currenttime = GETDATE();
   SELECT @msg='Deleted ' + CONVERT(VARCHAR(9),@TotalRowCount) + ' Records. Runtime so far is '+CONVERT(VARCHAR(30),DATEDIFF(MILLISECOND,@starttime,@currenttime))+' milliseconds.'
   RAISERROR(@msg, 0, 1) WITH NOWAIT;  -- Print message after every loop. Can't use the PRINT function as SQL buffers output in loops.  

END; 
SELECT COUNT(*) TableNamesContainingSys FROM #DelTest WHERE name LIKE '%sys%'; -- Check row count after loop finish
DROP TABLE #DelTest;
    • 3

relate perguntas