Wie optimiere ich den Upsert-Vorgang (Aktualisieren und Einfügen) im SSIS-Paket?

Beispielpaket mit SSIS 2008 R2, das Stapeloperationen einfügt oder aktualisiert:

Hier ist ein Beispielpaket, geschrieben in SSIS 2008 R2 , das zeigt, wie man Einfügung, Aktualisierung zwischen zwei Datenbanken mittels Stapeloperationen durchführt.

• Die Verwendung von OLE DB Command verlangsamt die Aktualisierungsvorgänge für Ihr Paket, da nicht Stapeloperationen durchführt. Jede Zeile wird einzeln aktualisiert.

Das Beispiel verwendet zwei Datenbanken, nämlich Source und Destination . In meinem Beispiel befinden sich beide Datenbanken auf dem Server, aber die Logik kann immer noch auf Datenbanken angewendet werden, die sich auf verschiedenen Servern und Standorten befinden.

Ich habe eine Tabelle namens dbo.SourceTable in meiner Quelldatenbank erstellt Source .

Außerdem wurden zwei Tabellen namens dbo.DestinationTable und dbo.StagingTable in meiner Zieldatenbank Destination erstellt.

Etwa 1,4 Millionen Zeilen in die Tabelle eingefügt dbo.SourceTable mit eindeutigen Werten in die Spalte RowNumber . Die Tabellen dbo.DestinationTable und dbo.StagingTable waren anfangs leer. Für alle Zeilen in der Tabelle dbo.SourceTable ist das Flag IsActive auf false gesetzt.

Erstellt ein SSIS-Paket mit zwei OLE DB-Verbindungsmanagern, die jeweils mit den Datenbanken Source und Destination verbunden sind. Entworfen den Kontrollfluss wie folgt:

• Erstes Execute SQL Task führt die Anweisung TRUNCATE TABLE dbo.StagingTable für die Zieldatenbank aus, um die Staging-Tabellen zu kürzen.

• Im nächsten Abschnitt wird erklärt, wie Data Flow Task konfiguriert ist.

• Zweites Execute SQL Task führt die unten angegebene SQL-Anweisung aus, die Daten in dbo.DestinationTable unter Verwendung der in dbo.StagingTable verfügbaren Daten aktualisiert, vorausgesetzt, dass dies der Fall ist ein eindeutiger Schlüssel, der zwischen diesen beiden Tabellen übereinstimmt. In diesem Fall ist der eindeutige Schlüssel die Spalte RowNumber .

Zu aktualisierendes Skript:

Ich habe die Datenflussaufgabe wie unten gezeigt entworfen.

• OLE DB Source liest Daten aus dbo.SourceTable mit dem SQL-Befehl SELECT RowNumber,CreatedOn, ModifiedOn FROM Source.dbo.SourceTable WHERE IsActive = 1

• Lookup transformation wird verwendet, um zu prüfen, ob der RowNumber-Wert bereits in der Tabelle dbo.DestinationTable

vorhanden ist

• Wenn der Datensatz nicht vorhanden ist, wird er an das OLE DB Destination weitergeleitet, das als Insert into destination table bezeichnet wird fügt die Zeile in dbo.DestinationTable

ein

• Wenn der Datensatz vorhanden ist , wird er an die OLE DB Destination umgeleitet, die als Insert into staging table bezeichnet wird Zeile in dbo.StagingTable . Diese Daten in der Zwischenspeichertabelle werden im zweiten 'SQL-Task ausführen, um die Stapelaktualisierung durchzuführen.

Um einige weitere Zeilen für die OLE DB-Quelle zu aktivieren, habe ich die folgende Abfrage ausgeführt, um einige Datensätze zu aktivieren

Die erste Ausführung des Pakets sah wie folgt aus. Alle Zeilen wurden an die Zieltabelle weitergeleitet, da sie leer war. Die Ausführung des Pakets auf meinem Rechner hat etwa 3 seconds übernommen.

Lief die Zeilenanzahl-Abfrage erneut, um die Zeilenanzahl in allen drei Tabellen zu finden.

Um einige weitere Zeilen für die OLE DB-Quelle zu aktivieren, habe ich die folgende Abfrage ausgeführt, um einige Datensätze zu aktivieren

Die zweite Ausführung des Pakets sah wie folgt aus. 314,268 rows , die zuvor während der ersten Ausführung eingefügt wurden, wurden in die Staging-Tabelle umgeleitet. 628,766 new rows wurden direkt in die Zieltabelle eingefügt. Die Ausführung des Pakets auf meinem Rechner hat ungefähr 12 seconds gedauert. 314,268 rows in der Zieltabelle wurden in der zweiten Task "SQL ausführen" mit den Daten mithilfe der Stagingtabelle aktualisiert.

Lief die Zeilenanzahl-Abfrage erneut, um die Zeilenanzahl in allen drei Tabellen zu finden.

Ich hoffe, das gibt Ihnen eine Idee, Ihre Lösung zu implementieren.

Die beiden Dinge, die ich mir ansehen möchte, sind Ihre Einfügungen (stellen Sie sicher, dass Sie entweder die "Tabelle oder Ansicht - schnell laden" oder "Tabellenname oder Variablen des Ansichtsnamens - schnelles Laden") und Ihre Updates verwenden.

Wie Sie richtig festgestellt haben, ist die Aktualisierungslogik normalerweise, wenn die Leistung nach unten sinkt. Dies liegt daran, dass die OLE DB-Komponente Singleton-Updates für jede durchlaufende Zeile absetzt. Die übliche Herangehensweise, mit der die Leute dies bewerkstelligen, besteht darin, alle Aktualisierungen in eine Zwischenspeichertabelle zu schreiben, so wie es Ihre Einfügelogik tut. Verfolgen Sie dann Ihre Data Flow Task mit einem Execute SQL Task , um eine Massenaktualisierung durchzuführen.

Wenn Sie daran interessiert sind, Tools von Drittanbietern zu erwerben, bietet PragmaticWorks ein Upsert-Ziel