T-SQL Ninja #19

Batches

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.

Referenzen

Ninja-Notebooks @ GitHub

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.