SSIS vs. SQL – Lesbarkeit/Wartbarkeit -oder- Wie viel SQL darf’s denn sein?

This Article in English…

Die Vorgeschichte zu diesem Artikel…

SQL Server Integration Services (SSIS) ist ein äußerst mächtiges Tool Set für die Entwicklung von ETL-Strecken. Es gibt viele gute Gründe, die für einen Einsatz von SSIS sprechen. Es gibt derer aber auch genügend, die dagegen sprechen. Beschränken wir uns auf den Microsoft Produkt Stack, dann kommt als Alternative für die Entwicklung von komplexen ETL Strecken (im Wesentlichen) nur noch Transact-SQL (T-SQL) in Frage.

Dieser Artikel gehört zu einer Serie von Artikeln, die wichtige Entscheidungskriterien für die Wahl der richtigen Technologie(n) – SSIS und/oder T-SQL – beleuchten.

Mit Blick auf die Verwendung einer Quellcodeverwaltung habe ich in dem Artikel SSIS vs. Transact-SQL – Quellcodeverwaltung die Vorteile der Verwendung von SQL Skripten (und hier SQL Server Stored Procedures), etc. gegenüber SSIS herausgestellt. Die Änderungen in einer Stored Procedure können leicht durch Vergleich zweier Versionen in Visual Studio dargestellt werden. Ein ähnlicher Vergleich zweier Versionen eines SQL Server Integration Services (SSIS) Paketes zeigt selbst bei minimalen Änderungen bereits eine unübersichtliche Anzahl an Änderungen in dem zugrundeliegenden Dokumententyp dtsx. Damit scheint es schier unmöglich die Änderung zweier Versionen zu identifizieren.

In dem erwähnten Artikel habe ich über ein T-SQL Statement die Hierarchie-Ebenen der Mitarbeiter aus der Tabelle [AdventureWorksDW2017].[DimEmployee] abgeleitet, um anschließend auf der Basis der gefundenen Hierarchie-Ebenen 1 bis 5 ein Ranking der Urlaubs- und Krankheitsstunden zu ermitteln. Die Ermittlung sollte jeweils über die Windowed Function NTILE() und DENSE_RANK() erfolgen. Die Klassifizierung der Mitarbeiter über die NTILE-Funktion sollte 3 Klassen ergeben.

Die Bearbeitung der Aufgabe mit T-SQL hat nur wenigen Minuten entwickelt. Common Table Expressions (CTE) unterstützen rekursive Aufrufe und das Ranking konnte schnell über die die Windowed Function ermittelt werden.

Um die Verwendung von T-SQL Prozeduren/Skripten mit Blick auf die Quellcodeverwaltung der Verwendung von SSIS Paketen gegenüberstellen zu können, wollte ich ein “inhaltsgleiches” SSIS Paket entwickeln. Ich habe mich der naiven Vorstellung hingegeben, dass diese Aufgabe zwar mit etwas mehr zeitlichem Aufwand zu lösen ist, aber immerhin mit einem vertretbaren Aufwand lösbar ist.

Weit gefehlt!

Für die beiden wesentlichen Anforderungen gibt es meines Wissens keine einfache Lösung, geschweige denn Standard Tasks oder Funktionen, die in einer Expression verwendet werden könnten.

  • Rekursive Ermittlung der Hierarchie-Ebenen
  • Ermittlung des Rankings

Einen einfachen Lösungsweg für die rekursive Ermittlung der Hierarchie-Ebenen habe ich weder in den Untiefen einschlägiger Blogs gefunden noch selbst auf die “Schnelle” ableiten können. Versuche, das Ranking gemäß der Funktion NTILE() über eine Expression zu ermitteln, habe ich schnell aufgegeben und diesen Teil der Aufgabe schließlich über Skript-Tasks gelöst. Das Ergebnis ist der trotz der Komplexität des entwickelten Artefakts – wie ich finde – recht übersichtlich. Es ist aber eine statische Lösung und sie ist auf 5 Hierarchieebenen beschränkt.

