SQL-Abfrage mit 2 Joins

[Onkel_Hägi]

Hallo Leute,

ich habe hier folgendes Problem:

Ich soll aus einer Tabelle zwei Uhrzeiten (min und max) rausziehen die zu einer Tour (die ziehe ich mir aus einer anderen Tabelle) und einer Scannart gehören. Zusätzlich soll ich aus der gleichen Tabelle eine andere Uhrzeit rausziehen (auch wieder min) für eine andere Scannart aber die gleiche Tour. Dazu kommt noch die Gesamtmenge der ersten Scannart. Mein SQL sieht aus wie folgt:

select k1.tour as 'Tour',

  min(b2.zeit) as 'kleinste Zeit Scannart Y',

  min(b1.zeit) as 'kleinste Zeit Scannart X',

  max(b1.zeit) as 'größte Zeit Scannart X',

  count(distinct b1.id) as 'Gesamtmenge Scannart X'

from datentabelle b1 

  left join kundentabelle k1 on (b1.nr=k1.nr)

  left join datentabelle b2 on (b2.datum=b1.datum)

where b1.datum='2007-06-08'

  and b1.scannart=X 

  and b2.scannart=Y

  and b2.tour=k1.tour

group by 'Tour'

Wenn ich die Tabelle b2 weglasse läuft das Statement wunderbar, aber sobald ich die Tabelle b2 mit reinnehme braucht die Auswertung Ewigkeiten (habe sie auch schon mal 20 Minuten lang laufen lassen, kam auch nichts)...

Habt Ihr zufällig eine Idee woran das liegen könnte?

Danke schonmal!

Gruß Onkel Hägi

[Jaraz]

Hi,

fehlender Index auf join und where Spalten.

Gruß Jaraz

[Onkel_Hägi]

Die Spalte Datum gehört zum Index dazu, genauso wie die Spalte nr in der Kundentabelle, daran kanns also eigentlich nicht unbedingt liegen...

Noch mehr Ideen?

[Byteloser]

Versuchs mal mit:

select k1.tour as 'Tour',

  min(b2.zeit) as 'kleinste Zeit Scannart Y',

  min(b1.zeit) as 'kleinste Zeit Scannart X',

  max(b1.zeit) as 'größte Zeit Scannart X',

  count(distinct b1.id) as 'Gesamtmenge Scannart X'

from datentabelle b1 

  inner join kundentabelle k1 on (b1.nr=k1.nr)

  inner join datentabelle b2 on (b2.datum=b1.datum AND b2.tour=k1.tour)

where b1.datum='2007-06-08'

  and b1.scannart=X 

  and b2.scannart=Y

group by 'Tour'

Wenn man explizit echte Verknüpfung der Tabellen möchte, sollte man INNER JOIN benutzen. OUTER JOINs sind bei unsachgemässen Gebrauch absolute Performance-Killer.

[Onkel_Hägi]

Bringt leider auch nichts, wenn ich das explain davorsetze bringt er mir bei der Tabelle b2 sogar noch um einiges mehr Datensätze die er durchsuchen möchte als bisher (bisher ca. 4000, jetzt ca. 1,3 Millionen)

[Jasper]

spalte scannart in beiden tabellen indiziert? verteilung der werte innerhalb der spalten, fragen über fragen.

wie sieht der ausführungsplan aus? poste mal den output, dass macht vieles einfacher.

-j

[Onkel_Hägi]

Die Spalte Scannart ist in keiner der Tabellen (ist übrigens die gleiche Tabelle) ein Index, das kann ich aber auch nicht einfach ändern, das würde sich dann mit den anderen Indizes beissen.

Hier der Output vom Explain:

table;type;possible_keys;key;key_len;ref;rows;Extra

b1;ref;idx_datum,idx_kd;idx_datum;3;const;61208;Using where\; Using temporary\; Using filesort

k1;eq_ref;PRIMARY,idx_tour;PRIMARY;8;b1.kunden_nr;1;

b2;ref;idx_datum;idx_datum;3;b1.datum;3864;Using where

Eigentlich siehts ja ganz gut aus finde ich, er durchsucht nicht allzuviele Datensätze, sprich eigentlich isses komisch, dass er kein Ergebnis bringt...

Ich habe gestern auch beim Join auf die b2 versucht mit FORCE INDEX zu arbeiten, aber da bekomme ich immer nen Fehler...

Gruß Hägi

[Onkel_Hägi]

Hat niemand mehr eine Idee?

Das SQL sieht jetzt inzwischen aus wie folgt:

select k1.tour as 'Tour',

  ifnull(min(b2.zeit),'keine Zeit') as 'erste Zeit Scannart Y',

  min(b1.zeit) as 'erste Zeit Scannart X',

  max(b1.zeit) as 'letzte Zeit Scannart X',

  count(distinct b1.send_id) as 'Gesamtmenge Scannart X'

from datentabelle b1 USE INDEX (idx_datum)

  inner join kundentabelle k1 on (b1.nr=k1.nr)

  inner join datentabelle b2 USE INDEX (idx_datum) on (b2.tour=k1.tour) 

where b1.datum='2007-06-08'

  and b2.datum='2007-06-08'

  and b1.scannart=X

  and b2.scannart=Y

group by 1

Das einzige was ich mir noch erklären könnte, wäre dass er die Zuordnung zur ersten Spalte (also der Tour) nicht hinbekommt, da sie bei der Scannart Y anders bestimmt wird als bei der Scannart X. Wobei das kann doch auch net sein, oder? Er geht doch nach dem Wert der dann in der Spalte drinsteht meine ich... Hier noch der Output vom Explain wenn ich den drüberlaufen lasse:

table;type;possible_keys;key;key_len;ref;rows;Extra

b1;ref;idx_datum;idx_datum;3;const;61376;Using where\; Using temporary\; Using filesort

k1;eq_ref;PRIMARY,idx_tour;PRIMARY;8;b1.nr;1;

b2;ref;idx_datum;idx_datum;3;const;61376;Using where

Ich bin mit der Abfrage so langsam echt am verzweifeln... :old

[Jan Jansen]

Für mal nur den Self-Join aus und schau mal wie die Ausgabe/Performance ist. Versuch auch mal ob du "distinct" vermeiden kannst

Ist der Self-Join überhaupt notwendig? Reicht nicht eine einfache Gruppierung?

Wenn gar nichts hilft, schmeiß das Statement weg, setz dich nochmal an die Fachanforderung und schau ob du eventuell fachlich optimieren kannst (die Menge der Datensätze schon früh einschränken und mit den eingeschränkten Ergebnissen den Join auf die anderen Tabellen [mit Subselects arbeiten])

[chill3r]

Warum machst du nicht 2 selects und verbindest die mit 'union all'?

1 select für Tour mit 1. Scannerart und 1 select für Tour mit 2. Scannerart

Find das generell schlimm, dass immer versucht wird so viel wie möglich in 1 select zu packen damit's am Ende keiner mehr versteht

[Jasper]

Warum machst du nicht 2 selects und verbindest die mit 'union all'?

1 select für Tour mit 1. Scannerart und 1 select für Tour mit 2. Scannerart

Find das generell schlimm, dass immer versucht wird so viel wie möglich in 1 select zu packen damit's am Ende keiner mehr versteht

weil 1 statement performanter ist als 2 getrennte, deren resultsets dann noch zusammengeführt werden müssen, wenn beide statements die gleichen daten lesen (wie in diesem fall).

-j