Echt schwierig: Performance Problem

Computer: Software & Programmierung SQL & Datenbank - Programmierung
#1

Hallo zusammen

Aus einer Umsatztabelle (tbl_intake) möchte ich eine Zusammenfassung erstellen. Und zwar sollen Kunde, Kundenland, Kundenbetreuer, Verkaufssaison und Produktgruppe gruppiert angezeigt werden.

Das komplizierte daran ist, dass in den Feldern Customer, Country und Productgroup nicht immer der selbe Wert gespeichert ist, obwohl das selbe gemeint wäre. Deshalb gibt es eine Tabelle tbl_valuegroups, welche diese Varianten zusammenfasst.

In der Tabelle tbl_advisormapping wird jeder Kunde-Land-Produktgruppen-Kombination ein Betreuer (=advisor) zugeordnet.

Ich benütze momentan folgende Abfrage:

SELECT a.customer, a.country, a.advisor, i.season, a.productgroup, sum(i.quantity), sum((i.unitprice*i.quantity))
FROM tbl_intake i
LEFT JOIN tbl_advisormapping a
ON (a.customer = (select title from tbl_valuegroups where grouptype=‚customer_intake‘ and level=‚2‘ and item=i.customer)
AND a.country = (select title from tbl_valuegroups where grouptype=‚country‘ and level = ‚2‘ and item=i.country)
AND a.productgroup = (select title from tbl_valuegroups where grouptype=‚productgroup‘ and level=‚2‘ and item = i.productcategory))
GROUP BY a.customer, a.country, a.productgroup

*** Das Problem: ***
Die grosse Umsatztabelle (tbl_intake) enthält ca. 30’000 Einträge. Die ganze Abfrage dauert nun etwa 6-7 Stunden.

Gibt es eine Möglichkeit, die Abfrage anders zu formulieren, so dass ich eine bessere Performance erreiche?

Bei Unklarheiten gehe ich natürlich noch weiter ins Detail. Also einfach fragen.

Danke für eure Mühen.

Gruss,

Janosh

#2

Hallo pumix!

Erste Frage: Welche DB?
Und weiter: Hast du dir den execution plan für den Select schon einmal angesehen? Sind die Bottlenecks dort erkennbar? (Gibt es die richtigen Indices? Werden diese auch verwendet?)

SELECT a.customer, a.country, a.advisor, i.season,
a.productgroup, sum(i.quantity), sum((i.unitprice*i.quantity))
FROM tbl_intake i
LEFT JOIN tbl_advisormapping a
ON (a.customer = (select title from tbl_valuegroups where
grouptype=‚customer_intake‘ and level=‚2‘ and item=i.customer)
AND a.country = (select title from tbl_valuegroups where
grouptype=‚country‘ and level = ‚2‘ and item=i.country)
AND a.productgroup = (select title from tbl_valuegroups where
grouptype=‚productgroup‘ and level=‚2‘ and item =
i.productcategory))
GROUP BY a.customer, a.country, a.productgroup

Deine tbl_valuegroups sieht mir ausgesprochen suspekt aus. Das müsste sich sauberer abbilden lassen… Wieso speichert tbl_intake logisch gleiche Werte mit unterschiedlichen Einträgen ab? Wenn sich das bereinigen ließe hättest du zimlich sicher kein Performance-Problem (insbesondere weil 30.000 Sätze für eine DB, die sich auch so nennen darf keine Aufgabe darstellen sollten).

Gruß
Martin

#3

Hallo Martin

Erste Frage: Welche DB?

mySQL

Und weiter: Hast du dir den execution plan für den Select
schon einmal angesehen? Sind die Bottlenecks dort erkennbar?
(Gibt es die richtigen Indices? Werden diese auch verwendet?)

Nein, hab mir keinen solchen Plan angesehen. Ich weiss auch nicht genau, wie man sich diesen ansehen und wie man dann damit Bottle Necks entdecken kann. Indeces werden leider keine verwendet.

Deine tbl_valuegroups sieht mir ausgesprochen suspekt aus.

