Wissen

Query Optimierung in Fabric Warehouse

In modernen Data-Warehouse-Umgebungen stehen wir häufig vor der Herausforderung, Abfragen auf große historisierte Tabellen effizient auszuführen. In unserem Fabric Warehouse arbeiten wir beispielsweise mit rund 150 Quelltabellen, die jeweils einen Gültigkeitstimestamp enthalten. Für die meisten analytischen Fragestellungen benötigen wir jedoch nicht die gesamte Historie, sondern lediglich den aktuell gültigen Eintrag je Datensatz. In diesem Artikel zeigen wir, wie sich die Abfrage umschreiben lässt, um die Sortierung zu vermeiden und damit die Laufzeit deutlich zu verkürzen. Anhand eines Praxisbeispiels mit 46 Millionen Zeilen vergleichen wir verschiedene Ansätze, analysieren die Query-Pläne und bewerten die Performancegewinne.

Ausgangslage

Wir haben in einem DWH ca. 150 historisierte Quelldatentabellen, die für jeden Eintrag einen Gültigkeitstimestamp beinhalten. In den allermeisten Fällen interessiert uns nur der aktuell gültige Eintrag aus der Tabelle. Das haben wir bisher mit folgendem SQL erreicht:

Dabei zählen wir für jeden Key in der Tabelle alle Versionen durch, angefangen mit der neuesten (erkennbar am höchsten Gültigkeitstimestamp _systemModifiedAt). Anschließend wählen wir den 1, und damit aktuellen, Eintrag aus.

Aus dieser Abfrage generiert Fabric Warehouse den folgenden Abfrageplan:

Wir sehen dass zunächst die history Tabelle gescannt, und dann nach [Key] und _systemModifiedAt sortiert wird (Die Sortierspalten sind nicht im Screenshot, man sieht sie indem man in SSMS auf dem Sort Operator rechtsklickt und Properties auswählt).

Ein Sort ist immer eine sehr aufwändige Operation, und wenn man es schafft, sie loszuwerden wird man häufig mit einem massiven Performanceboost belohnt. Also versuchen wir mal unser SQL so umzuschreiben dass wir ohne den Sort auskommen.

Warum haben wir einen Sort in unserem Plan?

Zunächst fällt auf dass wir in der ROW_NUMBER Funktion explizit ein ORDER BY angeben. Auch folgt auf den Sort Operator ein Window Aggregate Operator, der verwendet wird um Windowing Functions zu implementieren (tatsächlich sehen wir die Kombination Sort -> Window Aggregate sogar zweimal in unseren Plan!). Wir müssten also die ROW_NUMBER Funktion loswerden um auch den Sort loszuwerden!

Wie können wir also ohne die ROW_NUMBER Funktion für jeden Key den aktuell gültigen Eintrag auswählen?

Zunächst stellen wir fest, dass der aktuell gültige Eintrag ja gerade der Eintrag mit dem höchsten Timestamp ist.

Man kann also auf den Key gruppieren und den MAX Timestamp auswählen. Das erreichen wir mit folgendem SQL:

So haben wir nun für jeden Key den Timestamp des aktuellen Eintrags. Um jetzt noch alle anderen Spalten zu erhalten können wir einfach das Ergebnis dieser Abfrage an die ganze Tabelle ranjoinen:

Dadurch erhalten wir nun zu jedem Key den aktuell gültigen Eintrag. Der Query Plan hierzu so so aus:

Wir sehen, dass wir die Tabelle nun zweimal scannen, aber dafür beide Sort Operators losgeworden sind. Tatsächlich zieht der obere Index Scan sogar nur zwei Spalten aus der Tabelle, nämlich Key und _systemModifiedAt, so dass dieser Table Scan auch gar nicht mal so aufwendig ist (hier profitieren wir davon dass Fabric Warehouse spaltenbasiert arbeitet).

Wenn ich beide Abfragen ausführe und ihre Laufzeit vergleiche, zeigt sich, dass die erste Abfrage auf dieser Tabelle mit 46 Millionen Zeilen etwa 5 Minuten läuft, während die zweite Abfrage in etwas über 2 Minuten fertig wird. Wir haben also hier die Ladezeit um die Hälfte reduzieren können.

Können wir noch mehr Performance gewinnen?

Nach den beiden Table Scan Operatoren kommt jeweils ein Shuffle. Bei einem Shuffle werden die Zeilen zwischen den verschiedenen Compute Nodes, auf denen die Abfrage ausgeführt wird, hin und her geschickt. Das ist natürlich Aufwand, und gerade der untere Shuffle Operator trägt 85% der geschätzten Kosten. Wenn wir den einsparen könnten, könnten wir nochmal einen Performanceboost erhalten.

 Ein Shuffle wäre vielleicht nicht nötig, wenn das Ergebnis der inneren Abfrage auf allen Compute Nodes zur Verfügung stehen würde. Eine solchen sogenannten Broadcast kann man mit dem REPLICATE Join Hint erzwingen.

Damit erhalten wir folgenden Abfrageplan, der tatsächlich nur noch einen Shuffle Operator enthält.

Wir sehen auch den Broadcast Operator, der die Daten der inneren Abfrage auf alle Compute Nodes verteilt.

Wenn wir die Abfrage allerdings ausführen stellen wir fest, dass die Laufzeit nun einige Sekunden länger ist als in der Version ohne REPLICATE Hint. Schade. Aber einen Versuch war es wert.

Fazit:

Durch das Umschreiben der Abfrage von ROW_NUMBER() hin zu einer Aggregation mit MAX() konnten wir die Sort-Operatoren eliminieren und die Laufzeit auf großen Tabellen von fünf auf etwa zwei Minuten halbieren. Weitere Experimente wie der Einsatz von REPLICATE Hints bringen nicht immer zusätzliche Vorteile, verdeutlichen aber, wie wichtig es ist, Abfragepläne zu analysieren und Optimierungen gezielt zu testen. Insgesamt zeigt sich: schon kleine Anpassungen im SQL können in Fabric Warehouse erhebliche Performanceverbesserungen bewirken.