Frage Wie man große Daten der Tabelle in SQL ohne Protokoll löscht?


Ich habe eine große Datentabelle. In dieser Tabelle befinden sich 10 Millionen Datensätze.

Was ist der beste Weg für diese Abfrage?

   Delete LargeTable where readTime < dateadd(MONTH,-7,GETDATE())

75
2018-06-13 20:22


Ursprung


Antworten:


  1. Wenn Sie alle Zeilen in dieser Tabelle löschen, ist die einfachste Option, Tabelle abschneiden, etwas wie

    TRUNCATE TABLE LargeTable
    GO
    

    Die Tabelle truncate wird die Tabelle einfach leeren. Sie können die WHERE-Klausel nicht verwenden, um die zu löschenden Zeilen zu begrenzen, und es werden keine Trigger ausgelöst.

  2. Wenn Sie andererseits mehr als 80-90 Prozent der Daten löschen, sagen Sie, wenn Sie insgesamt 11 Millionen Zeilen haben und Sie 10 Millionen andere Zeilen löschen möchten, würden Sie diese 1 Million Zeilen einfügen (Datensätze, die Sie behalten möchten ) zu einer anderen Staging-Tabelle. Kürzen Sie diese große Tabelle und fügen Sie diese 1 Million Zeilen zurück.

  3. Oder wenn Berechtigungen / Sichten oder andere Objekte, die diese große Tabelle als ihre zugrunde liegende Tabelle hat, durch das Löschen dieser Tabelle nicht betroffen sind, können Sie diese relativ kleine Menge der Zeilen in eine andere Tabelle ablegen, diese Tabelle löschen und eine andere Tabelle mit demselben Schema erstellen und diese importieren Zeilen zurück in diese ex-Large-Tabelle.

  4. Eine letzte Option, die ich mir vorstellen kann, ist, die Datenbank zu ändern Recovery Mode to SIMPLE und dann Zeilen in kleineren Chargen mit einer while-Schleife etwas wie diese löschen ..

    DECLARE @Deleted_Rows INT;
    SET @Deleted_Rows = 1;
    
    
    WHILE (@Deleted_Rows > 0)
      BEGIN
       -- Delete some small number of rows at a time
         DELETE TOP (10000)  LargeTable 
         WHERE readTime < dateadd(MONTH,-7,GETDATE())
    
      SET @Deleted_Rows = @@ROWCOUNT;
    END
    

und vergessen Sie nicht, den Wiederherstellungsmodus wieder auf voll zu setzen, und ich denke, Sie müssen ein Backup machen, um es vollständig affektiv zu machen (den Änderungs- oder Wiederherstellungsmodus).


127
2018-06-13 20:50



@ m-ali Antwort ist richtig, aber auch daran erinnern, dass Protokolle können viel wachsen, wenn Sie nicht die Transaktion nach jedem Chunk fest und führen Sie einen Checkpoint. So würde ich es machen und diesen Artikel nehmen http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes als Referenz, mit Leistungstests und Grafiken:

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;


WHILE (@Deleted_Rows > 0)
  BEGIN

   BEGIN TRANSACTION

   -- Delete some small number of rows at a time
     DELETE TOP (10000)  LargeTable 
     WHERE readTime < dateadd(MONTH,-7,GETDATE())

     SET @Deleted_Rows = @@ROWCOUNT;

   COMMIT TRANSACTION
   CHECKPOINT -- for simple recovery model
END

50
2018-02-04 15:04



Sie können GO + auch so oft verwenden, wie Sie dieselbe Abfrage ausführen möchten.

DELETE TOP (10000)  [TARGETDATABASE].[SCHEMA].[TARGETTABLE] 
WHERE readTime < dateadd(MONTH,-1,GETDATE());
-- how many times you want the query to repeat
GO 100

25
2017-11-25 10:05