Ja, das stimmt. Man könnte das normalisieren. Jedoch würden dann die Abfragen noch komplizierter. Sie enthält einfach Varianten und Gruppierungen. Beispiel:
| Group_Title | Group_Item |
…USA…USA
…USA…U.S.A.
…USA…United States

All diese Item-Varianten können in der tbl_intake auftauchen, weil diese von verschiedenen Stellen und von Hand eingegeben wird. Das ist natürlich ein grosses Problem, das ist mir klar. Des weiteren enthält die tbl_valuegroups auch Gruppierungen, z.b. um verschiedene Unterfirmen zu einem Kunden zu gruppieren.

Gruss,
Janosh

#4

Hallo,

wäre schön, wenn Du dazu noch wüsstest, welche DB Du benutzt:wink:

Gruß

Peter

#5

Hallo, Janosh!

Erste Frage: Welche DB?

mySQL

Sollte auch über 30.000 Datensätze lachen…

Deine tbl_valuegroups sieht mir ausgesprochen suspekt aus.

Ja, das stimmt. Man könnte das normalisieren. Jedoch würden
dann die Abfragen noch komplizierter. Sie enthält einfach
Varianten und Gruppierungen. Beispiel:
| Group_Title | Group_Item |
…USA…USA
…USA…U.S.A.
…USA…United States

Wer so was macht, schubst auch kleine Russenkinder vors Auto. Frage: Müssen die komischen Eingaben erhalten bleiben? Ich würde (schon in der Erfassungsmaske, um genau dieses Drama zu verhindern) nur normalisierte Werte speichern. So was lässt sich auch prima mit einem entsprechenden Datenbankdesign und referenzieller Integrität lösen. Und wenn schon Sch… unschöne Altdaten eingegeben werden, würde ich sie „von Hand“ bereinigen, in dem Du (vor weiteren Abfragen) eine Aktualisierungsabfrage über die Daten jagst. Dadurch ersparst Du Dir zeitfressende Joins, die eh nur Daten normieren.

Dann generell: Verwende am besten Zahl-Schlüsselfelder in der Datenbank und dann auch beim Joinen. Textfelder dauern noch mal länger. (Wenn das gerade nicht geht, versuche bei Gelegenheit, das Datenbankdesign zu re-designen…)

Vielleicht gewinnst Du auch etwas, wenn Du komplexe Zwischenabfragen in eine temporäre Tabelle speicherst und dann auf die zugreifst.

Verwende wenn möglich Indizes auf häufig durchsuchten/gejointen Feldern.

Halte die Zwischenergebnisse von Abfragen immer möglichst klein, d. h. Selektionen möglichst früh, Joins möglichst spät; einschränkende Joins früher als nicht einschränkende.

Alles in allem scheint die Performance durch ein schlechtes Datenbankdesign verursacht.

Gruß, Manfred

#6

Hallo Janosh,

Erste Frage: Welche DB?

mySQL

…execution plan…

Nein, hab mir keinen solchen Plan angesehen. Ich weiss auch
nicht genau, wie man sich diesen ansehen und wie man dann
damit Bottle Necks entdecken kann.

Leider bin ich auf mySQL recht - ähm - unerfahren (ich hab’ schon einmal einen Rehcner gesehen, auf dem eine lief). Da können dir die jeweiligen Experten sicher besser helfen.

Indeces werden leider keine
verwendet.

Wenn ich deinen SELECT noch richtig im Kopf habe bedeutet das dann in etwa 90.000 Full Table Scans der suspekten katastrophalen Tabelle von unten. Dass das dauert wundert mich kaum…

Deine tbl_valuegroups sieht mir ausgesprochen suspekt aus.

Ja, das stimmt. Man könnte das normalisieren.

Das stimmt so nicht ganz: Man muss das normalisieren. Alles andere endet zwangsläufig in einer Katastrophe! Nur mal so zwischengefragt: Wie stellst du sicher, dass auch wirklich jeder Wert in der Valuegroups abgebildet ist? Hast du dir auch schon überlegt, was dein SELECT mit Sätzen macht, wo einer der drei Verweise nicht aufgeht? Von Performance wollen wir da noch gar nicht reden. Dieses Design kann und wird zu logischen Inkonsistenzen in der DB führen.

Jedoch würden
dann die Abfragen noch komplizierter.

Warum? Du ersparst dir die Subselects. Oder verheimlichst du mir da noch was?

Sie enthält einfach
Varianten und Gruppierungen. Beispiel:
| Group_Title | Group_Item |
…USA…USA
…USA…U.S.A.
…USA…United States

Das ist BÖSE!

All diese Item-Varianten können in der tbl_intake auftauchen,
weil diese von verschiedenen Stellen und von Hand eingegeben
wird.

Wenn sich das nicht schon bei der Erfassung ausbessern lässt, dann musst du eben die Datensätze „zwischenspeichern“ und erst dann in deine tbl_intake übernehmen.

Das ist natürlich ein grosses Problem, das ist mir klar.

Das ist kein Problem sondern im Sinne von sauberem DB-Design schlicht und ergreifend falsch… (ok, ich nehme an, du hast mein Flehen vernommen und höre jetzt auf darauf herumzuhacken :wink:)

Des weiteren enthält die tbl_valuegroups auch Gruppierungen,
z.b. um verschiedene Unterfirmen zu einem Kunden zu
gruppieren.

Das ist aber etwas grundlegend anderes (logisch gesehen), als das, was du sonst noch mit der tbl_valuegroups machst (und hat natürlich seine Berechtigung). Es ist schon richtig, dass man logisch verschiedene Daten physisch gleich ablegen kann, aber da sollte man dann schon seeehr genau wissen was man da tut und vor allem warum. Das konsequent fortgeführt könnte man dann ja eigentlich gleich eine DB mit nur einer Tabelle mit 100 Zahlen- und 100 Textspalten machen, da kann man dann alles drin ablegen…

Gruß
Martin

#7

Hallo Martin und Manfred

Vielen Dank für eure ausführlichen Antworten und Erklärungen.

Müssen die komischen Eingaben erhalten bleiben?

Nein, ich werde deshalb diese Bereinigungen beim Import der Daten in tbl_intake durchführen. Bei der Erfassung lässt es sich leider (zur Zeit) nicht beheben.

Verwende wenn möglich Indizes auf häufig durchsuchten/gejointen Feldern.

Grundsätzliche Frage: Was wäre der Nachteil, wenn man einfach auf sämtlichen Feldern (ausser grossen Textfeldern) einen Index hätte?
Und bezüglich ‚gejointe Felder‘: Macht es einen Unterschied, ob ich Einschränkungen in der ON () Klammer angebe, oder später in der WHERE Clause?

Zwischenabfragen in eine temporäre Tabelle speicherst

Genau das hab ich nun mal gemacht, mit PHP als Skriptsprache. Die Abfrage dauert nun noch ca. 30 Sekunden. (Enormer Unterschied!)

Oder verheimlichst du mir da noch was?

Ich glaube nicht, aber es wären dann noch mehr Tabellen betroffen (durch die Normalisierung gäbe es aus der tbl_valuegroups sicher 2 Tabellen) und deshalb wären vielleicht mehr Joins nötig?

Alles in allem scheint die Performance durch ein schlechtes Datenbankdesign verursacht

Wie könnte ich es ändern? Die Betreuer (advisor) werden definiert durch Kundennamen, Kundenland und Produktgruppe.
Das Problem mit den Gruppierungen ist deshalb, weil je nach Statistik, die daraus gemacht werden soll, wieder völlig andere Gruppierungen nötig sind.

Gruss,
Janosh

#8

Hallo, Janosh!

Verwende wenn möglich Indizes auf häufig durchsuchten/gejointen Feldern.

Grundsätzliche Frage: Was wäre der Nachteil, wenn man einfach
auf sämtlichen Feldern (ausser grossen Textfeldern) einen
Index hätte?

Mit jedem Index steigt der Verwaltungsaufwand beim Ändern und Einfügen, weil die Daten quasi redundant in einer Indextabelle abgelegt werden. Im Gegenzug verbessert sich der Zugriff. Dies bringt Dir natürlich nur dann was, wenn Du auch auf das Feld zugreifst. Und grundsätzlich sollten die Werte im Indexfeld auch hinreichend unterschiedlich sein. Wenn Du in einer Tabelle 1000 deutsche Kunden und zwei ausländische hast, hilft Dir der Index auf dem Feld „Land“ auch nicht wirklich weiter.

Grundsätzlich lassen sich solche Aussagen nur sicher treffen, wenn Daten- und Zugriffsmuster hinlänglich bekannt sind. Ich halte es pauschal so: Will ich nach einem Feld des öfteren suchen oder sortieren und sind die Daten ausreichend variabel, gibt’s einen Index. Sonst nicht.

Und bezüglich ‚gejointe Felder‘: Macht es einen Unterschied,
ob ich Einschränkungen in der ON () Klammer angebe, oder
später in der WHERE Clause?

Wahrscheinlich schon. Und das wohl von Datenbank zu Datenbank. Grundsätzlich zerlegen die meisten bis alle Datenbanken die Befehle in einen bestimmten Ablaufplan. Dieser sollte (als Faustregel) eben so optimiert werden, dass die Zwischenmengen an Ergebnissen möglichst klein bleiben.

Grundsätzlich ist ein SELECT t1.f11, t1.f12, t2.f21 FROM t1 INNER JOIN t2 ON t1.key=t2.key gleichbedeutend mit SELECT t1.f11, t1.f12, t2.f21 FROM t1, t2 WHERE t1.key=t2.key. Welches unter der jeweiligen Datenbank performanter ist, solltest Du austesten.

Zwischenabfragen in eine temporäre Tabelle speicherst

Genau das hab ich nun mal gemacht, mit PHP als Skriptsprache.
Die Abfrage dauert nun noch ca. 30 Sekunden. (Enormer
Unterschied!)

Mühsam ernährt sich das Eichhörnchen. Aber irgendwie immer noch zu lang, würde ich anhand der Datenmenge behaupten wollen. Läuft die Abfrage wirklich auf dem Server, oder hängt da irgendwie ein langsames Netz dazwischen?

Alles in allem scheint die Performance durch ein schlechtes Datenbankdesign verursacht

Wie könnte ich es ändern?

Wie findest Du das Design von meiner aktuellen Datenbank:wink:?

Richtig, gar nicht… Dazu müssten wir das Design erst mal kennen. Heißt: Tabellen, Beziehungen, Felddatentypen der wichtigsten Felder, Indizes.

Ich fürchte aber, dass eine grundlegende Änderung anstünde. Und ob dann die anderen Programme noch das machen, was sie sollen, wage ich zu bezweifeln. Die müssten dann auch noch ordentlich angepasst werden. Du hättest aber in jedem Fall eine schönere, sauberere und vor allem konsistente Datenbasis.

Die Betreuer (advisor) werden
definiert durch Kundennamen, Kundenland und Produktgruppe.
Das Problem mit den Gruppierungen ist deshalb, weil je nach
Statistik, die daraus gemacht werden soll, wieder völlig
andere Gruppierungen nötig sind.

Dafür gibt’s ja die entsprechenden Konstrukte, die SQL zur Verfügung stellt. Das hat mit dem DB-Design erst mal nichts direkt zu tun…

Gruß, Manfred

#9

Hallo,

vermutlich arbeitet Dein Server nur noch auf der Platte.

versuche mal folgende Indexe:

valuegroups (grouptype, level, item)
advisorymapping (customer, country, productgroup)

Gruß

Peter

#10

Hallo Manfred

Läuft die Abfrage wirklich auf dem Server, oder hängt
da irgendwie ein langsames Netz dazwischen?

Hmm… läuft übers LAN, müsste also beim Server sein.

Heißt: Tabellen, Beziehungen, Felddatentypen der
wichtigsten Felder, Indizes.

tbl_intake: customer|country|productcode|contractnumber|quantity|unitprice
tbl_advisormapping: advisor|customer|country|productgroup
tbl_valuegroups: title|item|grouptype|level

In der tbl_valuegroups hab ich z.b. unter grouptype=‚productgroup‘ alle möglichen productcodes (welche in tbl_intake vorkommen) als Item gespeichert und dann dazu die entsprechende Produktgruppe (wie sie in tbl_advisormapping vorkommt) im Feld title.

Alle Felder sind vom Typ ‚varchar‘. Jede Tabelle hat zusätzlich noch einen PK ‚id‘ vom Typ int(20) autoincrement.

Die müssten dann auch noch ordentlich angepasst werden.

Bis jetzt laufen noch nicht viele Skripte, welche diese neuen Tabellen benützen. Somit wäre eine Anpassung/Restrukturierung noch möglich.

Dafür gibt’s ja die entsprechenden Konstrukte, die SQL zur
Verfügung stellt.

Was verstehst du unter Konstrukte? Z.b. LEFT JOIN etc.?

Gruss,
Janosh

#11

Hallo pumix

Wenn ich deine Abfrage (auf die Schnelle) richtig lese holst du aus einer Tabelle drei inhaltlich verschiedene Werte. Damit wird alleine das Suchen der jeweiligen „Teile“ der Tabelle ein „Drama“. Also erste Idee: Zumindest die drei „Inhalte“ trennen. Allerdings hilft das nicht wirklich.

Nachdem jetzt schon „unsaubere“ Werte das sind, und ein Re-Design immer lästig ist (aber sicher am besten :wink:) beim Einlesen der Daten (sowas wie eine Stored Procdure der die neuen Werte übergeben werden? kenn nur etwas MS-SQL) direkt die Werte aus der tbl_valuegroups ermittlen und in neue, zusätzliche Felder schreiben. Das ändert erstmal nichts an der Eingabe (die kommt später?) und auc nichts an deinen Abfragen.

Dann die für die schon vorhandenen Datensätze per SQL die neuen Felder der alten Datensätze füllen. Das ist eigentlich analog zum Eintragen beim Anlegen, nur halt für die alten Datensätze.

Alle neuen kriegen dann die notwendigen Einträge automatisch aus deiner TBL und die alten haben sie (einmaliger Aufwand)!

Nun suzessive alle Abfragen auf die neuen Felder umstellen.

Und nun ein Re-Design? oder doch nicht …

mfg
Dirk

#12

Hallo Dirk

Danke für den Tipp. Vermutlich werd ich aber ein Redesign vornehmen müssen und deshalb so oder so alle Daten rumkopieren. *seufz*

Gruss
Janosh

#13

Hallo Peter,

Denkst du, ist es besser jeweils 3 einzelne Indexe zu erstellen, oder jeweils nur einer, welcher alle 3 Spalten enthält? Gibt es da auch so eine Faustregel, wann welche Art besser ist?

Gruss,
Janosh

#14

Ja, es gibt eine FAustregel: theorie ist nichts, ausprobieren alles.
Theoretisch: ein Index über jeweils alle 3 Spalten ist besser.
Praktisch: manchmal wird der komplexere Index vom Optimizer nicht gefunden. Dann sollte man die Einzelindexe erstellen.

Vorschlag: ich würde erst mal die Komplettindexe versuchen. Wenn das klappt, sollte die Abfrage gut 1000 mal schneller sein. Eher mehr!

Gruß

Peter

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#15

Vielen Dank für deine Antwort.

Gruss
Janosh

Ja, es gibt eine FAustregel: theorie ist nichts, ausprobieren
alles.
Theoretisch: ein Index über jeweils alle 3 Spalten ist besser.
Praktisch: manchmal wird der komplexere Index vom Optimizer
nicht gefunden. Dann sollte man die Einzelindexe erstellen.

Vorschlag: ich würde erst mal die Komplettindexe versuchen.
Wenn das klappt, sollte die Abfrage gut 1000 mal schneller
sein. Eher mehr!

Gruß

Peter