Was sind Batches?
Manchmal kann es vorkommen, dass ihr Operationen auf sehr großen Tabellen machen müsst. Dann kann es vorkommen, dass aufgrund des Transaktions-Verhaltens eurer Datenbank kein Platz mehr in der TempDB vorhanden ist. Das liegt daran, dass Relationale Datenbanken den ACID-Prinzipien folgen, nach denen Operationen Atomar sein sollen, also wird beispielsweise ein DELETE-Statement komplett ausgeführt oder zurückgerollt, Zwischenstände, bei denen nur manche Datensätze gelöscht wurden, dürfen nicht vorkommen.
Wie könnt ihr mit Batches arbeiten?
Um mit Batches zu arbeiten, benötigt ihr WHILE-Statements, die wir in der vergangenen Woche vorgestellt hatten. Die Grundidee ist dabei: lösche so lange immer die nächsten N Datensätze bis keine mehr übrig sind. Suchen wir uns für diese Fragestellung zunächst die größte Tabelle unserer WideWorldImporters-Datenbank. Um diese zu finden, fragen wir die Metadaten der Datenbank ab:
SELECT
SCHEMA_NAME(A.schema_id) + '.' + A.Name AS [TableName]
,B.rows as [RowCount]
FROM sys.objects A
INNER JOIN sys.partitions B
ON A.object_id = B.object_id
WHERE A.type = 'U'
ORDER BY B.[rows] DESC
Die größte Tabelle mit über 3,5 Millionen Datensätzen ist dabei die Warehouse.ColdRoomTemperatures_Archive-Tabelle. Sehen wir uns diese Tabelle einmal an, dann stellen wir fest, dass sie die Aufzeichnungen aus dem Zeitraum vom 20.12.2015 bis zum 31.05.2016 enthält:
SELECT MIN(RecordedWhen) ,MAX(RecordedWhen) FROM Warehouse.ColdRoomTemperatures_Archive
Hiervon möchten wir nun alle Datensätze löschen, die älter als der 01.04.2016 sind. Wenn wir nachzählen, stellen wir fest, dass es sich dabei immerhin um 2,2 Millionen Datensätze handelt:
SELECT
COUNT(*)
FROM Warehouse.ColdRoomTemperatures_Archive
WHERE RecordedWhen < '2016-04-01'
Da es sich bei der Tabelle allerdings um eine temporale Tabelle (System versioned table) handelt, können wir aus dieser gar nicht so direkt löschen, kopieren wir sie uns also in ein temporäres Objekt, in dem wir nach Lust und Laune löschen können:
DROP TABLE IF EXISTS #WarehouseDeleteTest SELECT * INTO #WarehouseDeleteTest FROM Warehouse.ColdRoomTemperatures_Archive
Um diese nun für die TempDB und das Transaktionslog ressourcenschonend zu löschen, können wir nun das oben beschriebene Pattern anwenden. Dafür legen wir zunächst eine Variable an, in der wir speichern wie viele Datensätze wir im letzten Schritt gelöscht haben. Dann löschen wir so lange immer 100.000 Datensätze aus der Tabelle bis diese Anzahl null ist, also keine Datensätze zum Löschen mehr übrig sind. Das tun wir jeweils in einer Transaktion, um das Transaktionslog nicht zu sehr zu belasten:
DECLARE @deletedRows int = 1
WHILE @deletedRows > 0
BEGIN
BEGIN TRANSACTION
DELETE TOP(100000)
#WarehouseDeleteTest
WHERE RecordedWhen < '2016-04-01'
SET @deletedRows = @@ROWCOUNT
COMMIT TRANSACTION
END
Wir sehen im Output mehrere Zeilen, die uns mitteilen, dass 100.000 Datensätze gelöscht wurden (100000 rows affected). Dann am Ende eine Zeile, bei der weniger als 100.000 Datensätze betroffen sind (96324 rows affected) und ganz zum Schluss eine Zeile wo keine Datensätze zum Löschen mehr übriggeblieben sind (0 rows affected). Zählen wir nun nach, so haben wir in unserer temporären Tabelle nur noch 1,3 Millionen Datensätze übrig:
select count(*) FROM #WarehouseDeleteTest
Und wenn wir zu guter Letzt noch prüfen, was das Maximale und Minimale Datum dieser Datensätze ist, so stellen wir fest, dass wie erwartet alle Datensätze vor dem ersten April 2016 gelöscht wurden:
SELECT MIN(RecordedWhen) ,MAX(RecordedWhen) FROM #WarehouseDeleteTest
Dieses Vorgehen ist immer dann sinnvoll, wenn ihr große Datenmengen verarbeiten möchtet, euch die transaktionale Sicherheit aber nicht so wichtig ist, dass das in einem atomaren Schritt erfolgen muss. Beispielsweise lässt es sich im Data Warehousing-Bereich gut anwenden, wenn aus einer Staging-Tabelle in eine DWH-Tabelle gemerged werden soll ( MERGE Statements hatten wir in Woche 15 beschrieben) und die Quell-Tabelle so viele Datensätze enthält, dass das MERGE Statement sämtliche Hardware-Ressourcen sprengt. Wichtig ist nur, dass ihr euch bewusst seid, dass ihr hier die ACID-Prinzipien außer Gefecht setzt und euch um entsprechende Fehlerbehandlung und Wiederaufsetzbarkeit kümmert.
