SSIS Datenimport: Update vieler Datensätze sehr langsam
avatar

Das Problem

Im Zuge eines Datenimports per SSIS musste ich u.a. viele Adress-Daten (> 350.000 Datensätze) aus einer Warehouse-Datenbank in die Datenbank der entwickelten Anwendung importieren. Dieser Import sollte jede Nacht erfolgen um die Fachanwendungs-Datenbank mit der Warehouse-Datenbank zu synchronisieren.

Die Daten des Warehouse habe ich zunächst über ein eigenes SSIS-Paket 1:1 in extra dafür angelegte Staging-Tabellen (die exakt die gleiche Struktur wie die WarehouseTabelle besitzen) importiert.
So eine 1:1 Kopie ist sehr schnell erstellt (im SQL-Server-Management-Studio kann der Assistent zum Daten-Export benutzt werden um das SSIS-Paket zu erzeugen), performant und schont vor allem die Ressourcen des Warehouse-Servers.

Diese Staging-Tabellen habe ich dann im eigentlich Datenimport ausgelesen, die Transformation durchgeführt und die Daten in die fachlichen Tabellen der  Anwendung geschrieben.

Der erste Durchlauf in dem nur Daten in die leere Adress-Tabelle eingefügt wurden verlief so zügig wie erwartet.
Umso überraschter war ich dann, als ich feststellte, dass der zweite Durchlauf, bei dem die Adressdaten durch ein OLE-SQL-Command welches ein „Update“ ausführt, wurden seeehr langsam vonstatten ging. Pro 3000 Zeilen wurden ca. 10 Sekunden benötigt.

Die Lösung

Bei meinen Recherchen fand ich den unter [1] verlinkten Artikel in dem die Problematik sehr gut beschrieben ist.
In dem von mir entwickelten Paket wurde das Update-Command für jede Datenzeile einzeln aufgerufen wurde, SSIS ist aber dafür entwickelt mit Buffern zu arbeiten um mehrere 1000 bis 10.000 Datensätze auf einen Schlag zu verarbeiten.
Da es in meinem Fall egal war, wann meine Daten aktualisiert wurden, entschied ich mich für den im Artikel vorgestellten Ansatz die Daten in eine temporäre Tabelle einzufügen und die Adress-Tabelle dann „en bloc“ mit einem Update-Statement zu aktualisieren.

Um das Beispiel nachzuvollziehen müssen folgende Tabellen erstellt werden:

CREATE TABLE [dbo].[Warehouse_Adresse]
(
  [AdresseId] [int] IDENTITY(1,1) NOT NULL,
  [Strasse] [nvarchar](100) NOT NULL,
  [Hausnummer] [int] NOT NULL,
  [Zusatz] [nvarchar](255) NULL,
  [PLZ] [nvarchar](5) NOT NULL,
  [Ort] [nvarchar](40) NOT NULL,
  [SyncKey] [nvarchar](255) NOT NULL
) ON [PRIMARY]
 
CREATE TABLE [dbo].[Adresse]
(
  [AdresseId] [int] IDENTITY(1,1) NOT NULL,
  [Strasse] [nvarchar](100) NOT NULL,
  [Hausnummer] [int] NOT NULL,
  [Zusatz] [nvarchar](255) NULL,
  [PLZ] [nvarchar](5) NOT NULL,
  [Ort] [nvarchar](40) NOT NULL,
  [SyncKey] [nvarchar](255) NOT NULL
) ON [PRIMARY]

(Die Strukturgleichheit der beiden obigen Tabellen nur zu Vereinfachungszwecken so gewählt wurden, die realen Tabellen wichen erheblich voneinander ab)

Die dann über folgendes Skript mit Testdaten befüllt wird (Achtung: je nach Hardware zieht dieses Skript eine mehr oder wenige lange Kaffeepause nach sich 😉 ):

DECLARE @insertStatement VARCHAR(max)
 
DECLARE @counter int
SET @counter = 0
WHILE @counter < 400000
BEGIN
  SET @counter = @counter + 1
  SET @insertStatement = 'INSERT INTO Warehouse_Adresse (Strasse, Hausnummer, Zusatz, PLZ, Ort, SyncKey) SELECT ''Teststrasse'', ' + convert(varchar,@counter) + '1, ''a'', 20359, ''Hamburg'', ''syncKey' + convert(varchar,@counter) + ''''
  EXEC(@insertStatement)
END

Im alten Ansatz wird eine OLE-DB-Quelle zum Auslesen benutzt und die Daten über einen OLE-DB-Befehl eingefügt (die Transformation habe ich zum besseren Verständnis weggelassen):

SSIS_Update_Org

Für den neuen Ansatz wird folgende Zwischentabelle erstellt:

CREATE TABLE [dbo].[Adresse_temp]
(
 [AdresseId] [int] IDENTITY(1,1) NOT NULL,
 [Strasse] [nvarchar](100) NOT NULL,
 [Hausnummer] [int] NOT NULL,
 [Zusatz] [nvarchar](255) NULL,
 [PLZ] [nvarchar](5) NOT NULL,
 [Ort] [nvarchar](40) NOT NULL,
 [SyncKey] [nvarchar](255) NULL,
) ON [PRIMARY]

Das SSIS-Paket wird dahingehend geändert, dass die Daten nun immer in die Zwischentabelle eingefügt werden:

SSIS_Update_Optimiert

Im letzten Schritt wird dann die Adress-Tabelle mit den Inhalten der Zwischentabelle aktualisiert. Dies geschieht mit folgendem SQL-Befehl:

UPDATE Adresse
SET
  Adresse.Strasse = Adresse_temp.Strassee,
  Adresse.Hausnummer = Adresse_temp.Hausnummer,
  Adresse.Zusatz = Adresse_temp.Zusatz,
  Adresse.PLZ = Adresse_temp.PLZ,
  Adresse.Ort = Adresse_temp.Ort,
FROM Adresse, Adresse_temp
WHERE Adresse.SyncKey = Adresse_temp.SyncKey;
 
DELETE FROM Adresse_temp

Fazit

Der gewählte Ansatz hat in meinem Fall den Datenimport von „unbenutzbar“ auf „läuft in 2 Minuten durch“ beschleunigt.
Eigentlich bin ich kein Fan von Zwischentabellen, da sie die Datenbank unübersichtlicher machen, doch manchmal lässt sich ihr Einsatz nicht vermeiden. Für Datenimporte mit wenigen 10.000 Datensätzen setze ich nach wie vor keine Zwischentabellen ein, sondern schreibe Update-Statements wie im zuerst gewählten Ansatz.

Links

[1] http://www.ssistalk.com/2009/10/29/ssis-why-is-my-data-flow-so-slow-with-an-ole-db-command-component

Schreibe einen Kommentar