Zwischendurch blitzte dann immer wieder die Frage auf… warum hast Du das relevante SQL Statement nicht in die OLEDB SRC-Datasource Task gepackt: Wieviel SQL darf’s denn sein?

In diesem Artikel werden drei Lösungsansätze mit Blick auf diese Fragestellung beschrieben und verglichen:

  • Komplexes SQL in einer Stored Procedure
    Ausgangspunkt ist eine Stored Procedure, die das Ergebnis eines SELECT Statement in eine Ziel-Tabelle schreibt. Die Stored Procedure wird von einer SQL Task im Control Flow eines SSIS Paketes aufgerufen. Ein Control Flow, sonst nichts.
  • Komplexes SQL in einer Data Source Task
    Das SQL Statement kann alternativ auch in der Data Source Task eines SSIS Data Flows platziert werden, gefolgt von einer Destination Task, um die Daten in die Ziel-Tabelle zu schreiben. Die Lösung enthält einen Control Flow, einen Data Flow und zwei Data Flow Tasks.
  • Einfaches SQL in einer Data Source Task
    Die extreme Alternative ohne SQL (außer eines einfachen SELECTs in der Data Source Task) hat in der von mir entwickelten Variante eine “temporäre” Tabelle zum Zwischenspeichern des Ergebnisses, einen Control Flow, 2 Data Flows und zahlreiche Data Flow-Tasks, die darüber hinaus mit nicht ganz einfachen Conditional Splits und Precedence Constraints verknüpft sind.

Diese drei Alternativen werden in diesem Artikel unter den folgenden Gesichtspunkten bewertet:

  • Dauer der Entwicklung
  • Lesbarkeit
  • Wartbarkeit
  • Performance
  • Funktionsumfang

Die drei Lösungsansätze

Zunächst werden die drei Lösungen vorgestellt. Alle Lösungen wurden mit Microsoft Visual Studio 2017 und SQL Server 2017 entwickelt.

Komplexes SQL in einer Stored Procedure

Diese Lösung basiert auf einem komplexen SQL Statement, das die Daten in die Zieltabelle [dbo].[post00210001] schreibt. Basis des Statements ist die rekursive Verwendung einer CTE für die Berechnung der Hierarchie-Ebenen. Dem Statement vorangestellt ist eine TRUNCATE-Anweisung, um die Zieltabelle vor dem INSERT zu leeren. Der Name der Prozedur lautet [dbo].[spInsertpost0021]. Die Prozedur wird schließlich in SSIS in dem Paket über eine Execute SQL Task aufgerufen.

Source

Komplexes SQL in einer Data Source Task

Die zweite Lösung basiert auf einem SSIS Data Flow, der nichts anderes enthält als eine OLE-DB Source Task sowie eine OLE-DB Destination Task.

Die OLE-DB Source Task definiert die Datenquelle als SQL Statement… eben das oben erwähnte komplexe SQL Statement aus der Prozedur [dbo].[spInsertPost0021], jedoch ohne den INSERT INTO Part des Statements.

Es werden keine weiteren Transformationen in dem Data Flow durchgeführt.

Einfaches SQL in einer Data Source Task

Die dritte Lösung verwendet ausschließlich SSIS Tasks für die Berechnung der Hierarchie-Ebenen sowie des Rankings. Die gewählte Lösung beinhaltet einen Control Flow, zwei Data Flows und zwei Tabellen. Der Control Flow enthält 2 SQL Tasks sowie 2 Data Flow Tasks mit den folgenden Aufgaben:

  • 0500 SQL Truncate Table
    Diese Task löscht die beiden temporären Tabellen, die für die Lösung benötigt werden: [dbo].[post00210003] und [dbo].[post00210004].
  • 1000 DFT Calculate Levels
    Dieser Data Flow berechnet zu jedem Mitarbeiter den Hierarchielevel. Die Berechnung ist nicht generisch und auf die 5 enthaltenen Hierarchielevel begrenzt.
  • 2000 SQL Level Counts
    Für die Berechnung des Rankings gemäß der Windowed Function NTILE() ist es erforderlich, die Anzahl der Mitarbeiter je Hierarchielevel zu kennen. Diese SQL Task führt 5 SELECT Statements aus, um die Anzahl der Mitarbeiter je Level zu ermitteln und in hierfür vorgesehenen Variablen zu speichern.
  • 3000 DFT Calculate Ranking
    Der zweite Data Flow ermittelt schließlich das Ranking mit Hilfe von Skript Tasks und speichert das Endergebnis in der Zieltabelle [dbo].[post00210004].