Diese Variation von M.AliEs funktioniert gut für mich. Es löscht einige, löscht das Protokoll und wiederholt. Ich beobachte das Protokoll wachsen, fallen und neu beginnen.

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;
WHILE (@Deleted_Rows > 0)
  BEGIN
   -- Delete some small number of rows at a time
    delete top (100000) from InstallLog where DateTime between '2014-12-01' and '2015-02-01'
    SET @Deleted_Rows = @@ROWCOUNT;
    dbcc shrinkfile (MobiControlDB_log,0,truncateonly);
END

8
2018-05-14 22:13



@Francisco Goldenstein, nur eine kleine Korrektur. Das COMMIT muss nach dem Setzen der Variablen verwendet werden, ansonsten wird WHILE nur einmal ausgeführt:

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;

WHILE (@Deleted_Rows > 0)
BEGIN
    BEGIN TRANSACTION

    -- Delete some small number of rows at a time
    DELETE TOP (10000)  LargeTable 
    WHERE readTime < dateadd(MONTH,-7,GETDATE())

    SET @Deleted_Rows = @@ROWCOUNT;

    COMMIT TRANSACTION
    CHECKPOINT -- for simple recovery model

END

7
2017-11-04 13:14



Wenn Sie Partitionen partitionieren möchten (und können), ist dies eine effektive Technik zum Entfernen großer Datenmengen mit geringem Laufzeitaufwand. Nicht kosteneffektiv für eine einmalige Übung.


5
2018-06-14 05:11



Ich war in der Lage, 19 Millionen Zeilen von meinem Tisch von 21 Millionen Zeilen in Minuten zu löschen. Hier ist meine Herangehensweise.

Wenn Sie eine haben Primärschlüssel automatisch inkrementieren In dieser Tabelle können Sie dann diesen Primärschlüssel verwenden.

  1. Ermitteln Sie den Mindestwert des Primärschlüssels der großen Tabelle, wobei readTime <dateadd (MONTH, -7, GETDATE ()). (Index zu readTime hinzufügen, falls nicht bereits vorhanden, wird dieser Index zusammen mit der Tabelle in Schritt 3 gelöscht.). Lässt es in einer Variablen 'min_primary' speichern

  2. Fügen Sie alle Zeilen mit Primärschlüssel> min_primary in eine Staging-Tabelle ein (Speichertabelle, wenn die Anzahl der Zeilen nicht groß ist).

  3. Lass den großen Tisch fallen.

  4. Erstellen Sie die Tabelle neu. Kopieren Sie alle Zeilen aus der Staging-Tabelle in die Haupttabelle.

  5. Löschen Sie die Staging-Tabelle.


3
2017-08-29 09:46



Sie können kleine Stapel mit einer while-Schleife löschen, etwa so:

DELETE TOP (10000)  LargeTable 
WHERE readTime < dateadd(MONTH,-7,GETDATE())
WHILE @@ROWCOUNT > 0
BEGIN
    DELETE TOP (10000)  LargeTable 
    WHERE readTime < dateadd(MONTH,-7,GETDATE())
END

2
2017-12-27 16:11



Eine andere Verwendung:

SET ROWCOUNT 1000 -- Buffer

DECLARE @DATE AS DATETIME = dateadd(MONTH,-7,GETDATE())

DELETE LargeTable  WHERE readTime < @DATE
WHILE @@ROWCOUNT > 0
BEGIN
   DELETE LargeTable  WHERE readTime < @DATE
END
SET ROWCOUNT 0

Wahlweise;

Wenn Transaktionsprotokoll aktiviert ist, deaktivieren Sie Transaktionsprotokolle.

ALTER DATABASE dbname SET RECOVERY SIMPLE;

1
2017-07-20 13:21



Kürzere Syntax

select 1
WHILE (@@ROWCOUNT > 0)
BEGIN
  DELETE TOP (10000) LargeTable 
  WHERE readTime < dateadd(MONTH,-7,GETDATE())
END

0
2018-03-15 11:57