Langsames SQL; Wie schneller machen?

[Jan]

Hallo Leute!

Ich habe 3 Tabellen (DB -> MySQL, Auslese mit PHP):

Zum einen eine Kundentabelle, die so aussieht: KID, Firma
Dann eine Gruppentabelle, welche so aussieht: GID, Gruppe
Und dann eine "Beziehungstabelle": BID, GID, KID

Nun kann es eben sein, dass es für eine Firma mehrere Beziehungen gibt, weil sie mehreren Gruppen angehört.

Nun gibt es eine Liste, in der die Firmen den Gruppen entsprechend anders gekennzeichnet werden. Hier greife ich auf 4 Gruppen zu.

Momentan mache ich es so:

SELECT * From Kundentable

Und dann bei jedem Durchlauf:

SELECt * FROM Beziehungstable WHERE KID = FirmenID And (GID = 3 or GID = 7 or GID 11 Or GID 20).

So bekomme ich eine Liste aller Beziehung und kann anhand dieser abprüfen, welchen der gewünschten Gruppen sie angehört oder nicht.

Nun ist der Aufbau der Seite extrem langsam (es listet etwa 200 Firmen auf), und die Kundenanzahl wird in nächster Zeit stark ansteigen.

Meiner Meinung nach liegt es u.a. auch an dieser SQL-Verschachtelung.

Wie könnte ich dies nun in ein SQL zusammenfassen, damit ich in etwa so ein Ergebnis bekomme:

Firma          G1    G2    G3    G4
-----------------------------------
Musterfirma    0     1     1     0

0 stünde hier für "Nein: nicht vorhanden", und 1 eben für "Ja: vorhanden".

Ein Grund, wieso die Seite recht langsam lädt ist wohl auch der, dass die Auflistung als eigenes <table> in einer <td> erfolgt, so erscheint die Liste erst, wenn sie komplett geladen ist, und somit erscheint auch der Rest der Seite erst am Schluss, was während des Ladens (etwa 7 Sekunden) etwas langweilig wirkt.

lg, Jan

[Stefan]

Wie könnte ich dies nun in ein SQL zusammenfassen, damit ich in etwa so ein Ergebnis bekomme:

Firma          G1    G2    G3    G4

Musterfirma    0     1     1     0

0 stünde hier für "Nein: nicht vorhanden", und 1 eben für "Ja: vorhanden".

Kreuztabellenabfragen, wie es so schön in M$ Access heißt, sind soweit mir das bekannt ist in MySQL nicht ohne weiteres realisierbar.
Von daher wirst Du nur über die Verschachtelung arbeiten können, wenn Du Dir nicht eine Funktion für Dein MySQL Server schreibst ...

Wobei ich überleg grad, dass es mit einer fiesen verschachtelten Case When Abfrage doch möglich sein sollte. Ist mir jetzt aber zu spät dafür *g* sorry :)

Vielleicht morgen früh ...

Gruss,
Stefan

[Jan]

Hallo Stefan!

Wobei ich überleg grad, dass es mit einer fiesen verschachtelten Case When Abfrage doch möglich sein sollte. Ist mir jetzt aber zu spät dafür *g* sorry :)

Hab mir grad mal das einfach Beispiel für Case in meinem "NittyGritty SQL"-Buch nachgeschlagen. Bin da Deiner Meinung, dass es - wenn überhaupt - mit einem nicht grad schönen Case When gehen müsste.

Werd mal ein bisschen herumprobieren, wäre aber trotzdem für einen Ansatz dankbar *ggg*

Bis mrogen früh!

lg, Jan

[Stefan]

Hi Jan

Hab mir grad mal das einfach Beispiel für Case in meinem "NittyGritty SQL"-Buch nachgeschlagen. Bin da Deiner Meinung, dass es - wenn überhaupt - mit einem nicht grad schönen Case When gehen müsste.

Also ich habe grade nochmal genauer drüber nachgedacht und des Rätsels Lösung ist eine vereinfachte Form der Case-When (die einfache If) in Verbindung mit einer Summenformel... :o)

