Metody przyspieszenia ogromnego USUŃ Z <tabela> bez klauzul

Korzystanie z SQL Server 2005.

Wykonuję ogromną operację DELETE FROM bez klauzul where. Jest to w zasadzie odpowiednik instrukcji TRUNCATE TABLE - z wyjątkiem tego, że nie wolno mi używać TRUNCATE. Problem polega na tym, że tabela jest ogromna - 10 milionów wierszy i jej ukończenie zajmuje ponad godzinę. Czy jest jakiś sposób na przyspieszenie go bez:

• Używanie obcięcia
• Wyłączać lub upuszczać indeksy?

Dziennik T znajduje się już na osobnym dysku.

Wszelkie sugestie mile widziane!

Co możesz zrobić, to wsadowe usuwanie w ten sposób:

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

Gdzie xxx to, powiedzmy, 50000

Modyfikacja tego, jeśli chcesz usunąć bardzo wysoki odsetek wierszy ...

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

Możesz użyć klauzuli TOP, aby łatwo to zrobić:

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

Zgadzam się z sugestiami podzielenia twoich usunięć na porcje do zarządzania, jeśli nie możesz użyć TRUNCATE, i podoba mi się sugestia upuszczenia / stworzenia ze względu na oryginalność, ale jestem ciekawa następującego komentarza w twoim pytaniu:

Jest to w zasadzie odpowiednik instrukcji TRUNCATE TABLE - z wyjątkiem tego, że nie wolno mi używać TRUNCATE

Zgaduję, że powodem tego ograniczenia jest bezpieczeństwo, które należy przyznać, aby bezpośrednio obciąć tabelę oraz fakt, że pozwoliłoby to na obcięcie tabel innych niż ten, którego dotyczy.

Zakładając, że tak jest, zastanawiam się, czy utworzenie procedury składowanej, która korzysta z TRUNCATE TABLE i używa „EXECUTE AS”, byłoby uważane za realną alternatywę dla nadania praw bezpieczeństwa niezbędnych do bezpośredniego obcinania tabeli.

Mamy nadzieję, że dzięki temu uzyskasz szybkość, której potrzebujesz, a jednocześnie rozwiążesz obawy związane z bezpieczeństwem, jakie może mieć Twoja firma, dodając konto do roli db_ddladmin.

Kolejną zaletą korzystania z procedury składowanej w ten sposób jest to, że sama procedura przechowywana może zostać zablokowana, aby mogły z niej korzystać tylko określone konta.

Jeśli z jakiegoś powodu nie jest to akceptowalne rozwiązanie i potrzeba usunięcia danych z tej tabeli jest czymś, co należy zrobić raz dziennie / godzinę / itp., Poprosiłbym o utworzenie zadania agenta SQL w celu obcięcia tabeli o zaplanowanej godzinie każdego dnia.

Mam nadzieję że to pomoże!

Z wyjątkiem obcinania ... tylko usuwanie partiami może ci pomóc.

Możesz upuścić tabelę i odtworzyć ją ze wszystkimi ograniczeniami i indeksami, oczywiście. W Management Studio masz opcję skryptu tabeli, aby upuścić i utworzyć, więc powinna to być trywialna opcja. Ale to tylko wtedy, gdy wolno ci wykonywać akcje DDL, co, jak widzę, nie jest tak naprawdę opcją.

Ponieważ to pytanie jest tak ważnym odniesieniem, zamieszczam ten kod, który naprawdę pomógł mi zrozumieć usuwanie za pomocą pętli, a także przesyłanie wiadomości w pętli w celu śledzenia postępu.

Zapytanie zostało zmodyfikowane na podstawie tego duplikatu pytania. Kredyt @RLF dla bazy zapytań.

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;