web-development-kb-eu.site

Methoden zum Beschleunigen eines großen DELETE FROM <Tabelle> ohne Klauseln

Verwenden von SQL Server 2005.

Ich führe ein großes DELETE FROM ohne no-Klauseln durch. Es entspricht im Grunde einer TRUNCATE TABLE-Anweisung - außer dass ich TRUNCATE nicht verwenden darf. Das Problem ist, dass die Tabelle riesig ist - 10 Millionen Zeilen, und die Fertigstellung dauert über eine Stunde. Gibt es eine Möglichkeit, es schneller zu machen, ohne:

  • Verwenden von Abschneiden
  • Indizes deaktivieren oder löschen?

Das T-Log befindet sich bereits auf einer separaten Festplatte.

Anregungen willkommen!

sql-serverperformancedelete
37
tuseau

Was Sie tun können, ist Batch-Löschungen wie folgt:

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

Wo xxx ist, sagen wir 50000

Eine Änderung davon, wenn Sie einen sehr hohen Prozentsatz von Zeilen entfernen möchten ...

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
39
gbn

Sie können die TOP-Klausel verwenden, um dies einfach zu erledigen:

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

Ich bin mit den Vorschlägen einverstanden, Ihre Löschvorgänge in verwaltbare Blöcke zu stapeln, wenn Sie TRUNCATE nicht verwenden können, und ich mag den Drop/Create-Vorschlag für seine Originalität, aber ich bin gespannt auf den folgenden Kommentar in Ihrer Frage:

Es entspricht im Grunde einer TRUNCATE TABLE-Anweisung - außer ich darf TRUNCATE nicht verwenden

Ich vermute, der Grund für diese Einschränkung hängt mit der Sicherheit zusammen, die gewährt werden muss, um eine Tabelle direkt abzuschneiden, und mit der Tatsache, dass Sie damit andere als die von Ihnen betroffene Tabellen abschneiden können.

Unter der Annahme, dass dies der Fall ist, frage ich mich, ob die Erstellung einer gespeicherten Prozedur, die TRUNCATE TABLE verwendet und "EXECUTE AS" verwendet, als praktikable Alternative zur Gewährung von Sicherheitsrechten angesehen wird, die zum direkten Abschneiden der Tabelle erforderlich sind.

Dies gibt Ihnen hoffentlich die Geschwindigkeit, die Sie benötigen, und berücksichtigt gleichzeitig die Sicherheitsbedenken, die Ihr Unternehmen möglicherweise hat, wenn Sie Ihr Konto zur Rolle db_ddladmin hinzufügen.

Ein weiterer Vorteil der Verwendung einer gespeicherten Prozedur auf diese Weise besteht darin, dass die gespeicherte Prozedur selbst gesperrt werden kann, sodass nur bestimmte Konten sie verwenden dürfen.

Wenn dies aus irgendeinem Grund keine akzeptable Lösung ist und Sie die Daten in dieser Tabelle entfernen müssen, müssen Sie einmal am Tag/in der Stunde/usw. einen SQL Agent-Job erstellen, um die Tabelle abzuschneiden jeden Tag zu einer festgelegten Zeit.

Hoffe das hilft!

7
Jeff

Außer abschneiden .. nur das Löschen in Stapeln kann Ihnen helfen.

Sie können die Tabelle löschen und natürlich mit allen Einschränkungen und Indizes neu erstellen. In Management Studio haben Sie die Möglichkeit, ein Skript zum Löschen und Erstellen einer Tabelle zu erstellen. Dies sollte daher eine triviale Option sein. Dies ist jedoch nur möglich, wenn Sie DDL-Aktionen ausführen dürfen. Ich sehe, dass dies keine Option ist.

5
Marian

Da diese Frage eine so wichtige Referenz ist, veröffentliche ich diesen Code, der mir wirklich geholfen hat, das Löschen mit Schleifen und auch das Versenden von Nachrichten innerhalb einer Schleife zu verstehen, um den Fortschritt zu verfolgen.

Die Abfrage wurde geändert von this doppelte Frage. Gutschrift an @ RLF für die Abfragebasis.

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 = @[email protected]; -- 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;
1
Max xaM