Aber bevor ich dazu komme, würde ich gerne noch etwas zu Deinen Tabellen anmerken. Ich gehe ja davon aus, dass die Tabellen noch mehr Felder beinhalten, Du aber nur die relevanten hier hergeschrieben hast. Nur eines ist mir aufgefallen. Du denkst, jede Tabelle muss einen Primärschlüssel haben, der in einem Feld ist, kann das sein? Deine Beziehnungstabelle sieht nämlich durch das Feld BID (=BeziehungsID??) so aus. Hier reichen die Felder KID und GID als zusammengesetzer Primärschlüssel. Das bedeutet: Die Kombination aus KID und GID ist eindeutig und darf nur einmal vorliegen, was auch logisch ist, weil jeder Kunde nur einmal in einer Gruppe drin sein kann, oder? ;) (Entweder er ist drin oder er ist es nicht, drinner als drin wäre wohl vom Thema abgekommen *g*) Und durch Deine Tabelle mit der BID ermöglichst Du genau diesen Fall, das ein Kunde mehrmals in einer Gruppe sein kann. Also nimm das Feld einfach raus :o)

Deine Tabellen sehen dann so aus:

1. Kundentabelle ( KID, Firma );
2. Gruppentabelle ( GID, Gruppenname );
3. Beziehungstabelle ( KID, GID );

Darauf aufbauend (mit diesen Namen) kannst Du für Deine Lösung folgendes SQL Statement ausführen:

SELECT
 Kundentabelle.Firma,
 SUM(IF(Beziehungstabelle.GID=<IDGruppe1>,1,0)),
 SUM(IF(Beziehungstabelle.GID=<IDGruppe2>,1,0)),
 SUM(IF(Beziehungstabelle.GID=<IDGruppe3>,1,0)),
 SUM(IF(Beziehungstabelle.GID=<IDGruppe4>,1,0)),
 COUNT(*)

FROM
 Kundentabelle

LEFT JOIN
 Beziehungstabelle ON Kundentabelle.KID = Beziehungstabelle.KID

GROUP BY
 Kundentabelle.KID

ORDER BY
 Kundentabelle.Firma ASC;

Hinweis: <IDGruppe1> usw. sind natürlich durch die entsprechenden IDs zu ersetzen, bevor das gute Stück ausgeführt wird. Du kannst hinter die Felder auch immer noch ein 'AS <FELDNAME>' setzen, aber das verlangsamt die ganze Sache wieder deswegen lass ich es raus.

Wenn Du dieses Query anwenden willst, dann nutze etwa diesen PHP Code:

<?php

// Datenbankverbindung aufbauen
$db = mysql_connect( <host>:<port>, <user>, <pass> );
      mysql_select_db( <dbname> );

// Query definieren
$query = <<<ENDSQL
SELECT
 Kundentabelle.Firma,
 SUM(IF(Beziehungstabelle.GID=<IDGruppe1>,1,0)),
 SUM(IF(Beziehungstabelle.GID=<IDGruppe2>,1,0)),
 SUM(IF(Beziehungstabelle.GID=<IDGruppe3>,1,0)),
 SUM(IF(Beziehungstabelle.GID=<IDGruppe4>,1,0)),
 COUNT(*)

FROM
 Kundentabelle

LEFT JOIN
 Beziehungstabelle ON Kundentabelle.KID = Beziehungstabelle.KID

GROUP BY
 Kundentabelle.KID

ORDER BY
 Kundentabelle.Firma ASC;
ENDSQL;

// Query ausfuehren
$res = mysql_query( $query, $db );

// Ergebnisliste ausgeben
if( mysql_num_rows( $res ) ) {
 while( list( $firma, $in_gruppe1, $in_gruppe2, $in_gruppe3, $in_gruppe4, $anzahl_gruppen ) = mysql_fetch_row( $res ) ) {
  // Hier kannst Du dann mit den oben erstellten Variablen arbeiten...
 }
}

// Datenbankverbindung schließen
mysql_close( $db );

Hinweis: In der Praxis sieht es dann so aus, dass Du erst den Tabellenkopf erstellst, dann die Ergebniszeilen ausgibst und dann die Tabelle beendest, aber das wirst Du Dir selbst zusammenreimen können :o)

Viele Grüße

Stefan

[nobody]

du benötigst einen einfachen join.

da die GID in der beziehungstabelle enthalten ist (n:m), ist der join auf die tabellen kunden und beziehungen zu machen.

select kundentabelle.* from kundentabelle k inner join beziehungstabelle b on k.kid = b.kid

