Performance-Optimierung von mehrfachen 1:n-Joins sinnvoll?

[Osterberger]

Hallo,

arbeite momentan bei einem größeren Projekt mit einer relativ umfangreichen Datenbank mit und habe eine allgemeine Frage zur Optimierung von SQL-Statements bei one-to-many-Beziehungen.

Es gibt sehr viele Situationen, in denen 2 oder mehr Tabellen gejoint werden müssen und eine Ausgabe der folgenden Form erzeugt werden soll:


Tabellen in diesem Beispiel: Kunden (beordern) Bestellungen (beinhalten) Produkte

Kunde 1:
   Bestellung 1:
      Produkt 1, Produkt 2, Produkt 3…
   Bestellung 2:
      Produkt 4, Produkt 5, Produkt 6…

Kunde 2:
   Bestellung 3:
      Produkt 3, Produkt 4, Produkt 5…

Man joint hier in der Regel (?) alle drei Tabellen, sortiert das Ergebnis nach Kundennummer, dann nach Bestellungs-ID und schließlich nach Produkt-ID und durchläuft dieses Ergebnis dann unter Verwendung von Hilfsvariablen mit denen überprüft werden kann ob ich mich in der Iteration noch bei der aktuellen Bestellung bzw. beim aktuellen Kunden befinde oder ich die nächste Überschrift ausgeben muss (wenn es dafür eine bessere Praktik gibt, wäre ich für Hinweise übrigens sehr dankbar).

In diesem stark vereinfachten Beispiel mag das kein Problem darstellen. Da bei meinem Projekt jedoch die Tabellen sowohl horizontal als auch vertikal sehr umfangreich sind, habe ich die (evtl. unbegründete) Sorge, dass durch die Joins eine riesige Datenmenge entsteht, insbesondere weil die Daten der "linken" Tabellen ja so oft im Ergebnis kopiert auftauchen wie es entsprechende Einträge in den rechts davon gejointen Tabellen gibt, obwohl man die Daten ja eigentlich nur ein einziges Mal zurückgeben müsste, nämlich dann wenn sich der Wert in der linken Tabelle ändert.

Meine Frage ist nun: Ist es unnötig, in diese Richtung zu optimieren, obwohl teilweise 5 Tabellen auf diese Art gejoint werden müssen und eine Zeile sehr große Datenmengen enthält? Optimal wäre so etwas wie ein Array als Spalte in einem SQL-Resultat zurückzugeben, was aber meines Wissens nur sehr unschön mit GROUP_CONCAT möglich wäre.

Danke für alle Antworten und viele Grüße

[SebTM]

Hallo,

ich an deiner Stelle würde das "Pferd" mal von hinten aufrollen und ggf. Performance-Tests durchführen die dir zahlenmäßig auch ein Gefühl geben was diese Queries ausmachen. Dann könntest du noch überlegen ob es ggf. sinnvoll wäre - das du doch mehrere Queries sendest um die Menge an doppelten Daten zu verringern.

Ich bin mir in diesem falls selbst nicht sicher, was hier "Best practice" wäre - das waren jetzt mal so meine Gedankengänge zu deinen Schilderungen!

[akretschmer]

Man joint hier in der Regel (?) alle drei Tabellen, sortiert das Ergebnis nach Kundennummer, dann nach Bestellungs-ID und schließlich nach Produkt-ID und durchläuft dieses Ergebnis dann unter Verwendung von Hilfsvariablen mit denen überprüft werden kann ob ich mich in der Iteration noch bei der aktuellen Bestellung bzw. beim aktuellen Kunden befinde oder ich die nächste Überschrift ausgeben muss (wenn es dafür eine bessere Praktik gibt, wäre ich für Hinweise übrigens sehr dankbar).

Du kannst eher schon aggregieren.

In diesem stark vereinfachten Beispiel mag das kein Problem darstellen. Da bei meinem Projekt jedoch die Tabellen sowohl horizontal als auch vertikal sehr umfangreich sind,

