Ausführunsplan / SQL Optimizer

[exilant]

Vielen Dank für euer Interesse an meinem Problem. Mit der Erklärung über die Verwendung der Indexe bin ich jedoch nicht einverstanden. Warum mir ein Index auf dem _Feld_ V2FERTIGARTIKEL in der _Tabelle_ V2PS dabei helfen können soll, die passende Ergebnismenge aus gejointen _Tabelle_ V2FERTIGARTIKEL zu holen erschließt sich mir nicht. Tut es im übrigen ja auch nicht wie die Statistik zeigt.

Wenn ich das Query ohne JOIN auf die Auftragstabelle loslasse, also nur die Sätze aus der V2PS die den gewünschten Stati entsprechen Abfrage, bekomme ich den erwarteten Plan und die erwartete Anzahl indexierte Reads:

PLAN (V2PS INDEX (V2PS_IDX1)) -> Das ist der Index auf das Feld STATUS

Statistik: Reads indexed = 864 -> Entspricht der Anzahl der Sätze in der Menge.

Durch Auswerten der WHERE Klausel kommt man schnell drauf, das es auf das Feld STATUS einen Index gibt. Genau den benutzt er auch.

Alles OK.


Gibt es aber das JOIN, so verwendet er keine sinnvollen Indexe mehr. Das zeigt die Statistik eindeutig. Wieso und warum er das tut sei jetzt mal dahingestellt.
Die Indexe der Tabelle geben es geradezu Lehrbuchhaft her, einen optimalen Suchlauf zu starten.

Sowohl die Statistik als auch der Plan zeigen klar, dass der Optimizer seine Arbeit nicht tut.
Er verwendet - obwohl vorhanden - keinen sinnvollen Index und liest bei der einen Tabelle den gesamten Index durch (240.000 indexed reads) und die andere Tabelle komplett natural (7700 not indexed reads).
Klarer kann ein Optimizer wohl nicht scheitern.

Dieses Beispiel finde ich im übrigen noch sehr trivial.Wenn der Optimizer daran scheitert möchte ich nicht wissen was er mit wesentlich komplexeren Abfragen veranstaltet.

Ich werde mir mal ein paar andere Sachen ansehen.

Grüße,
Martin

[Namenloser]

Zitat von exilant:

Vielen Dank für euer Interesse an meinem Problem. Mit der Erklärung über die Verwendung der Indexe bin ich jedoch nicht einverstanden. Warum mir ein Index auf dem _Feld_ V2FERTIGARTIKEL in der _Tabelle_ V2PS dabei helfen können soll, die passende Ergebnismenge aus gejointen _Tabelle_ V2FERTIGARTIKEL zu holen erschließt sich mir nicht.

Der Join ist kommutativ. Und nur nach deinen Tabellengrößen zu urteilen ist es eben effizienter, es genau umgekehrt zu machen, und aus der Tabelle v2ps die passenden Einträge zur Tabelle v2fertigartikel zu holen.

[exilant]

Zitat von Namenloser:

Der Join ist kommutativ. Und nur nach deinen Tabellengrößen zu urteilen ist es eben effizienter, es genau umgekehrt zu machen, und aus der Tabelle v2ps die passenden Einträge zur Tabelle v2fertigartikel zu holen.

Von Effezienz wage ich hier nicht zu reden. Wenn die Effizienz des Optimierers darin besteht, die kleinere von beiden Tabellen "nach links" zu schieben ist das - mit Verlaub - schon sehr schlicht.
Es handelt sich wie gesagt um Tabellen mit lehrbuchmäßigen Indexen und einer trivialen Query. Wenn der Optimizer es für optimal hält einen Index komplett zu lesen und eine andere Tabelle ohne Index "natural" komplett durchzugehen dann kann ich davon ausgehen dass es in der algorithmischen Welt des Optimizers eben nicht optimal zugeht...

@Dejan Vu: Natürlich bin ich hier um zu lernen. Selbstverständlich habe ich an der Qualität von Firebird keine Zweifel. Ich benutze ihn seit es ihn gibt. Und ich bin mehr als zufrieden damit. Weiterhin bin ehrlich dankbar für jede Antwort die ich in diesem Thread erhalten habe und habe mich auch mit jeder Antwort auseinandergesetzt.
Allerdings halte ich mich nicht für verbohrt weil ich das Ergebnis eines SQL Optimizers
für fragwürdig halte.

Grüße,
Martin

[mkinzler]

Dann Teste doch einfach mit 2 größeren Testtabellen, dann siehst Du, ob das Verhalten den wenigen Werten geschuldet ist.

[exilant]

Zitat von mkinzler:

Dann Teste doch einfach mit 2 größeren Testtabellen, dann siehst Du, ob das Verhalten den wenigen Werten geschuldet ist.

Ja. Genau das hat mir tsteinmaurer gestern auch schon nahegelegt. Und das ist sehr sinnvoll.
Und es führt zu einem sehr guten Ergebnis: Ich habe hier eine Datenbank rumliegen in der Messwerte gespeichert werden. Die habe ich zu während des Nachmittags zu testzwecken mal Aktiviert und meine Auftragsdaten reingeschoben (macht in deisem Kontext zwar keinen Sinn, ist aber schön zum testen). Soeben war der Batch fertig. Ich habe dann sofort getestet.

In der DB gibt es eine Tabelle mit der stolzen Anzahl von 74.530.307 Sätzen.
Ein Feld im Messdatensatz enthält einen Schlüssel aus meiner Auftragsdatei (wie gesagt ca. 240.000 Sätze).
Und siehe da: Es wird ein optimaler Plan gebaut.

select mwrestfeuchte.par,
v2ps.status
from mwrestfeuchte
inner join v2ps on (mwrestfeuchte.par=v2ps.par)
where v2ps.status in ('A','U')

Auch hier sind die Indexe lehrbuchmäßig. Und siehe da: Er verwendet für beide Tabellen den optimalen index und die Statistik ist sehr gut aus:

PLAN JOIN (V2PS INDEX (V2PS_IDX1, V2PS_IDX1), MWRESTFEUCHTE INDEX (MWRESTFEUCHTE_IDX1))

Indexed Reads mwrestfeuchte: 4680
Indexed Reads v2ps: 12

Also an Namenloser, Dejan Vu und all' die anderen: Es ist zu vermuten, dass der Optimizer je nach Tabellengröße "abwägt" wann er "wirklich" was tut.

Sehr beruhigend.
Und nochmal vielen Dank an alle.

Grüße,
Martin

[Dejan Vu]

Zitat von exilant:

Von Effezienz wage ich hier nicht zu reden. Wenn die Effizienz des Optimierers darin besteht, die kleinere von beiden Tabellen "nach links" zu schieben ist das - mit Verlaub - schon sehr schlicht.

Die guten Dinge sind immer schlicht.
Woher willst Du denn eigentlich wissen, das das nicht die beste Strategie ist? Du denkst Dir das nur, aber bis ich hier einen Beweis sehe, glaube ich dem Optimizer, ehrlich gesagt, denn der rechnet wenigstens.

Mach doch mal Folgendes: Kopiere die Tabellen einfach und erzeuge die in deinen Augen optimalen Indexe, aber vermeide oder deaktiviere den Index, der in deinen Augen vom Optimizer falsch verwendet wird und bringe den Optimizer einfach dazu, dir zu gehorchen. Dann vergleiche die beiden I/O-Statistiken. Vielleicht wird man schlau draus und tritt die Schlichtheit des Optimizers doch in die Tonne.

Glauben kann Jeder (macht die Menschheit auch überwiegend). Aber *wissen* und *beweisen* machen wenige.

[exilant]

Zitat von Dejan Vu:

Die guten Dinge sind immer schlicht.

Das ist auch in diesem Fall wohl so.

Danke,
Martin

[Dejan Vu]

Der Programmteil im Compiler (der aus einem SELECT einen ausführbare Struktur erzeugt), nennt sich ja 'Optimizer'. Der optimiert wirklich, d.h. er wählt anhand der Index- und I/O-Statistiken (und diversen anderen Werten) den vermeidlich optimalen Ausführungsplan aus. Das geht meist über Brute-Force, d.h. der Algorithmus probiert nicht alle Kombinationen durch. Trotzdem dauert es ein paar ms, bis der Plan fertig ist, weswegen es sich lohnt, seine aus der Anwendungen kommenden Anweisungen per 'prepare' vorzukompilieren. Dann wird einmalig ein Query-Plan angelegt.

[Dejan Vu]

Zitat von exilant:

...Mit der Erklärung ... bin ich jedoch nicht einverstanden. Warum ... erschließt sich mir nicht. ...Wenn der Optimizer daran scheitert ...

Ich würde mir die Frage stellen, wer hier scheitert und wer etwas nicht verstehen kann oder will. Nicht sauer sein, aber vielleicht versuchst Du einmal, die sehr fundierten Ausführungen vom Namenlosen zu verstehen und dir auch zu überlegen, das FB ein ausgereiftes RDBMS ist, das millionenfach im Einsatz ist. Man kann davon ausgehen, das der Optimizer in diesen einfachen Fällen das Richtige macht, Du es aber einfach nicht verstehst. Ich war im Übrigen auch anfangs verwirrt.

Das soll nicht heißen, das Du zu blöd bist (also jetzt wirklich nicht mißverstehen), aber dreh den Spieß einfach mal um, gehe davon aus, das die anderen (Namenloser und der FB-Optimizer) Recht haben und Du dazulernen musst. Ich habe das in dieser Runde auch gemacht.

Erzeuge andere Index und prüfe, wie der Optimizer sich dann verhält. Lies bei Firebird.org, wie der Optimizer das macht (gibts bestimmt irgendwo ein Whitepaper dazu).

Oder vertraue einfach darauf, dass das RDBMS 'das schon machen wird'. Ich arbeite mit SQL-Server und muss nur 1x pro Jahr dem Optimizer mit einem Hint auf die Sprünge helfen (oder die Statistiken mal auf den neuesten Stand bringen). Ansonsten ist mir das doch wurscht, wie der mir die Daten liefert. Wenns nicht sofort kommt, prüfe ich, wieso und tune meine Query. Meistens kann man noch was rauskitzeln. Nur bloß keinen neuen Index (Antipattern: Index shotgun)

[p80286]

Zitat von exilant:

Bei größeren Datenmengen könnte das zum ernsten Problem werden. Ober habe ich da ganz grundsätzlich missverstanden oder übersehen?

Zitat von mkinzler:

Dann Teste doch einfach mit 2 größeren Testtabellen, dann siehst Du, ob das Verhalten den wenigen Werten geschuldet ist.

Zitat von Dejan Vu:

Oder vertraue einfach darauf, dass das RDBMS 'das schon machen wird'.

Ich für meinen Teil hab es aufgegeben, an Abfragen per OptimizerHint herum zu schrauben.
Meist ist der Flaschenhals die übertragene Datenmenge und nicht die Abfrage als solche.
Wenn's mit dem Tempo klemmt, dann sollte man schon schauen, ob das was man da nachfragt auch sinnvoll ist.

Gruß
K-H