Umfangreiches Select

[André Stahl]

Hallo,

ich habe eine IB-Datenbank mit einigen Tabellen und wenigen Testdatensätzen. Select funktiniert schnell und gut aber folgende Abfrage dauert ewig bzw. hängt sich sogar auf...

<PRE>
select distinct N.ID, N.PLZ, E.KM
from T_NUTZER N
left join V_WIRKSAMENUTZERZEITEN W
left join T_PLZ P
left join T_ENTFERNUNGEN E
left join T_DATEN D
left join T_INTERESSE I
left join T_BEWERTUNGEN BN
on (N.ID=W.NUTZER)
on (N.PLZ=P.PLZ)
on (E.PLZ1=:AKTPLZ) and (N.PLZ=E.PLZ2)
on (N.ID=D.NUTZER)
on (N.ID=I.NUTZER)
on (BN.NUTZER2=:AKTNUTZER) and (BN.NUTZER1=N.ID)
where (E.KM<=:MAXKM) and (N.STATUS=30)
and ((N.TYP='D')or (N.TYP='P'))
and ((D.GEBDATUM>=:MINGEBDATUM) and (D.GEBDATUM<=:MAXGEBDATUM))
order by E.KM, N.PLZ, N.ID
</PRE>

Die Where-Klausel erzeuge ich dynamisch je nach Nutzereinstellungen und kann daher variieren.

Gibt es einen besseren Ansatz? Ich könnte z.B. auch die ersten 4 Tabellen abfragen T_NUTZER bis T_ENTFERNUNGEN und danach die Ergebnisse auf die restlichen Bedingungen prüfen?

Oder erzeugt man besser einzelne Views und verknüpft diese dann miteinander?

Was ist der richtige Ansatz?

Danke von
André

[Chris Stengel]

Hi Andre,

ich wuerde die left joins in joins umwandeln. Du kannst z.B. in den Tabellen T_PLZ, T_ENTFERNUNG etc. 0 Werte einfuegen, und dann joins verwenden.
<br>
Je nach Version verwendet Interbase nur fuer den 1. left join einen Index - ausserdem sind left joins langsam.<p>
Je nachdem, welche Version von Interbase verwendet wird, verwendet Interbase bei der Verknuepfung von mehr als 5 Tabellen (bin mir bei 5 nicht mehr so ganz sicher) keine Indizes mehr fuer die folgenden joins. Es ist dann ein Lotterie-Spiel, welche Tabelle mit einem Index verknuept wird. (war zumindestens bei IB 6.01 so - Firebird 1.5 kann mehr). Ab der 5. Tabelle kann ein Join also von Vorteil sein.<p>
Was Du z.B. nach einem Import von vielen Daten machen solltest, ist ein Backup / Restore - da wird dann die Selectivity der Indices neu berechnet. Auch das kann einen Performance-Gewinn bringen.<p>
Mit isql oder ibexpert kannst Du Dir die verwendeten Indices anzeigen lassen - und somit Performanceprobleme feststellen.

CU Chri

[André Stahl]

Hallo Chris,

auf Left Joins kann ich nicht immer verzichten, da teilweise 0..n-Beziehungen bestehen. Es sind also nicht immer Detaileinträge vorhanden.

Das Verhalten bei Left Join beunruhigt und enttäuscht mich. Unerwartet lieferte folgende Abfrage kein Ergebnis:
<PRE>
select *
from T_NUTZER N
join V_WIRKSAMENUTZERZEITEN W
join T_PLZ P
join T_ENTFERNUNGEN E
join T_DATEN D
left join T_BEWERTUNGEN B
left join T_INTERESSE I
on (B.NUTZER1='10') and (B.NUTZER2=N.ID)
on (N.ID=I.NUTZER)
on (N.ID=W.NUTZER)
on (N.PLZ=P.PLZ)
on (E.PLZ1='00000') and (N.PLZ=E.PLZ2)
on (N.ID=D.NUTZER)
where (E.KM<=100) and (N.STATUS=30)
and ((N.TYP='H')or (N.TYP='D')or (N.TYP='P'))
and ((B.BEWERTUNG=10) or (B.BEWERTUNG is NULL)or (B.BEWERTUNG=20)or (B.BEWERTUNG=30)or (B.BEWERTUNG=40)or (B.BEWERTUNG=50)or (B.BEWERTUNG=60))
and (I.MAIL='J')
order by E.KM, N.PLZ, N.ID
</PRE>