Horizontal? Möglicherweise stimmt dein Design nicht. Unabhängig davon: wie viele Spalten sind im Resultat?

Meine Frage ist nun: Ist es unnötig, in diese Richtung zu optimieren, obwohl teilweise 5 Tabellen auf diese Art gejoint werden müssen und eine Zeile sehr große Datenmengen enthält? Optimal wäre so etwas wie ein Array als Spalte in einem SQL-Resultat zurückzugeben,

array_agg() existiert und darf genutzt werden.

was aber meines Wissens nur sehr unschön mit GROUP_CONCAT möglich wäre.

Kenne ich nicht.

[VPh]

Weiß nicht ob ich's richtig verstanden habe, die Aussage sieht etwa so aus?

Kunde | Bestellung | Produkt | Produkt...
Hans   |                  |             |
          | Bestellung 1|
          |                   | Handschuhe | Schneeschaufel | ...

oder eher

Kunde | Bestellung | Produkt | Produkt...
Hans   | Bestellung 1| Handschuhe | Schneeschaufel | ...

In diesem stark vereinfachten Beispiel mag das kein Problem darstellen.

Um das eigentliche Problem nachvollziehen zu können, sollte die Beschreibung zum Problemfall natürlich so passend wie möglich sein.
Würde mich sehr über ein kleines Beispiel freuen: http://sqlfiddle.com

[Osterberger]

Sorry, habe mich wohl nicht klar ausgedrückt. Also es geht weniger um meinen konkreten Problemfall an sich als um die Nebeneffekte die ganz allgemein Joins mit sich bringen:

http://sqlfiddle.com/#!2/d4836/1

Wenn man diese Abfrage ausführt, sieht man, dass z.B. im Ergebnis links sowohl die Daten für Max als auch für Peter mehrmals auftauchen. Ist ja soweit logisch, so funktioniert ein Join eben. Nun muss man sich aber vorstellen, dass die Kunden-Tabelle noch viel mehr Spalten enthält (Metadaten, die auch alle benötigt werden), so ca. 10-15 Spalten mehr die teilweise Volltext sind. Somit würden noch viel mehr Daten für jede Bestellung kopiert werden, obwohl man bei der sortierten Ausgabe ja die Daten eigentlich nur dann braucht, wenn sich der Kunde ändert. Wenn man sich jetzt noch vorstellt, dass eine dritte Tabelle "Produkte" dazu kommt, würde sich die Datenmenge erneut multiplizieren.

@akretschmer: Danke für den Hinweis, aber array_agg gibt es wohl nur unter Postgre, richtig? Ich benötige eine Lösung für MariaDB bzw. MySQL. GROUP_CONCAT funktioniert ähnlich, gibt aber einen String zurück den man dann auf Softwareseite erst als Array interpretieren muss und das ist ziemlich unschön.

@SebTM: Ja, Performance-Tests müssen noch durchgeführt werden, ich habe nur im Voraus ein ungutes Gefühl bekommen beim Gedanken daran, dass so große Datenmengen zurückgegeben werden müssen.

@VPh: Die Ausgabe sollte einfach gruppiert erfolgen, d.h. das SQL-Resultat wird nach Kunde, dann nach Bestellung und dann nach Produkt sortiert und immer wenn sich in der Iteration die Bestellung bzw. der Kunde ändert wird die aktuelle Gruppe verlassen und eine neue mit Überschrift usw. begonnen. Also eine Art hierarchische Strutktur.

Ich weiß nicht, ob und wie dieses Problem im allgemeinen Konsensus behandelt wird, vielleicht mache ich mir ja auch unnötig Sorgen aufgrund von internen Optimierungen der Schnittstelle oder einer kompletten Fehleinschätzung meinerseits etc.

[akretschmer]

Ich weiß nicht, ob und wie dieses Problem

Ich seh ganz andere Probleme:

• keine Foreign key Constraints
• select *
• NULL-Werte in Primary Keys
• schrottige Datentypen (timestamp als INT)

