Skip to content

Tipps & Tricks: Performantes Löschen von großen Datenmengen

2. September 2013

Sicherlich stand fast jeder schon mal vor der Aufgabe, anhand einer vorgegebenen Bedingung Zeilen aus einer Tabelle zu löschen. Problematisch wird es aber dann, wenn die Tabelle sehr groß ist und die Löschbedingung auf ein Großteil der Zeilen zutrifft. Wenn zum Beispiel aus einer Log-Tabelle alle Einträge entfernt werden sollen, die nicht aus dem aktuellen Jahr sind, würde von der Logik dazu folgende SQL-Anweisung passen:

  DELETE FROM dbo.Protokoll
  WHERE year(CreatedAt)<year(getdate())

Das Problem dabei ist, dass die DELETE-Anweisung extrem lange läuft und auch das DB-Protokoll – insbesondere im Full-Recovery-Mode – dabei stark anwächst. Ein TRUNCATE als Alternative funktioniert hier nicht, da ja nicht alle Zeilen gelöscht werden sollen.

Ein besserer Lösungsansatz liegt darin, den kleinen Teil von Datensätzen, der beibehalten werden soll in eine separate Tabelle zu kopieren, danach die alte Tabelle zu löschen und schließlich die neue Tabelle umzubenennen:

  SELECT Id, CreatedAt, Beschreibung
INTO dbo.ProtokollNeu
FROM dbo.Protokoll
WHERE year(CreatedAt)=year(getdate())

  DROP TABLE dbo.Protokoll

  EXEC sp_rename ‘dbo.ProtokollNeu’, ‘Protokoll’

Das funktioniert soweit recht performant. Problem dabei ist, dass alle Indizes und weitere Eigenschaften (z.B. Identitätsspezifikation) der Protokolltabelle dabei verloren gehen, da sie beim SELECT INTO nicht mitkopiert werden. Um dies zu umgehen verwendet man eine temporäre Tabelle lediglich als Zwischenspeicher für die Zeilen, die erhalten bleiben sollen, löscht anschließend die Protokolltabelle per TRUNCATE komplett und verschiebt schließlich die Daten aus der temporären Tabelle zurück in die Protokolltabelle:

  SELECT Id, CreatedAt, Beschreibung
INTO #ProtokollTemp
FROM dbo.Protokoll
WHERE year(CreatedAt)=year(getdate())

  TRUNCATE TABLE dbo.Protokoll

  SET IDENTITY_INSERT dbo.Protokoll ON

  INSERT INTO dbo.Protokoll (Id, CreatedAt, Beschreibung)
SELECT Id, CreatedAt, Beschreibung
FROM #ProtokollTemp

  SET IDENTITY_INSERT dbo.Protokoll OFF

  DROP TABLE #ProtokollTemp

Bei dieser Variante müssen die zu erhaltenden Zeilen zwar zweimal bewegt werden, was aber – wenn es sich wirklich nur um einen kleinen Teil der Daten handelt – immer noch deutlich schneller sein sollte, als alle anderen Daten per DELETE zu löschen.

Ein Problem bleibt allerdings auch noch bei dieser Lösung: Wenn Foreign Key Constraints definiert sind, die auf die Protokoll-Tabelle verweisen, müssen diese vorher gelöscht werden, damit die TRUNCATE TABLE Anweisung funktioniert. Anschließend sollten auch die Daten in den referenzierenden Tabellen angepasst werden, damit diese nicht mehr auf inzwischen gelöschte Datenzeilen verweisen. Zuletzt können dann die Foreign Key Constraints wieder angelegt werden. Bei einer Protokoll-Tabelle ist dies aber normalerweise nicht der Fall, so dass das oben gezeigte Skript ausreichen sollte.