k.KID = FirmenID And (b.GID = 3 or b.GID = 7 or b.GID 11 Or b.GID 20).

zudem sollte gid und kid indiziert sein!

[Stefan]

du benötigst einen einfachen join.

da die GID in der beziehungstabelle enthalten ist (n:m), ist der join auf die tabellen kunden und beziehungen zu machen.

select kundentabelle.* from kundentabelle k inner join beziehungstabelle b on k.kid = b.kid

k.KID = FirmenID And (b.GID = 3 or b.GID = 7 or b.GID 11 Or b.GID 20).

zudem sollte gid und kid indiziert sein!

In dem Fall würde er ja für jeden Kunden bis zu 4 verschiedene Datensätze bekommen, je nachdem wie vielen Gruppen der Kunde zugehört und auch die Projektion des genannten Querys ist nicht mit dem zu vergleichen was er möchte...

Er wollte doch (oder täusch ich mich?) einen Datensatz pro Kunde/Firma mit 4 Felder in denen alle Gruppen drin sind mit 1 oder 0 für "Mitglied", "Nicht Mitglied"...

Also wenn das der Fall ist, ist die Lösung hier völlig falsch *g*
Die Indezes auf GID und KID sind ja eigentlich durch den Primärschlüssel gegeben...

Ich denk mal drüber nach und bastel dann was :o) (sofern ich nicht total falsch liege)

Gruss
Stefan

[nobody]

sql liefert immer eine 2-dimensionale ergebnismenge in form einer tabelle. der inhalt dieser tabelle wird durch das sql statement spezifiziert.

einen transpose der achsen ist bei sql nicht vorgesehen. dies muß man aus der ergebnismenge selbst erledigen.

allerdings kann man in sql werte umsetzen, so daß bspw. aus einer 3 ein x wird. oder auch doppelte identische zeilen reduzieren.

die frage galt aber in erster linie der performance. hier ist es in jedem fall besser, mehrere tabellen mit sql zu verbinden und eine abfrage auszuführen, als im programm geschachtelt auf mehrere einzeltabellen abzufragen.

wichtig ist es, die anzahl der abfragen zu reduzieren.

im übrigen ist es nicht zwingend, primärschlüssel zu haben. daher galt der hinweis, spalten zu indizieren, welche in der suchbedingung benötigt werden.

[Stefan]

im übrigen ist es nicht zwingend, primärschlüssel zu haben. daher galt der hinweis, spalten zu indizieren, welche in der suchbedingung benötigt werden.

Im Falle eines ID-Feldes ist es zwingend notwendig einen Primärschlüssel zu haben, da das ID Feld sonst doppelt belegt werden könnte und das Datenbanksystem dann nicht der dritten Normalform aus der Normalformenlehre entsprechen würde. Wenn Du trommeln magst, dann bitte richtig.

die frage galt aber in erster linie der performance. hier ist es in jedem fall besser, mehrere tabellen mit sql zu verbinden und eine abfrage auszuführen, als im programm geschachtelt auf mehrere einzeltabellen abzufragen.

Die Performancesteigerung bezog sich in diesem Fall auf ein konkretes Beispiel und die Ergebnismenge aus deiner Idee lässt sich in PHP nur noch zeitaufwendiger verarbeiten, als es die rekursive Abfrage(n) tut/tun. Also schau Dir einfach mal die einfache Lösung an, die ich in einem anderen Posting zu diesem Thread gemacht habe und fühl Dich nicht gleich auf die Füße getreten. Ein Forum ist eben dafür da Lösungen für ein spezielles Problem zu finden und Du bist eben nicht auf das Problem eingegangen und deswegen habe ich Deine Antwort als "falsch" gewertet, wenn sie doch inhaltlich gesehen auf allgemeine Performancefragen als richtig eingestuft hätte werden sollen. Also: Frieden, okay? ;)

Gruss
Stefan

[nobody]

hallo stefan,
es fühlt sich niemand weder angegriffen noch auf den schlips getreten. zudem geht es hier nicht um rechthaberei oder schlaumeierei.

die frage ist einfach eine aufgabenstellung aus der praxis. der fragesteller ist offensichtlich noch nicht so erfahren, die beste lösung für sein problem selbst herauszufinden. daher wandte er sich an dieses forum.