Controlflow

1000 DFT Calculate Levels

Die Datenquelle 1000 OLEDB Source enthält ein einfaches SQL Statement ohne weitere Berechnungen.

3000 DFT Caculate Ranking

Bewertung

Dauer der Entwicklung

Komplexes SQL in einer Stored Procedure

Bei der Entwicklung gab es – wie oben erwähnt – zwei Herausforderungen: die Ermittlung der Hierarchie-Ebenen der Mitarbeiter und die Ermittlung des Rankings. T-SQL bietet für beide Herausforderungen Konzepte und Lösungen an, die eine einfache und schnelle Lösung ermöglichen. Über das Konzept der rekursiven Common Table Expression können hierarchisch organisierte Daten schnell und mit wenig Aufwand abgefragt werden. Mit den Windowed Functions NTILE() und DENSE_RANK() ist auch die zweite Herausforderung mit nur wenigen Zeilen umgesetzt. Das SQL Statement ist in nur wenigen Minuten erstellt.

Komplexes SQL in einer Data Source Task

Die zweite Lösung verwendet das Komplexe SQL Statement als Datenquelle in einer Data Source Task. Das SSIS Paket der zweiten Lösung enthält lediglich zwei Data Flow Tasks: Eine Datenquelle und ein Datenziel. Es werden keine weiteren Transformationen durchgeführt. Der wesentliche Aufwand ergibt sich aus der Erstellung des SQL Statements. Wie im vorigen Abschnitt erläutert, ist dieses in nur wenigen Minuten erstellt. Das SSIS Paket selbst ist mit nur minimalem Aufwand ebenfalls schnell erstellt.

Einfaches SQL in einer Data Source Task

Diese Lösung wurde unter der Maßgabe entwickelt, dass sowohl die Berechnung der Hierarchie-Ebenen sowie des Rankings mit den Board-Mitteln von SSIS umzusetzen sind. Die Datenquelle der Data Source Task wird durch ein einfaches SELECT Statement definiert. Alle weiteren Transformationen erfolgen über SSIS Data Flow Tasks. Im Unterschied zu T-SQL gibt es hier nicht die eine ideale Lösung. Tatsächlich bin ich vor der Erstellung dieser Beispiel-Lösung dem Trugschluss aufgesessen, dass die beiden Herausforderungen ähnlich komfortabel in SSIS zu lösen sind. Nach meinem derzeitigen Kenntnisstand ist das nicht möglich. Für die nicht generische Ermittlung der Hierarchie-Ebenen und die Ermittlung des Rankings wurden mehr als 40 Data Flow Tasks konfiguriert, die darüber hinaus mit komplexen Precedence-Constraints und Join/Split-Tasks verbunden sind. Die Entwicklung hat einige Stunden Zeit gekostet.

Fazit

In diesem Beispiel sind die Anforderungen mit T-SQL wesentlich schneller umgesetzt als mit (nur) SSIS. Während in T-SQL der Lösungsweg mehr oder weniger vorgegeben ist, musste der Lösungsweg in SSIS erst einmal entworfen werden. Der Aufwand der Entwicklung des SQL Statements entsprach nur einem Bruchteil der Zeit, die für die SSIS Lösung aufgewendet werden musste.

Je mehr SQL in einem SSIS Paket enthalten ist, desto schneller wird die Entwicklung sein.

Lesbarkeit

Komplexes SQL in einer Stored Procedure