[VPh]

Wenn man diese Abfrage ausführt, sieht man, dass z.B. im Ergebnis links sowohl die Daten für Max als auch für Peter mehrmals auftauchen. Ist ja soweit logisch, so funktioniert ein Join eben. Nun muss man sich aber vorstellen, dass die Kunden-Tabelle noch viel mehr Spalten enthält (Metadaten, die auch alle benötigt werden), so ca. 10-15 Spalten mehr die teilweise Volltext sind.

Die Metadaten würde ich dann wohl gesondert selektieren, die müssen wirklich nicht zusammen mit den Bestellungen in einem Resultset stecken.

Aber so richtig habe ich noch nicht verstanden, worum es hier denn eigentlich geht. Auf Joins lässt sich nicht verzichten(MySQL). Wenn da Unmengen an Daten zurück kommen, stecken vielleicht unnötige Daten in der Projektion, z.B. Metadaten in jeder Zeile des Ergebnisses.

[Osterberger]

@akretschmer: Das Schema wird so natürlich nicht eingesetzt, mir ging es nur darum das Prinzip aufzuzeigen dass Daten beim Join mehrfach zurückgegeben werden.

@VPh: Auf Joins kann man natürlich nicht verzichten, jedoch könnte man beispielsweise die Foreign-Keys erst aus dem einen Resultset auslesen und danach eine weitere Abfrage nach den zugehörigen Datensätzen senden und so auf ein oder mehrere Joins in der Hauptabfrage verzichten. Oder man könnte - wie bereits angesprochen - eine Aggregatfunktion verwenden um eine Liste in einen einzelnen Wert zu komprimieren. Die zusätzlichen Daten sind dabei nicht unnötig, das Schema ist vollständig normalisiert.

[erc]

In welchem Szenario wird das eigentlich zum Problem? Die Datenbank wird sicherlich nicht zusammenbrechen wenn das Resultset statt 20kb 50kb groß ist...

[Osterberger]

Heute hatte ich wieder so einen Fall, ich hoffe das Beispiel macht es jetzt klarer: Es gibt eine Tabelle mit Produkten, die entsprechend lange Datensätze aufweist. DIese Produkte sind allerdings in eine hierarchische Baumstruktur eingeordnet, d.h. jedes Produkt ist einem Baumknoten zugewiesen. Der Baum ist in einer separaten Tabelle mittels Nested Sets realisiert. Nun wird eine bestimmte Abfrage gestellt, wodurch mehrere Produkte ausgegeben werden sollen, allerdings inklusive dem kompletten Pfad dorthin.

Beispielausgabe:

Produkt 1: ……………
Drogiere > Körperpflege > Haarpflege

Produkt 2: ……………
Küchenbedarf > Geschirr > Töpfe

usw.

Dazu muss natürlich für jedes Produkt aus dem Ergebnis noch der Pfad über den Baum ausgelesen werden. Hierzu lese ich aus dem Ergebnis alle benötigten Baumknoten-IDs aus und stelle eine zweite Abfrage an dem Baum, deren Ergebnis ich dann in ein assoziatives Array einlese, das jedem Endknoten ein Array mit den Elternknoten zuordnet.

Die Alternative wäre jetzt, dass man diese Baumabfrage noch in die erste Abfrage mit integriert, hierdurch würde jedes Produkt 3x (oder so tief wie eben die Kategorien-Verschaltelung ist) kopiert auftauchen nur damit noch die 3 Kategorietitel mit dabei sind. Abgesehen davon wäre es auch nicht so bequem auszulesen und es entsteht dabei auch noch zusätzliche Redundanz wenn mehrere Produkte im selben Baumknoten sind. DIe dritte Möglichkeit wäre GROUP_CONCAT, was aber extrem hässlich wird sobald man nicht nur Strings sondern ganze Objekte aggregieren will.

Bei sowas frage ich mich oft, wie man das am geschicktesten und appetitlichsten lösen sollte.