In T_Bewertungen sind keine passenden Datensätze vorhanden und diese werden daher mit <null> gefüllt. In T_Interesse wären zwar Daten enthalten, aber auch hier wird mit <null> aufgefüllt. I.MAIL='J' wird also nie erfüllt, obwohl die Datenbank tatsächlich öfters 'J' enthält.
<PRE>
select *
from T_NUTZER N
join V_WIRKSAMENUTZERZEITEN W
join T_PLZ P
join T_ENTFERNUNGEN E
join T_DATEN D
left join T_INTERESSE I
left join T_BEWERTUNGEN B
on (B.NUTZER1='10') and (B.NUTZER2=N.ID)
on (N.ID=I.NUTZER)
on (N.ID=W.NUTZER)
on (N.PLZ=P.PLZ)
on (E.PLZ1='00000') and (N.PLZ=E.PLZ2)
on (N.ID=D.NUTZER)
where (E.KM<=100) and (N.STATUS=30)
and ((N.TYP='H')or (N.TYP='D')or (N.TYP='P'))
and ((B.BEWERTUNG=10) or (B.BEWERTUNG is NULL)or (B.BEWERTUNG=20)or (B.BEWERTUNG=30)or (B.BEWERTUNG=40)or (B.BEWERTUNG=50)or (B.BEWERTUNG=60))
and (I.MAIL='J')
order by E.KM, N.PLZ, N.ID
</PRE>

Nach dem Austauschen von T_Interesse und T_Bewertungen funktioniert die Abfrage.

Wird eigentlich bei SQL immer ALLES NACH einem Left Join ohne passende Detaildaten mit <null> gefüllt? Ich würde das als Fehler interpretieren!?

Ein enttäuschter und verzweifelter
Andr&#233

[Chris Stengel]

Hi Andre,

warum entaeuscht und verzweifelt? Es gibt fuer alles eine Loesung :-).

> auf Left Joins kann ich nicht immer verzichten, da teilweise 0..n-Beziehungen bestehen. Es sind also nicht immer Detaileinträge vorhanden.

Doch, kannst Du :-)<p>
Siehe mal folgende Tabellen:
<pre>
tab1
id fremd_id wert
------------------------
1 1 Hallo
2 0 Hallo

tab2
id wert
---------------
1 Welt
</pre>
Wenn Du nun tab1 mit tab2 vernuepfen willst, und alle Werte aus tab1 haben willst, wuerdest Du
<pre>
select *
from tab1 t1
left outer join tab2 t2
on t1.fremd_id = t2.id
</pre>
schreiben. Wenn Du nun einen JOIN verwendest, wird Dir nur tab1.id = 1 zurueckgeliefert.
Wenn Du aber in tab2 noch einen 0 Wert reinschreibst:
<pre>
id wert
---------------
0
1 Welt
</pre>
liefert Dir ein Join das korrekte Ergebnis.<p>
> Wird eigentlich bei SQL immer ALLES NACH einem Left Join ohne passende Detaildaten mit <null> gefüllt? Ich würde das als Fehler interpretieren!?

Ja, da dieser Zustand ja undefiniert ist - und null bedeutet ja undefiniert.<p>

Ach ja, bleibt eigentlich nur mein obligatorischer Verweis auf die Firebird Datenbank. Diese hat (in der Version 1.5) einen verbesserten Optimierer, und verwendet auch bei mehreren left outer joins einen index (bei der Anzahl bin ich mir hier aber nicht sicher).

CU Chri

[André Stahl]

Hi Chris,