Das eigentliche SQL INSERT Statement der ersten Lösung erstreckt sich bei großzügiger Strukturierung gerade mal über ca. 80 Zeilen. Sofern das Statement zusätzlich halbwegs ordentlich formatiert ist, ist der gewählte Lösungsweg schnell erfassbar. Das oben abgebildete Statement der ersten Lösung ist gut lesbar.

Komplexes SQL in einer Data Source Task

Die zweite Lösung verwendet in Teilen das SQL Statement aus der Stored Procedure [dbo].[spInsertPost0021] der ersten Lösung. Während das SQL Statement an sich gut verständlich ist, ist es innerhalb des Feldes SQL command text der Data Source Task nur schwer lesbar. Zum einen wird für die Darstellung des Textes eine proportionale Schriftart verwendet und zum anderen ist das Feld nichts anderes als ein Guck-Loch. Komplexe Statements sind nur sehr schwer über diesen Dialog lesbar.

Einfaches SQL in einer Data Source Task

In dem auf SSIS basierenden dritten Lösungsansatz wurde als Datenquelle für die Lösung ein einfaches SQL Statement definiert. Die Komplexität liegt hier in den über 40 Data Flow Tasks. Während ein SQL Statement mehr oder minder von oben nach unten gelesen werden kann, sind bei der Lektüre eines komplexen SSIS Paketes umfangreiche Aktionen erforderlich: Alle Tasks sind zu öffnen, ihre Konfiguration muss geprüft werden. Ein großer Teil der Logik ist über die Precedence Constraints und Join Tasks abgebildet und muss erarbeitet werden. Die Erfassung der Logik dieser Lösung ist mit ungleich mehr Aufwand verbunden als die Erfassung des komplexen SQL Statements.

Fazit

T-SQL Statements sind bei strukturierter und formatierter Notation ungleich lesbarer als SSIS Pakete, die die gleiche Aufgabe erfüllen.

Der Grad der Lesbarkeit richtet sich auch danach, wo das SQL Statement gespeichert und in welchem „Editor“ das Statement per Default angezeigt wird. Während eine Prozedur oder eine Statement in SQL Server Management Studio gut lesbar ist, ist es das in dem Dialog der Data Source Task nicht.

Wartbarkeit

Lesbarkeit

Sofern bei Wartbarkeit auf die Lesbarkeit abgestellt wird, geht dieser Punkt ganz klar ebenfalls an T-SQL. Jene Stellen, die zu ändern sind, aber auch solche, an denen neue Funktionen bereitgestellt werden, können in einem SQL Skript schnell identifiziert werden.

Künftige Anforderungen

Wartbarkeit stellt prinzipiell auf künftige Änderungen ab. Hier gibt es durchaus Aspekte, die eine einfache Bewertung schwierig machen.

Faktisch ist die Anzahl der Hierarchie-Ebenen in der Dimension [AdventureWorksDW2017].[DimEmployee] auf 5 beschränkt. Eine künftige Änderungen auf 6 Hierarchie-Ebenen bedeutet für die Lösung 1 (komplexes Statement) keinen zusätzlichen Aufwand. Egal wie tief die Hierarchie strukturiert ist, T-SQL kommt damit – unter Berücksichtigung der technischen Beschränkung auf 32.767 Rekursionen innerhalb einer rekursiven Common Table Expression – zu Recht.

Anders sieht es bei Verwendung von vorwiegend SSIS aus. Eine Erweiterung um zusätzliche Hierarchie-Ebenen bedeutet hier einen nicht unerheblichen Mehraufwand für die Anpassung des SSIS Paketes. Dramatisch hoch kann der Mehraufwand sein, wenn ein komplexer SSIS Data Flow irgendwo in der Mitte geändert werden muss. Das kann zur Folge haben, dass jene Data Flow Tasks, die der Änderung folgen, komplett neu entwickelt werden müssen.