die ddl zwingt nicht zur vergabe eines primärschlüssels oder index. das thema normalisierung ist hier nicht der gegenstand. die tabellen wurden vom fragesteller übrigens richtig aufgebaut. (entities und beziehungen).

die schachtelung von 2 datenbankabfragen in einer schleife kostet grundsätzlich deutlich mehr zeit, als wenn dieses ergebnis mit einer abfrage als join erledigt werden kann.

um die gewünschte achsendrehung zu erreichen, muß der resultset in einer schleife durchlaufen werden, und der transpose aufgebaut werden. das geht nicht anders.

der fragesteller ist sich dieser möglichkeit offensichtlich nicht bewußt. daher habe ich den gedankenanstoß gegeben.

[MudGuard]

Hi,

SELECt * FROM Beziehungstable WHERE KID = FirmenID And (GID = 3 or GID = 7 or GID 11 Or GID 20).

Fehlen da zwei = ?

Dann würde ich das eher als  ... AND GID in (3,7,11,20) schreiben - sieht für mich übersichtlicher aus - ob es effizienter ist, weiß ich nicht.

Firma          G1    G2    G3    G4

Musterfirma    0     1     1     0
0 stünde hier für "Nein: nicht vorhanden", und 1 eben für "Ja: vorhanden".

Hm. Mir ist nicht ganz klar, wie jetzt G1 bis G4 mit den GID zusammenhängen...

cu,
Andreas

[Ilja]

yo,

Zum einen eine Kundentabelle, die so aussieht: KID, Firma
Dann eine Gruppentabelle, welche so aussieht: GID, Gruppe
Und dann eine "Beziehungstabelle": BID, GID, KID

sollte der aufbau der tabelle so bleiben, sprich kommen keine weiteren attribute mehr dazu, kann man alles in einer tabelle zusammenfassen, sprich nur die beziehungstabelle. aber wie gesagt, der aufbau der tabllen sieht s aus, als wenn dort noch mehr attribute dazu kommen.

Nun gibt es eine Liste, in der die Firmen den Gruppen entsprechend anders gekennzeichnet werden. Hier greife ich auf 4 Gruppen zu.

anders gekennzeichnet müsste man genauer spezifizieren, was genau das bedeutet. außerdem muss man diese gruppen nicht unbedinkt in mysql gesondert abfragen, wenn du sowieso alle anzeigen willst, sondern kann die speziellen bei der ausgabe dann entsprechnd kennzeichnen.

Momentan mache ich es so:

SELECT * From Kundentable

Und dann bei jedem Durchlauf:

SELECt * FROM Beziehungstable WHERE KID = FirmenID And (GID = 3 or GID = 7 or GID 11 Or GID 20).

zwei lösungsvorschläge: zum einen direkt die gewüschten spaltennamen angeben und nicht mit dem * arbeiten, das sparrt zeit und bringt bessere performance. der andere tipp wurde bereits erwähnt, mach alles in einer liste ala AND GID IN (3,7,11,20). dinet der besseren übersicht. aber wie oben erwänt, die frage ist, ob du nicht sowieso alle gruppen der kunden auflisten willst und nur eben vier speziell hervorheben. dann wäre diese frage überflüssig. aber das solltest du noch mal darstellen.

Nun ist der Aufbau der Seite extrem langsam (es listet etwa 200 Firmen auf), und die Kundenanzahl wird in nächster Zeit stark ansteigen.

das muss nicht unbedinkt mit der datenbank zu tun haben, der sache solte man näher auf den grund gehen. 200 datensätze sind ehrlich gesagt gar nichts für eine datenbank wie mysql.

Wie könnte ich dies nun in ein SQL zusammenfassen, damit ich in etwa so ein Ergebnis bekomme:

das geht am besten über zwei getrennte abfragen, so wie du es gemacht hast. ein join wie vorgeschlagen würde das ergebnis so nicht hervorbringen, zummal joins in aller regel nicht gerade schnell sind.

0 stünde hier für "Nein: nicht vorhanden", und 1 eben für "Ja: vorhanden".

wenn du die datensätze ausliest, kann ja eine unbestimmte zahl an datensätzen zwischen 0 und 4 zurückgegeben werden. demzufolge würde ich erst mal alle in ein array einlesen und dann beim ausfbau der tabelle die 0 und 1 setzen, je nachdem, ob die gruppe im array vorhanden ist. unter php gibt s dan eine nützliche funktion.

Ilja