AUFGABENSTELLUNG
Fragestellung
Vielfach gibt es beim Import von Daten die Notwendigkeit, Informationen aus anderen Tabellen nachzuschlagen (Lookup, die Tabelle, in der nachgeschlagen wird, wird als Lookup Tabelle bezeichnet). In folgender Analyse wurden verschiedene Möglichkeiten dafür im Hinblick auf deren Performance untersucht.
Teststellung
Es werden Daten von einem Quellserver auf einen Zielserver übertragen. Die übertragene Datenmenge für den relevanten Test beträgt 50.000 Zeilen, es werden aber auch andere Datenmengen getestet. Für diese Daten wird ein Lookup auf eine zweite Tabelle am Zielserver durchgeführt. In dieser Lookup Tabelle befinden sich ca. 1.200.000 Zeilen. Das SSIS Package wird dabei am Zielserver ausgeführt.
VARIANTEN FÜR LOOKUP
SSIS Lookup Komponente
Die SSIS Lookup Komponente bietet die Möglichkeit, Daten in einer anderen Datenbanktabelle nachzuschlagen. Die Komponente bietet gleich mit eingebauten Caching Möglichkeiten.
Nachträgliches Einfügen der Lookup Daten per Update SQL
In einem ersten Schritt werden die Daten ohne die nachzuschlagenden Information in die Datenbank eingefügt. In einem zweiten Schritt werden per SQL Update Statement die benötigten Zusatzfelder nachgeschlagen.
SSIS Merge Join Komponente
Die Merge Join Komponente ermöglicht das Joinen von sortierten Tabellen in einem SSIS Paket. Der Lösungsweg ist in dieser Variante also die Abfrage der Basistabelle und der Lookuptabelle und die Zusammenführung im SSIS Paket im Speicher.
Join in SQL Server über Linked Server Abfragen
Es wird der Lookup Join bereits in der Datenbank in einer Distributed Query (d.h eine Abfrage über mehrere Server) durchgeführt. In SSIS werden die fertigen Daten nur noch in die Zieldatenbank geschrieben.
ERGEBNISSE
Referenzergebnis ohne Lookup
Ein bloßes Einfügen von 700.000 Datensätzen in die Zieldatenbank ohne Lookup benötigt ca. 29 Sek.
Lookup Komponente
* Ohne Index
+ Ausführungszeit bei 50.000 Zeilen: 24 Sek.
+ Ausführungszeit bei 100.000 Zeilen: 2 Min. 43 Sek.
+ Bei steigender Zeilenanzahl steigt die Ausführungszeit überlinear an.
Ab 100.000 Zeilen werden je 10.000 zusätzliche Zeilen ca. 3 min. benötigt.
* Mit Index
+ Ausführungszeit bei 50.000 Zeilen: 3 Sek.
+ Ausführungszeit bei 100.000 Zeilen: 7 Sek.
+ Ausführungszeit bei 700.000 Zeilen: 46 Sek.
* Wird keine Memory Restriction aktiviert, so wird die gesamte Lookup Tabelle gechached. Bei den getesteten Daten (1,2 Mio.) Datensätze überstieg der Speicherbedarf dafür die 1 GB Grenze, d.h. es wird für das Caching in SSIS mehr Speicher benötigt, als die Tabelle am SQL Server benötigt.
* Wird nur eine Memory Restriction eingestellt, so ist es wahrscheinlich, dass nicht alle benötigten Zeilen im Chache sind. Es ist daher zu empfehlen, das Caching SQL Sattement so zu gestalten, dass nur die wirklich benötigten Zeilen im Cache enthalten sind.
* Durch Setzen eines Index auf die Join Spalten und die zu holenden Spalten kann die Performance dramatisch erhöht werden, da der Lookup komplett aus dem Index bedient werden kann.
Merge Join Komponente
Der Test der Merge Join Komponente wurde abgebrochen, da die Merge Join Komponente alle Datensätze aus beiden Tabellen (Basis und Lookup Tabelle) in den Speicher lädt. Der Speicherbedarf überstieg dabei die 1 GB Grenze, eine sinnvolle Ausführung war dadurch nicht mehr möglich.
Nachträgliches Einfügen der Lookup Daten per Update SQL
* Ausführungszeit für 50.000 Zeilen: 11 Sek.
* Ausführungszeit für 100.000 Zeilen: 35 Sek.
* Ausführungszeit 700.000 Zeilen: 38 Sek.
* Die Ausführungszeit steigt mit der Menge der importierten Datensätze annähernd linear an
Join in SQL Server über Linked Server Abfragen
* Ausführungszeit für 50.000 Zeilen: 11 Sek.
* Ausführungszeit bei 100.000 Zeilen: 19 Sek.
* Ausführungszeit 700.000 Zeilen: 1 Min. 30 Sek.
* Die Ausführungszeit steigt mit der Menge der importierten Datensätze annähernd linear an.
* Abfragen auf Linked Server können direkt als SQL Command in SSIS Komponenten verwendet werden.
* Um Zugriffe auf Linked Server in einer View speichern zu können, muss auf dem Server der View der Distributed Transaction Coordinator Dienst ausgeführt werden.
* Potenzielle Probleme
+ Security Probleme beim Zurgiff auf den Linked Server
+ Locking Probleme, da während Import ständig auf die Lookup Tabelle selektiert wird
+ Performanceprobleme durch Übertragung der Datensätze zwischen den Servern über das Netzwerk (!!)
RESÜMEE
Erste Wahl was Performance betrifft ist die Verwendung der Lookup Komponente mit Indexierter Lookup Tabelle und angepasstem Lookup SQL Statement. Bei guter Indizierung der Lookup Tabelle ist die Performance unabhängig von Datenmengen sehr gut. Das einzige mögliche Problem hierbei ist, dass sehr viele SQL Statements über das Netzwerk abgesetzt werden müssen, was zu potenziellen Performanceproblemen führen kann.
Die zweitbeste Variante ist die Variante Einfügen und nachträgliches Update. Diese ist nicht so anfällig für Netzwerkübertragungsthemen, da für den Lookup nur ein SQL Statement abgesetzt wird. Aufgrund der großen potenziellen Problemsituationen bei Verwendung von Distributed Queries oder Distributed Views empfehlen wir, diese Variante wenn möglich zu vermeiden. Vor allem das Thema Locking und Netzwerkübertragung führen wahrscheinlich zu Problemen.