SQL Conditional Join – Was ist das und warum sollte ich es vermeiden?
Dieser Post ist entstanden aus einem aktuellen Arbeitsthema von mir. Wir haben bei uns eine HANA Datenbank in der wir verschiedene Daten einspielen, aufbereiten und dann über verschiedene Visualisierungstools darstellen können. Dabei müssen wir Daten aus verschiedenen Tabellen und Views miteinander verbinden. Dabei müssen wir dies nun auch manchmal anhand verschiedener Bedingungen verbinden oder verjoinen. Ein sogenannter Conditional Join. Was das genau ist und wo das Problem dabei ist werde ich in diesem Artikel erläutern.
Also was ist nun ein Conditional Join?
Grundsätzlich kann ich 2 Tabellen in SQL mit einem sogenannten Join über einen Schlüssel verbinden. Dabei gibt es z.B. den Inner Join der nur Tabellenzeilen anzeigt bei denen die Schlüssel in beiden Tabellen übereinstimmen.
SELECT A.Primarykey ,A.Field1 ,A.Field2 ,B.Primarykey FROM TABELLE1 AS A INNER JOIN TABELLE2 AS B ON A.Secondarykey = B.Primarykey
In dem Beispiel haben wir 2 Tabellen. Tabelle1 und Tabelle2. Wir haben diese über einen Inner Join verbunden. Der Secondarykey aus der Tabelle1 enthält die Primarykeys aus der Tabelle2. Es werden nur die Werte in dem Ergebnis angezeigt die auch in der Tabelle B vorhanden sind.
Ich hab mal Beispielhaft skizziert wie die Tabellen aussehen könnten:
Wir können sehen, dass anscheinend die Tabelle2 in den verschiedennen Spalten bestimmte Filterkriterien enthalten. Wenn ich also einen Inner Join auf z.B. die Spalte Condkey_3 machen würde. Dann würde ich im Ergebnis nur die Daten aus der ersten Spalte in der Tabelle1 bekommen.
Nun zu dem Problem:
nehmen wir an ich habe einen Event Log mit allen Aktivitäten aufgebaut(Tabelle1). Ich habe verschiedene Prozesse und möchte nun aber für jeden Prozess nur bestimmte Aktivitäten ausweisen. Dazu habe ich dann Tabelle2. In Abhängig von der Condition Spalte möchte ich nur bestimmte Events (e1,e2..) anzeigen lassen
Wenn ihr wissen wollt wozu man sowas gebrauchen könnte empfehle ich euch etwas über Process Mining zu lesen.
Um die Aufgabe aber zu realisieren kommt ein Conditional Join zum Einsatz.
SELECT
A.Primarykey
,A.Field1
,A.Field2
,B.Primarykey
FROM TABELLE1 AS A
INNER JOIN TABELLE2 AS B ON
CASE WHEN A.Condition = 1 THEN B.Condkey1
WHEN A.Condition = 2 THEN B.Condkey2
WHEN A.Condition = 3 THEN B.Condkey3
END = A.Secondarykey
Wie man sehen kann habe ich nun die Join Condition mit einer Case When Funktion erweitert. Nun werden die Daten genau so gefiltert wie ich es möchte und ich kriege das Ergebnis so gefiltert wie ich es möchte.
Ja super aber wo ist denn jetzt das Problem??
Nun das Problem ist nicht in dem Ergebnis, sondern in der Berechnung die von der HANA und auch anderen Datenbanken vorgenommen werden. Wir hatten nicht nur bis zu 10 Datensätze sondern weit über 100 Mio Datensätze in der einen Tabelle und ca. 1000 in der anderen. Plötzlich liefen wir immer in Memory Allocation Errors, oder auf Deutsch: wir wollten mehr Speicher allokieren als wir zur Verfügung hatten. Dabei haben wir durchaus Berechnungen die mit noch mehr Datensätzen hantieren. Also habe ich mich auf die Suche gemacht und dann das Problem gefunden. Bei dem Conditional Join wird nicht einfach pro Datensatz entschieden, sondern es wird die komplette Tabelle1 für jeden einzelnen Datensatz in Tabelle2 in den Speicher genommen. Bei unserem Beispiel bedeutete es, dass wir die 100 Millionen Datensätze 1000 mal in den Speicher nehmen, dann verjoinen und dann wieder auf die <100 Mio Datensätze zusammen dampfen. Zwischenzeitlich haben wir also 1.000.000×1.000 Datensätze im Speicher.
Oder anders 1 Milliarde Datensätze.
Gut das ist doof aber gibt es einen effizienteren Weg das Problem zu lösen?
Ja klar ! und ich hab relativ lang gebraucht um herauszufinden welchen. Am Ende war es ziemlich logisch und erstaunlich leicht. Wenn man mehrere Bedingungen in einem Join hat zum Beispiel über zusammengesetzte Schlüssel dann kann man die Bedingungen verschlüsseln. Dazu gibt es dann die klassischen Operatoren AND und OR. Normalerweise vermeidet man die Nutzung eines OR Operatoren in der Join Condition aber in dem Fall ist es tatsächlich der effizienteste Weg.
SELECT A.Primarykey ,A.Field1 ,A.Field2 ,B.Primarykey FROM TABELLE1 AS A INNER JOIN TABELLE2 AS B ON ((A.Condition = 1 AND A.Secondarykey = B.Condkey1) OR (A.Condition = 2 AND A.Secondarykey = B.Condkey2) OR (A.Condition = 3 AND A.Secondarykey = B.Condkey3) )
Was habe ich nun gemacht? Ich hab einfach eine in Klammern geschachtelte AND Bedingung genommen die zum einen die Condition und den dazugehörigen Schlüssel abprüft. Das habe ich mit jeder Condition gemacht und diese Bedingungen dann über eine OR Verbindung mit einander verbunden.
Das Ergebnis ist das gleiche wie bei der CASE WHEN Condition im oberen Beispiel. Der große Unterschied ist nur, es werden die Datensätze während der Berechnung nicht mit einander multipliziert und in den Speicher genommen. Dadurch ist die Berechnung deutlich Effizienter und die Ressourcen unseres Servers werden nicht so stark belastet.
Ich hoffe ich konnte euch das Problem erläutern und vor allem auch eine Lösung dazu anbieten die eine effizientere Berechnung erlaubt. Ich selber konnte damit die benötigten Ressourcen reduzieren und die Funktionalität unserer Anwendung erhöhen.