(ich habe inzwischen zwar eine andere Lösung durch einfache Join-Abfrage und nachträgliche Überprüfung der gefunden Sätze, aber das Problem finde ich schon noch interessant)

Das mit den Detailtabellen (Auffüllen mit 0) funktioniert bei mir nicht. Die Haupttabelle enthält Nutzerdaten und die mehreren Detailtabellen zu jedem Nutzer unterschiedliche untergeordnete Daten (z.B. Aktionen, Zeiträume o.ä.). Es müssen aber NICHT IMMER Subdaten vorhanden sein.

Ich versuch´s mal an einem kleineren Beispiel:

Gegeben ist zB. Tabelle T1 mit Feld I als Schlüsselfeld.
Tabelle T2 (mit Feldern I, A) und T3 (mit Feldern I, B) sind DetailTabellen.
T1 hat 4 Sätze, T1 und T2 haben nicht zu jedem Haupteintrag einen Satz.
<PRE>
T1
1
2
3
4
<BR>

T2
2,J
4,J
<BR>

T3
3,J
4,J
<BR>

select *
from T1
left join T1
left join T2
on T1.I=T2.I
on T1.I=T3.I
<BR>

hätte ich erwartet:
1 null null
2 J null
3 null J
4 J J
<BR>

tatsächlich erhält man:
1 null null
2 J null
3 null NULL
4 J J
<BR>

(nicht getesteter Verallgemeinerungsversuch)
</PRE>

Zumindest bei meiner Einfrage werden enthaltene Daten nicht angezeigt, nur weil ein früherer Left Join keine Daten enthält (hier Satz 3). Das ist für mich absolut unverständlich.

Der 3. Satz Feld B ist aus meiner Sicht nicht undefiniert!?

Stahl

[Mario Rosenbohm]

Hi André,<br><br>

ich glaube da hast du einen Fehler.<br>
(Beispiel T1 T2 T3)
Sollte doch bestimmt<br>
select * <br>
from T1 <br>
left join <b>T2</b> <br>
left join <b>T3</b> <br>
on T1.I=T2.I <br>
on T1.I=T3.I <br>
heissen.<br><br>

doch nun zum eigendlichen Fehler:<br><br>

Bei der obigen Anweisung ist das Ergebnis<br> schlicht und einfach<br><br>
1 null null<br>
2 null null<br>
3 null null<br>
4 J J <br><br>

im Interbase SQL MUß nach jedem "Join" die "on"
Anweisung kommen. Also <br><br>
select * <br>
from T1 <br>
left join <b>T2</b> <br>
on T1.I=T2.I <br>
left join <b>T3</b><br>
on T1.I=T3.I <br>
<br><br>
und schon kommt das gewünschte Ergebnis.<br><br>

1 null null <br>
2 J null <br>
3 null J <br>
4 J J <br><br>

probiere das mal mit deinem großen Select-Anweisung:<br>
<pre>select distinct N.ID, N.PLZ, E.KM
from T_NUTZER N
left join V_WIRKSAMENUTZERZEITEN W
on (N.ID=W.NUTZER)
left join T_PLZ P
on (N.PLZ=P.PLZ)
left join T_ENTFERNUNGEN E
on (E.PLZ1=:AKTPLZ) and (N.PLZ=E.PLZ2)
left join T_DATEN D
on (N.ID=D.NUTZER)
left join T_INTERESSE I
on (N.ID=I.NUTZER)
left join T_BEWERTUNGEN BN
on (BN.NUTZER2=:AKTNUTZER) and (BN.NUTZER1=N.ID)
where (E.KM<=:MAXKM) and (N.STATUS=30)
and ((N.TYP='D')or (N.TYP='P'))
and ((D.GEBDATUM>=:MINGEBDATUM) and (D.GEBDATUM<=:MAXGEBDATUM))
order by E.KM, N.PLZ, N.ID
</pre>
ich denke dann sollte es gehen.<br><br>

Gruß Mari

[André Stahl]

Hallo Mario,

vielen Dank! Genau so geht es :-)

Gruß Andr&#233