Auf der anderen Seite kann die Bewertung auch ganz anders ausfallen, wenn der Grund für eine künftige Änderung der Wechsel des Datenbank Providers ist. Bei einem Wechsel des Datenbank Managements Systems von zum Beispiel SQL Server nach Oracle ist nicht gewährleistet, dass der in SQL Statements verwendete Sprachumfang von der künftigen Plattform unterstützt wird. Im schlechtesten Fall kann ein Statement nicht oder nur mit umfangreichem Aufwand an den Sprachumfang der neuen Umgebung angepasst werden. Eine künftige Änderung der Plattform bedeutet für die Lösung 3 – unter der Voraussetzung, dass der Datenbank-Provider von SSIS unterstützt wird – keine Notwendigkeit einer funktionalen Änderung des SSIS Paketes.

Vergleich von zwei Versionen eines Artefaktes

Eine Voraussetzung für die Entwicklung wartbarer Artefakte ist die Versionierung der Artefakte in einer Quellcodeverwaltung. Der Entwickler muss zu jeder ausgelieferten und installierten Version einer Software oder einer ETL Strecke in der Lage sein, den Code im Repository zu identifizieren. Im Fehlerfall sind Änderungen am Code zurückzuverfolgen, um Fehler identifizieren zu können. Mit welcher Version ist eine fehlerhafte Implementierung erfolgt? Was wurde von Version zu Version geändert? Der Vergleich von jeweils zwei Versionen eines Artefaktes führt in der Regel schnell zu einer Antwort. In dem Artikel SSIS vs. Transact-SQL – Quellcodeverwaltung habe ich bereits die Aspekte der Vergleichbarkeit von T-SQL Statements und SSIS Paketen herausgearbeitet. Zwei Versionen eines SSIS Paketes sind schlicht nicht vergleichbar. SSIS Pakete sind unter diesem Gesichtspunkt nur sehr schwer wartbar.

Fazit

Die meisten Entwickler lagern einen großen Teil von Transformation bereits als SQL Statements in Data Source Tasks aus. Kaum eine Entwicklerin wird ein komplexes Statement, das mehrere Tabellen verjoint, über einen SSIS Data Flow entwickeln wollen. Der Aufwand hierfür wäre vergleichsweise exorbitant hoch und – das sei an dieser Stelle einfach so dahingestellt – die Performance grottig. Unter dem Aspekt der Wartbarkeit sollte dann allerdings die Frage zulässig sein, warum ein Statement innerhalb der Data Flow Source Task platziert wird?! Wäre es nicht besser, das Statement in einer View, einer Stored Function oder einer Stored Procedure zu verpacken? In diesem Fall kann die View, die Stored Function oder die Stored Procedure über ein Visual Studio Datenbank Projekt und einen Schema Compare schnell als SQL Skript in ein Visual Studio Datenbank Projekt überführt werden und das Skript in ein Quellcode Verwaltungssystem eingecheckt werden.

Bei der Bewertung der Vor und Nachteile von SQL gegenüber SSIS unter dem Gesichtspunkt der Wartbarkeit fällt auch hier die Beurteilung eindeutig zu Gunsten von SQL aus.

Es gibt eine kleine Ausnahme: Sofern sich künftige Änderungen aus Änderungen der Infrastruktur (anderer Datenbank-Provider, verteilte Umgebung, etc.) ergeben, sind Vor- und Nachteile der beiden Technologien im Detail gegeneinander abzuwägen.

Performance

Es gibt zahlreiche Faktoren, die die Performance beeinflussen können und bei einem belastbaren Vergleich zu berücksichtigen sind. Der Einfachheit halber berücksichtige ich diese Faktoren nicht, um letztlich mit den von mir gemessenen Ausführungszeiten meine Überzeugung pauschal untermauern zu können: SSIS ist langsamer als T-SQL wenn ein SSIS Paket bereits einfache Transformationen enthalten, die ohne Probleme auch in einem SQL Statement umgesetzt werden können.