About these ads
6 Kommentare
  1. Hallo,

    für Tabellen, wo ich im Vorfeld weiß, das eine hohe Anzahl Datensätze zu erwarten ist, setze ich generell eine Partitionierungsstrategie ein. Konkret bedeutet das eine Partition nach einer Zeitspalte und wenn die Daten nicht mehr benötigt werden, kann die veraltet Partition in eine andere Tabelle geswitcht werden und danach via DROP TABLE gelöscht werden.
    Natürlich ist klar, das dieses Vorgehen nur Sinn macht, wenn die Partition von Beginn an existiert. Aus Erfahrung heraus weiß ich, das eine nachträgliche Partitionierung bei großen Tabellen sehr lange dauern kann. In einem Inhouse-Projekt haben wir für eine nachträgliche Partitonierung auf eine Tabelle mit 1 Mrd. Datensätze 48h benötigt.

    Gruß
    C. Gräfe

    • Hallo und danke für die Ergänzung. Insbesondere in Fällen, wo eine Tabelle regelmäßig von alten Einträgen bereinigt werden muss, ist die Variante mit der Partitionierung sicherlich eine sinnvolle Erweiterung. Mir ging es erst einmal um die Grundidee, dass man nicht immer nur die Daten im Auge hat, die gelöscht werden sollen, sondern auch mal andersherum denkt, die Daten beseite zu schieben, die erhalten bleiben sollen, um dann anschließend den Rest ohne großes Logging zu Löschen. Aber letztlich hängt die Wahl des richtigen Wegs davon ab, ob es sich um eine einmalige Aktion handelt, oder um eine immer wiederkehrende Bereinigung, bei der sich der Vorbereitungsaufwand für die Einrichtung einer Partitionierung lohnt.

  2. Hallo Robert,

    das größte Problem bei der eigentlichen Abfrage (und damit auch der Grund für den durchgeführten Clustered Index Scan – ich gehe mal von einem Clustered Index aus!) ist, dass die Abfrage NONSargable ist.

    DELETE FROM dbo.Protokoll
    WHERE year(CreatedAt)<year(getdate())

    Ein Index auf CreatedAt und anschließend die Abfrage wie folgt ändern…

    DELETE dbo.Protokoll
    WHERE CreatedAt < '20130101';

    Dadurch wird ein SEEK-Predikat verwendet und Microsoft SQL Server kann schneller auf die betroffenen Ressoucen zugreifen!

    http://db-berater.blogspot.de/2012/11/optimierung-von-datenbankmodellensargab.html

    Ein weiterer – nicht zu vernachlässigender – Punkt ist das Löschen von Objekten. Da nicht immer eindeutig erkennbar ist, wer welche Berechtigungen auf dem Objekt besitzt, gehen diese Berechtigungen verloren, da SELECT * INTO neben den Indexen auch die Berechtigungen nicht berücksichtigt.

    Die von mir favorisierten Lösung wäre ein TRUNCATE TABLE, setzt aber leider auch spezielle Berechtigungen voraus (DDL), da TRUNCATE TABLE kein DML sonder eine DDL Operation ist.

    http://db-berater.blogspot.de/2013/06/truncate-table-wird-nicht-protokolliert.html

    Dennoch herzlichen Dank für die Tipps!
    Herzlichen Gruß und bis bald bei den FDBT, Uwe

    • Hallo Uwe,

      danke für den Kommentar. Der Einwand bzgl. SARGs ist normalerweise natürlich berechtigt, passt für den von mir beschriebenen Spezialfall allerdings nicht ganz. Mir ging es in dem Beispiel darum, dass ein Großteil der Daten aus der Tabelle gelöscht werden soll. Da nutzt der Index beim Löschen ohnehin wenig, weil die Bedingung nicht selektiv genug ist. Und truncate bringt nichts, weil ein Teil der Daten ja erhalten bleiben soll.

      Beim der Abfrage zum Kopieren der Daten in die temporäre Tabelle dagegen, stimme ich Dir zu, dass es für eine gute Performance Sinn macht, die Bedingung in der Abfrage so umzusetzen, dass ein Index auf die CreatedAt-Spalte (sofern ein solcher existiert) genutzt werden kann. Damit müsste man also statt …

      SELECT Id, CreatedAt, Beschreibung
      INTO #ProtokollTemp
      FROM dbo.Protokoll
      WHERE year(CreatedAt)=year(getdate())

      … die Variante mit der geänderten WHERE-Klausel verwenden:

      SELECT Id, CreatedAt, Beschreibung
      INTO #ProtokollTemp
      FROM dbo.Protokoll
      WHERE CreatedAt>=’20130101′

      Viele Grüße, Robert

      P.S.: Glückwunsch übrigens zur MVP-Ernennung!

Trackbacks & Pingbacks

  1. Tipp für SQL Server-Nutzer: Performantes Löschen großer Datenmengen Thema | My Blog
  2. Auszug – Microsoft TechNet NewsFlash 18/2013 - TechCenter - Blog - TechCenter - Dell Community

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ photo

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

Folgen

Erhalte jeden neuen Beitrag in deinen Posteingang.

%d Bloggern gefällt das: