Index wird nicht imme rbenutzt

[Dumpfbacke]

Hallo Delphianer,
ich habe hier ein sehr komische Verhalten von Firebird was ich mir nicht erklären kann.
Ich benutzte eine Abfrage und habe mich gewundert warum es so lange dauert. Dann habe ich bemerkt, das hier ein Index nicht benutzt wird. Wenn ich den SQl umstelle wird der Index benutt. Wie kann denn das sein ? Ich haben mal beide Select hier stark gekürtzt und eingefügt. In jeder Tabelle ist ein Index auf Ref un in Tabelle2 zusätzlich noch ein Index auf Auftrag.

Hier wird der Index nicht benutzt:

Delphi-Quellcode:

Select Tabelle1.* from Tabelle1
Left Outer Join Tabelle2 on Tabelle1.Ref = Tabelle2.ReF
where Tabelle2.Auftrag = '66'

Hier wird der Index benutzt und es geht natürlich um einiges schneller als oben

Delphi-Quellcode:

Select Tabelle1.* from Tabelle2
Left Outer Join Tabelle1 on Tabelle2.Ref = Tabelle1.Ref
where Tabelle2.Auftrag = '66'

Als Ergebnis kommen hier genau 5 Datensätze raus welche aus nur bei Version 2 "geladen" werden müssen.
Verstehen kann ich das ganz nicht. Könnte mir das jemand erklären ? Das Ergebnis ist wie gesagt gleich nur die Zeit die es deuert in unterschiedlich da bei der 1. Version kein Index benutzt wird.

Danke Tanja

[Ghostwalker]

Hi,

vorweg, soviel Plan von SQL (geschweige den Firebird) hab ich nicht.

Aber mir glimmt etwas im Hinterkopf, das das etwas mit den Joins zu tun hat. Evtl. mal statt dem outer-join einen inner-join nutzen.

In meiner letzten Firma waren joins nicht sehr gern gesehene Gäste und nur, wenn es nicht anders
ging, genutzt, da sie die last auf dem DB-Server (lt. unserer DB-Spezialisten) start erhöt haben.
Aber das bezog sich primär auf MySQL.

[nahpets]

Da stelle ich mal ein paar Vermutungen an:

Tabelle1 enthält viele Daten.
Tabelle2 enthält wenige Daten.

Die wenigen Daten aus Tabelle2 werden durch die Where-Bedingung noch starkt eingeschränkt.

Im Statement 1 wird für alle (also viele) Sätze aus Tabelle1 nachgeschaut, ob es in Tabelle2 was passendes gibt.

Im Statement 2 wird für wenige Sätze aus Tabelle2 nachgeschaut, ob es was in Tabelle1 gibt.

Die zu verarbeitenden Datenmengen unterscheiden sich hier also (vermutlich) sehr stark, auch wenn die Ergebnisse letztlich übereinstimmen.

Alternative:

SQL-Code:

select * from Tabelle1 
where exists 
(
  select 1 
  from Tabelle2 
  where Tabelle2.Auftrag = '66' 
  and Tabelle1.Ref = Tabelle2.Ref
)

Wobei mir nicht klar ist, wieso ein Left Outer Join, wenn nur Daten aus Tabelle1 benötigt werden?

Flapsig formuliert heißt das doch:

Nimm alles aus Tabelle1 und lege die Gegenstücke von Tabelle2 daneben, egal ob es sie gibt oder nicht, sofern der Auftrag in Tabelle2 gleich 66 ist und zeige mir die Daten aus Tabelle1.

Sinnvoller wäre aber doch:
Nimm alles aus Tabelle2, bei dem Auftrag gleich 66 ist, lege die Gegenstücke aus Tabelle1 daneben und zeige mir die Daten aus Tabelle1.

Übertrage das einfach mal auf Kästen mit Karteikarten und mache das händisch.

Vermutlich wirst Du die 2. Variante wählen, da hier die Vermutung nahe liegt, dass es mit weniger Aufwand zu erledigen sein wird.

Und für die Datenbank scheint es ebenso zu sein.

Wobei mir hier ein Left Outer Join unsinnig erscheint.

Wenn wir alles von Tabelle1 haben wollen und ggfls. alles aus der Tabelle2 daneben legen, sofern es denn existiert, wäre dies nur sinnvoll, wenn es in Tabelle2 keine weitere Einschränkung gibt.
Da hier aber auf Auftrag gleich 66 eingeschränkt wird, muss es ja was in Tabelle2 geben, damit ist der Left absolut überflüssig und ggfls. kontraproduktiv.

Bei mir sähe diese Abfrage ganz altbacken so aus:

SQL-Code:

select Tabelle1.* from Tabelle1, Tabelle2
where Tabelle1.Ref = Tabelle2.Ref
and Tabelle2.Auftrag = '66'

(Nach Oracleerfahrung: Rechts steht die Tabelle, die für die stärkste Einschränkung der Datenmenge sorgt. Keine Ahnung, ob Firebird da ggfls. ähnlich vorgeht.)

Du hast der Datenbank gegenüber aber den "Wissensvorsprung", dass Du weißt, welcher Weg der (vermutlich) einfachere sein wird.

Daher empfiehlt es sich immer, die Abfragen entsprechend aufzubauen.

Gerade bei großen Datenmengen kann man hierdurch schon mal sehr viel Laufzeit (und Arbeitsspeicher / temporär von der Datenbank genutzten Plattenspeicher) einsparen.

Meine praktische Erfahrung aus ein paar Jahrzehnten:

Baue eine Abfrage immer so auf, dass die von der Datenbank zu verarbeitende Datenmenge von vorneherein möglichst gering gehalten wird.
(Auch auf die Gefahr hin, dass eine Abfrage für den Menschen etwas schwieriger lesbar / verstehbar wird. Da helfen dann fundierte Kommentare.)

[TRomano]

@Ghostwalker: Na ja, es entspricht ja wohl dem relationalen Datenmodell, wenn man eben diese Relationen zwischen Tabellen mit Joins nutzt. Es kommt wohl eher darauf an, wie man SQL benutzt und nicht einfach was ausschließt ... DB-Admins beschweren sich immer über "Last" !
Die Nutzung eines Index hängt im Übrigen unter anderem davon ab, wie der Optimizer entscheidet. Wenn er keine hohe Selektivität sieht, dann nutzt er meist einen einfachen Table Scan und eben nicht den Index. Aber um hier einen Ratschlag geben zu können ist die Info-Lage etwas dünn. Zu mindestens kann man sich mit einem FB-Tool auch den Ausführungsplan ansehen.

[jobo]

nahpets hat schon das Wesentliche gesagt, ein Outer JOin mit einer Einschränkung auf der offenen Seite ergibt wenig Sinn, bzw. ist genau zu beleuchten, ob es so Sinn macht oder anders gelöst werden kann. Der Vorschlag, einen normalen Join mit der Auftragseinschränkung zu machen, scheint mir aus der Ferne am sinnvollsten.
Ich tippe mal, dass der Outer Join hier gar nicht benötigt wird und einfach aus "Gewohnheit" oder Mangel an Detailkenntnis verwendet wird.

[p80286]

Pardon, willst Du uns auf den Arm nehmen:

SQL-Code:

Select Tabelle1.* 
from Tabelle1
  Left Outer Join Tabelle2 on Tabelle1.Ref = Tabelle2.ReF
where Tabelle2.Auftrag = '66'

SQL-Code:

Select Tabelle1.* 
from Tabelle2
  Left Outer Join Tabelle1 on Tabelle2.Ref = Tabelle1.Ref
where Tabelle2.Auftrag = '66'

(etwas umgestellt, damit es auffälliger wird)

Das ist doch etwas vollkommen anderes!
Du solltest deine Joins noch einmal überprüfen!

Gruß
K-H

[jobo]

Naja, der Witz ist ja, Du hast Recht, es ist ein vollkommen anderer Join, aber es kann einem identischen Ergebnis führen, hängt von den Daten ab.

Wie gesagt, "Left [Outer] Join" ist hier vermutlich gar nicht gewollt, sondern einfach "Join".

Da der Optimizer zwar einen Unterschied erkennt, nicht aber die "tiefere Bedeutung" des Join, verhält er sich unterschiedlich und nimmt einmal den Index dazu und einmal nicht. (Was auch nichts am Ergebnis ändert)

Es bleibt die Empfehlung, sich mit den verschiedenen Formen von Joins auseinanderzusetzen. Und die Feststellung, dass das 1. Beispiel nicht sinnvoll ist und (vermutlich in Folge) unter einer "schlechten" Optimierung leidet. Es hat eben kein Optimizerprogrammierer diesen (syntaktisch möglichen, aber sinnlosen) Fall ausreichend berücksichtigt.

[p80286]

Das schlimme daran ist ja, daß das Ergebnis "richtig" ist. dann schleicht sich die "falsche" Join-Nutzung ein, und irgendwann kommt das böse Erwachen, "warum denn das, das hat doch bisher immer funktioniert"

(ist nicht bös gemeint, nur bittere Erfahrung aus learning by )

Gruß
K-H

[jobo]

Zitat von p80286:

Das schlimme daran ist ja, daß das Ergebnis "richtig" ist...

Ich nehme an, Du meinst speziell die Nutzung von "Outer Join" obwohl vollständiger Existenz der Joinmenge gegeben ist, wo es nicht auffällt, wenn man -richtiger Weise- einen normalen Join verwendet und so vielleicht nie den Unterschied zum "Outer Join" bemerkt.
Bis eben zu dem Tag, wo eine andere Datenlage entsteht.