Ein Beispiel hierfür ist bereits das einfache Verjoinen von Tabellen. In SSIS muss ein Datenfluss vor der Verwendung in einer Join Task sortiert werden. Das Sortieren hat zur Folge, dass der Datenfluss nicht mehr asynchron verarbeitet werden kann. Die synchrone Datenverarbeitung eines Joins in SSIS ist ohne Zweifel langsamer als die Verarbeitung innerhalb eines SQL Statements in SQL Server.

Die in diesem Artikel vorgestellte Lösung 3 (nur SSIS) ist im Vergleich zu der Lösung 1 (nur SQL) um den Faktor 2 langsamer. Mir ist bewusst, dass dieses eine äußerst undifferenzierte Aussage ist.

Der Vollständigkeit halber sei allerdings erwähnt, dass es auch Aufgaben gibt, die in SSIS wesentlich besser performen als ein SQL Statement.

Fazit

Pauschal kann die undifferenzierte Aussage getroffen werden, dass komplexe Transformationen, die nur mit SSIS umgesetzt sind, langsamer performen als eine reine SQL Umsetzung.

Funktionsumfang

Dieser Artikel behandelt eine Aufgabe, die unter anderem eindrucksvoll aufzeigt, wie unterschiedlich die Leistungsfähigkeit der gewählten Technologien ist. Was in diesem Beispiel mit einem vergleichsweise einfachen SQL Statement gelöst werden kann, erfordert mit SSIS einen eher komplexen Lösungsansatz. Das soll nun wahrlich kein grundsätzliches Plädoyer für T-SQL sein. Es gibt eine Menge Anforderungen, die mit T-SQL nicht oder nicht so einfach lösbar sind. SSIS ist ungleich flexibler bezüglich Datenquellen, Parallelisierung, Dateioperationen und vieler anderer Aspekte.

Fazit

Sofern eine Aufgabe mit einfach T-SQL lösbar ist, kann und sollte diese mit T-SQL bearbeitet werden. In allen anderen Fällen kommt natürlich SSIS zum Zuge.

Source Code

Die hier gezeigten Beispiele wurden in Visual Studio 2017 in Verbindung mit einer SQL Server 2017 Datenbank entwickelt. Der Source Code zu dem Beispiel kann über den folgenden Link heruntergeladen werden:

Quellcode

Fazit

Mit Bezug zu der Dauer der Entwicklung, der Lesbarkeit und der Wartbarkeit fällt die Wahl einfach. Je mehr SQL, desto schneller, lesbarer und wartbarer ist ein SSIS Paket. Mit Blick auf den Funktionsumfang kann nur eine differenzierte Betrachtung die richtige Antwort liefern. Die Berechnung von Rankings und Hierarchie, mag über T-SQL leichter sein. Die Entwicklung und die Performance einer Fuzzy-Suche hingegen wird in SSIS wesentlich schneller sein. Ähnlich verhält es sich bei der Performance allgemein. Es gibt Anwendungsbereiche, in denen SSIS um Längen besser performt als T-SQL. Immer dann, wenn es um asynchrone Datenverarbeitung geht – wenn also Daten schon geschrieben werden können, während die zu schreibenden Daten noch gelesen werden – ist SSIS den Vorzug zu geben. Das gleiche gilt insbesondere für die Parallelisierung von Datenoperationen. Bei einer synchronen Datenverarbeitung hingegen, wird die Wahl regelmäßig zu Gunsten von T-SQL ausfallen.

Trotz seiner Länge beantwortet dieser Artikel die Frage nach Wie viel SQL darf es denn sein? nur oberflächlich und in Teilen sehr undifferenziert. Mit diesem Artikel möchte ich keine absolute Meinung präsentieren. Der Artikel soll eher als Anregung dienen, den Umfang der Verwendung von SSIS immer kritisch zu hinterfragen.

Die eigentliche Frage sollte daher lauten: Wieviel SSIS soll es denn sein?

Als weiterführende Lektüre kann ich nur den lesenswerten Artikel The Hidden Costs of SSIS: How to Avoid SQL Server Integration Services Gotchas von Michael K. Campbell empfehlen, der die Pros und Cons bei der Wahl der besseren Technologie recht plakativ beschreibt.