Tweet
Hallo zusammen,
0. Handarbeit
ich habe immer wieder recht aufwendige Einzelqueries für Auswertungen. D.h. eine einzelne große/komplexe Abfrage, die an den Server geschickt wird. Zurück kommt ein einzelnes Resultset, in dem dann die fertige Auswertung steckt. Die Performance ist gut und ich bin "eigentlich" zufrieden...
Was dabei nicht so gut ist, ist die Übersichtlichkeit solcher Abfragen und die Wiederverwendbarkeit für andere/ähnliche Auswertungen. Bislang kopiere ich eine bestehende, ähnliche Auswertung und passe sie dann an. Das geht - ist aber nicht so toll. Ich hätte die Sache gerne modularer. D.h. wiederkehrende Teile werden einmal geschrieben und gut getestet. Später kann man diese dann einbinden/verwenden und alles wird übersichtlicher und sauberer. Soweit die Theorie. Was kann man alles machen? Mal schauen:
1. STORED FUNCTIONs
Bestimmte Teile habe ich probeweise in Funktionen ausgelagert. Als einfaches Beispiel: IstSaSoFeiertag(datum). Zurück kommt TRUE/FALSE (1/0). Wie der Name vermuten lässt, prüft diese Funktion, ob das übergebene Datum den Wochentag Samstag oder Sonntag hat und schaut in einer Feiertagstabelle nach, ob in "unserem" Bundesland das Datum auf einen Feiertag fällt. Bislang frage ich das über eine Subquery und WEEKDAY() direkt ab. Das geht genauso, ist aber länger/umständlicher und man kopiert dieses Codefragment umher. Noch schlimmer wird es, wenn Teile X-fach benötigt werden. Z.B. sollen 10 Spalten auf bestimmte Randbedingungen geprüft werden (z.B. für eine nachfolgende Gruppierung/Filterung). Dann wiederholt sich der fast gleiche Code 10-Mal. Und wenn man dann noch Spalte1 * Spalte2 AS Gesamt braucht, dann multipliziert man "zwei Würste" miteinander. Super unübersichtlich. In der Praxis mache ich dann extra Subqueries (bzw. CTEs), nur um mit den Alias-Namen weiter arbeiten zu können. Daher: eine Funktion als Rettung.
Man nimmt dann einfach eine Funktion mit dem Spaltennamen und ein paar notwendigen Parametern und gut ist. Das sieht kompakt und sauber aus, bekommt einen sprechenden Funktionsnamen und man muss nur einmal programmieren (DRY - don't repeat yourself). Alles ist gut. Sollte man meinen...
...bis man auf die jämmerliche Performance stößt. Solange die Funktion nur "wenige" Male aufgerufen wird, ist das super. Wenn man aber sehr viele Datensätze damit bearbeitet, dann bremst das extrem. Zudem kann MySQL dann die verknüpften Indizes nicht mehr benutzen, solange eine Funktion drauf liegt. Resultat bei mir: Faktor 100 bis 1000 langsamer. D.h. das kann ich nicht machen (leider, leider, leider!!!).
2. VIEWs
Wiederkehrende Teile werden in VIEWs gepackt und in der Auswertung muss nur noch mit den VIEWs gearbeitet werden, die alle Daten sinnvoll vorbereiten. Funktioniert, ist übersichtlich und wiederverwendbar. Problem gelöst... sollte man meinen. Problem again: Performance. Es gibt z.B. (noch) keine Indices auf Views bei MySQL. Bei einfacheren Queries geht sogar noch alles glatt (schnell), weil die darunterliegenden Indices (von den verknüpften Tabellen) genutzt werden können. Wenn es komplizierter wird, dann fängt MySQL mit nested subselects an, produziert dafür temporäre Tabelle(n) und die sind dann ohne Indizes => langsam. Auch haben VIEWs gewisse Einschänkungen, die es ohne VIEWs nicht gibt. Bis vor kurzem (vor MySQL 5.7.irgendwas) gingen z.B. nicht mal Subqueries im "FROM". Aktuell gibt's wohl auch noch Einschränkungen bei CTEs. Wie auch immer - es ist kein 1:1 Ersatz.
3. Materialized Views
Wäre eine Alternative, da man dort (je nach DB) Indizes benutzen kann und alles schon vorgecached bereit liegt. MySQL kann das leider nicht. Ich habe das mal manuell gemacht (CREATE TABLE test_materialized_view AS SELECT * FROM test_view / CREATE INDEX ... ON ... etc. pp.) und die Performance ist super. Man hat aber wieder ein komplexeres Szenario und muss sich Gedanken machen, wann und wie oft die Materialized View aktualisiert wird. Und wenn das Aktualisieren mal nicht funktioniert, hat man schnell total veraltete Daten). Es gib noch so Bastelzusätze wie "Flexviews", um Materialized Views in MySQL nachzurüsten. Aber will/muss man sich das wirklich antun? Das ist alles nicht so attraktiv.
4. STORED PROCEDUREs die SQL-Query-Strings erstellen die mit PREPARE/EXECUTE ausgeführt werden
Weitere Idee: Man arbeitet zwar mit Prozeduren und Funktionen, aber diese erstellen nur einen SQL-Query-String, der anschließend an PREPARE und EXECUTE verfüttert wird. Vorteil: Man kann wie oben beschrieben sauber mit Funktionen arbeiten, diese erstellen aber den "ausführlichen" SQL Quelltext, der sich dann auch X-Mal in ähnlicher Form wiederholen darf (wie oben beschrieben). Das Ergebnis ist dann identisch schnell wie bei der handgeschriebenen SQL-Query. Problem gelöst. Oder? Na ja... das macht die Sache wieder extra kompliziert, da man bei Fehlern nicht die entsprechende Stelle findet, sondern erst mal rückwärts suchen muss, von welchem Code das erstellt wurde und dann wiederum dort nachschauen muss, warum das so erstellt wurde. Etc. pp. Klingt nicht nach Spaß! Abgesehen davon hatte ich bei meinen Tests festgestellt, dass mit PREPARE nicht alles umsetzbar ist, was nativ geht (wegen Bug und Feature-Einschränkungen).
5. Anderes DBMS
Ein anderes DBMS verbinden mit mehr Features und dann damit auswerten. Z.B. kann man in Oracle mit "CREATE DATABASE LINK ..." eine andere Datenbank (also MySQL in meinem Fall) als lokales Schema bereitstellen und dann ganz normal damit arbeiten, als lägen die Daten auf der Oracle-Datenbank. Ob das aber von der Geschwindigkeit her überhaupt sinnvoll ist, weiß ich nicht. Aber es wäre möglich... ginge sicherlich... aber... hm... ich weiß nicht so recht...
6. Include von SQL-Dateien
Man bindet andere SQL-Quellen an den entsprechenden Stellen ein als "Includes".
Das geht dann aber nur mit dem mysql-CLI-Tool und ... ist etwas... na ja... grobschlächtig.
7. Skripte (Shell/Batch) verbinden SQL-Files
Wie bei 6. nur dass als Output eine einzelne SQL-Datei übrig bleibt, die ganz normal verwendet werden kann (außerhalb vom mysql-CLI-Tool). Oder Holzhammer: cat include/sub*.sql > main.sql
8. Anderweitige Programmiersprache erstellt SQL-Queries via Templates
Sowas wie PHP, Python, Java... erstellt die Queries und führt sie dann aus. Gibt sich nicht viel zu Punkt 4. Man könnte höchstens über Templates nachdenken, die via DSL (domain specific language) die SQL-Query zusammen bauen. (klingt auch total easy...gib mir 5 Minuten, dann läuft alles für IMMER)
9. Snippets via IDE
Bestimmte Teile werden in einer IDE als "Snippets" (Codefragmente als Vorlage) abgelegt und dann entsprechend an den gewünschten Stellen eingefügt. Besser als Copy+Paste aber an sich auch nicht wirklich sauber/schön.
10. Ich besinne mich zurück und kehre reumütig zu Punkt 0 zurück - denn früher ging es ja auch und war sowieso alles besser! ;-D
Wie macht ihr das? Gibt es noch geschicktere Methoden oder habt ihr noch andere Ideen für mich? Es soll keine Doktorarbeit daraus werden, aber die jetzige Methode (Punkt 0) ist doch sehr rudimentär und ich möchte sehr gerne zukünftig etwas einfacher/übersichtlicher arbeiten, OHNE deutlich länger als jetzt als auf das Auswertungsergebnis warten zu müssen.
Ich brauche lediglich die Quadratur des Kreises. Mehr verlange ich ja gar nicht! 
Mensch jetzt sagt doch auch mal was!
0. Handarbeit
ich habe immer wieder recht aufwendige Einzelqueries für Auswertungen. D.h. eine einzelne große/komplexe Abfrage, die an den Server geschickt wird. Zurück kommt ein einzelnes Resultset, in dem dann die fertige Auswertung steckt. Die Performance ist gut und ich bin "eigentlich" zufrieden...
Was dabei nicht so gut ist, ist die Übersichtlichkeit solcher Abfragen und die Wiederverwendbarkeit für andere/ähnliche Auswertungen. Bislang kopiere ich eine bestehende, ähnliche Auswertung und passe sie dann an. Das geht - ist aber nicht so toll. Ich hätte die Sache gerne modularer. D.h. wiederkehrende Teile werden einmal geschrieben und gut getestet. Später kann man diese dann einbinden/verwenden und alles wird übersichtlicher und sauberer. Soweit die Theorie. Was kann man alles machen? Mal schauen:
1. STORED FUNCTIONs
Bestimmte Teile habe ich probeweise in Funktionen ausgelagert. Als einfaches Beispiel: IstSaSoFeiertag(datum). Zurück kommt TRUE/FALSE (1/0). Wie der Name vermuten lässt, prüft diese Funktion, ob das übergebene Datum den Wochentag Samstag oder Sonntag hat und schaut in einer Feiertagstabelle nach, ob in "unserem" Bundesland das Datum auf einen Feiertag fällt. Bislang frage ich das über eine Subquery und WEEKDAY() direkt ab. Das geht genauso, ist aber länger/umständlicher und man kopiert dieses Codefragment umher. Noch schlimmer wird es, wenn Teile X-fach benötigt werden. Z.B. sollen 10 Spalten auf bestimmte Randbedingungen geprüft werden (z.B. für eine nachfolgende Gruppierung/Filterung). Dann wiederholt sich der fast gleiche Code 10-Mal. Und wenn man dann noch Spalte1 * Spalte2 AS Gesamt braucht, dann multipliziert man "zwei Würste" miteinander. Super unübersichtlich. In der Praxis mache ich dann extra Subqueries (bzw. CTEs), nur um mit den Alias-Namen weiter arbeiten zu können. Daher: eine Funktion als Rettung.
Man nimmt dann einfach eine Funktion mit dem Spaltennamen und ein paar notwendigen Parametern und gut ist. Das sieht kompakt und sauber aus, bekommt einen sprechenden Funktionsnamen und man muss nur einmal programmieren (DRY - don't repeat yourself). Alles ist gut. Sollte man meinen...
...bis man auf die jämmerliche Performance stößt. Solange die Funktion nur "wenige" Male aufgerufen wird, ist das super. Wenn man aber sehr viele Datensätze damit bearbeitet, dann bremst das extrem. Zudem kann MySQL dann die verknüpften Indizes nicht mehr benutzen, solange eine Funktion drauf liegt. Resultat bei mir: Faktor 100 bis 1000 langsamer. D.h. das kann ich nicht machen (leider, leider, leider!!!).
2. VIEWs
Wiederkehrende Teile werden in VIEWs gepackt und in der Auswertung muss nur noch mit den VIEWs gearbeitet werden, die alle Daten sinnvoll vorbereiten. Funktioniert, ist übersichtlich und wiederverwendbar. Problem gelöst... sollte man meinen. Problem again: Performance. Es gibt z.B. (noch) keine Indices auf Views bei MySQL. Bei einfacheren Queries geht sogar noch alles glatt (schnell), weil die darunterliegenden Indices (von den verknüpften Tabellen) genutzt werden können. Wenn es komplizierter wird, dann fängt MySQL mit nested subselects an, produziert dafür temporäre Tabelle(n) und die sind dann ohne Indizes => langsam. Auch haben VIEWs gewisse Einschänkungen, die es ohne VIEWs nicht gibt. Bis vor kurzem (vor MySQL 5.7.irgendwas) gingen z.B. nicht mal Subqueries im "FROM". Aktuell gibt's wohl auch noch Einschränkungen bei CTEs. Wie auch immer - es ist kein 1:1 Ersatz.
3. Materialized Views
Wäre eine Alternative, da man dort (je nach DB) Indizes benutzen kann und alles schon vorgecached bereit liegt. MySQL kann das leider nicht. Ich habe das mal manuell gemacht (CREATE TABLE test_materialized_view AS SELECT * FROM test_view / CREATE INDEX ... ON ... etc. pp.) und die Performance ist super. Man hat aber wieder ein komplexeres Szenario und muss sich Gedanken machen, wann und wie oft die Materialized View aktualisiert wird. Und wenn das Aktualisieren mal nicht funktioniert, hat man schnell total veraltete Daten). Es gib noch so Bastelzusätze wie "Flexviews", um Materialized Views in MySQL nachzurüsten. Aber will/muss man sich das wirklich antun? Das ist alles nicht so attraktiv.
4. STORED PROCEDUREs die SQL-Query-Strings erstellen die mit PREPARE/EXECUTE ausgeführt werden
Weitere Idee: Man arbeitet zwar mit Prozeduren und Funktionen, aber diese erstellen nur einen SQL-Query-String, der anschließend an PREPARE und EXECUTE verfüttert wird. Vorteil: Man kann wie oben beschrieben sauber mit Funktionen arbeiten, diese erstellen aber den "ausführlichen" SQL Quelltext, der sich dann auch X-Mal in ähnlicher Form wiederholen darf (wie oben beschrieben). Das Ergebnis ist dann identisch schnell wie bei der handgeschriebenen SQL-Query. Problem gelöst. Oder? Na ja... das macht die Sache wieder extra kompliziert, da man bei Fehlern nicht die entsprechende Stelle findet, sondern erst mal rückwärts suchen muss, von welchem Code das erstellt wurde und dann wiederum dort nachschauen muss, warum das so erstellt wurde. Etc. pp. Klingt nicht nach Spaß! Abgesehen davon hatte ich bei meinen Tests festgestellt, dass mit PREPARE nicht alles umsetzbar ist, was nativ geht (wegen Bug und Feature-Einschränkungen).
5. Anderes DBMS
Ein anderes DBMS verbinden mit mehr Features und dann damit auswerten. Z.B. kann man in Oracle mit "CREATE DATABASE LINK ..." eine andere Datenbank (also MySQL in meinem Fall) als lokales Schema bereitstellen und dann ganz normal damit arbeiten, als lägen die Daten auf der Oracle-Datenbank. Ob das aber von der Geschwindigkeit her überhaupt sinnvoll ist, weiß ich nicht. Aber es wäre möglich... ginge sicherlich... aber... hm... ich weiß nicht so recht...
6. Include von SQL-Dateien
Man bindet andere SQL-Quellen an den entsprechenden Stellen ein als "Includes".
Code:
In der main.sql: source include/sub1.sql source include/sub2.sql source include/sub3.sql
7. Skripte (Shell/Batch) verbinden SQL-Files
Wie bei 6. nur dass als Output eine einzelne SQL-Datei übrig bleibt, die ganz normal verwendet werden kann (außerhalb vom mysql-CLI-Tool). Oder Holzhammer: cat include/sub*.sql > main.sql
8. Anderweitige Programmiersprache erstellt SQL-Queries via Templates
Sowas wie PHP, Python, Java... erstellt die Queries und führt sie dann aus. Gibt sich nicht viel zu Punkt 4. Man könnte höchstens über Templates nachdenken, die via DSL (domain specific language) die SQL-Query zusammen bauen. (klingt auch total easy...gib mir 5 Minuten, dann läuft alles für IMMER)
9. Snippets via IDE
Bestimmte Teile werden in einer IDE als "Snippets" (Codefragmente als Vorlage) abgelegt und dann entsprechend an den gewünschten Stellen eingefügt. Besser als Copy+Paste aber an sich auch nicht wirklich sauber/schön.
10. Ich besinne mich zurück und kehre reumütig zu Punkt 0 zurück - denn früher ging es ja auch und war sowieso alles besser! ;-D
Wie macht ihr das? Gibt es noch geschicktere Methoden oder habt ihr noch andere Ideen für mich? Es soll keine Doktorarbeit daraus werden, aber die jetzige Methode (Punkt 0) ist doch sehr rudimentär und ich möchte sehr gerne zukünftig etwas einfacher/übersichtlicher arbeiten, OHNE deutlich länger als jetzt als auf das Auswertungsergebnis warten zu müssen.
Ich brauche lediglich die Quadratur des Kreises. Mehr verlange ich ja gar nicht! Mensch jetzt sagt doch auch mal was!
Comment