SQL Tabelle (CAS Changelogbook) schnell verkleinern

Posted on by

Das löschen mehrerer Zeilen aus großen SQL Tabellen kann schnell zum Geduldsspiel werden.

Wenn z.B. aus dem CAS Changelogbook alle alten Einträge entfernt werden sollen, würde folgende Anweisung schnell ein paar Stunden laufen ( und im Zweifelsfall in einen Timeout laufen:

DELETE FROM dbo.Changelogbook Where updatetimestamp < getdate()-365

Die DELETE-Anweisung läuft extrem lange und auch das DB-Protokoll – insbesondere im Full-Recovery-Mode wächst stark an.
Ein TRUNCATE kann alternativ nicht verwendet werden, da ja einige Zeilen erhalten bleiben 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 *
INTO dbo.ChangelogbookNeu
From dbo.Changelogbook
Where updatetimestamp > getdate()-365

DROP TABLE dbo.Changelogbook
EXEC sp_rename, 'dbo.ChangelogbookNeu', 'Changelogbook'

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 *
INTO #ChangelogbookTmp
FROM dbo.Changelogbook
Where updatetimestamp > getdate()-365;
--
TRUNCATE TABLE dbo.Changelogbook;

SET IDENTITY_INSERT dbo.Changelogbook ON

INSERT INTO dbo.Changelogbook ( IDNR, GGUID,Tablename, Tableguid, Fieldname, OldfieldValue, NewFieldValue, 
         IsMemo,Changetype, Updatetimestamp, Userdescription, UserGuid,Domainid,Fieldtype, Fieldname2)
SELECT * From #ChangelogbookTmp


SET IDENTITY_INSERT dbo.Changelogbook OFF
-- 
DROP TABLE #ChangelogbookTmp

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.

 

Quelle: https://pantheronsql.wordpress.com/2013/09/02/tipps-tricks-performantes-loschen-von-grosen-datenmengen/

Author: Stefan