Ausführunsplan / SQL Optimizer

[Dejan Vu]

Das Feld 'V2FERTIGARTIKEL' wird in der JOIN-Klausel verwendet, oder etwa nicht?? Die Farbe zwar nicht, aber der Index gibt trotzdem eine sortierte Liste der V2FERTIGARTIKEL, da V2FARBE *hinter* V2FERTIGARTIKEL im Index steht. Da kommen dann zwar einige Einträge doppelt vor, aber wenn der Optimizer sonst nichts hat, nimmt er eben diesen Index.

Code:

select ...
from v2ps
 inner join v2fertigartikel 
   on (v2ps.v2fertigartikel=v2fertigartikel.id) -- <<<<<<< Mööööööp
 where v2ps.status in ('U','A')

Vermutlich denkt er, das er damit schneller ans Ziel kommt, als einen anderen Index zu verwenden. Vielleicht ist FB so beschränkt und kann immer nur einen Index pro Verknüpfung verwenden. Keine Ahnung. Auf jeden Fall ist der Index sehr wohl ein Kandidat für eine Optimierung, da eben (zum 3.Mal) das Feld V2FERTIGARTIKEL in der ON-Klauses des JOINs vorkommt. Siehe dazu auch den Pseudocode vom Namenlosen und von mir. Die hätten zwar gerne zwei Indexe, aber einer reicht auch. Dann geht er die Tabelle v2FertigArtikel vielleicht schrittweise durch und sucht -hurtig wie er ist- in dem Index 'V2PS_IDX3' einen passenden Eintrag

Mal wieder Pseudocode:

Code:

Foreach record2 in table v2FertigArtikel do
  if V2PS_IDX3.HasValueForField('V2FERTIGARTIKEL', record2.Id, out recordnumber) then 
    record1 = v2p2.LoadRecord (recordnumber);
    output record1.JoinWith(record2)

Klingt nach einem (Query-)plan. Findest Du nicht?

[Namenloser]

Zitat von exilant:

v2ps -> Betriebsaufträge, 241.954 Datensätze
v2ps.v2fertigartikel = integer not NULL,

v2fertigartikel -> wie der Name sagt: Artikelstammdaten, 7.782 Datensätze
v2fertigartikel.id = integer, natürlich NOT NULL da PK

Mit den Statistiken ist eigentlich klar, was passiert. v2fertigartikel ist sehr viel kleiner als v2ps, also wird es das Effizienteste sein, v2fertigartikel einmal komplett durchzuscannen (der Index nützt dabei nichts) und sich aus v2ps jeweils die passenden Einträge mittels Index zusammenzuklauben.

Man kann ja mal die Laufzeit verschiedener Verfahren mal über den Daumen abschätzen:
Merge-Join (beide Indizes): 7 782 + 241 954 = 249 736
Nested Loop (nur Index über v2fertigartikel): 241 954 * log(7 782) = 941 465
Nested Loop (nur Index über v2ps): 7 782 * log(241 954) = 41 896

Was er da macht ist also schon das Effizienteste. Warum er aber den Index über (V2FARBE, V2FERTIGARTIKEL) statt nur über (V2FERTIGARTIKEL), kann ich dir auch nicht sagen. Vermutlich nimmt er einfach den erstbesten Index, mit dem er etwas anfangen kann.

[Dejan Vu]

Es gibt nur einen Index auf der Tabelle 'v2ps' und einen auf der Tabelle 'v2fertigartikel'. Es gibt keinen Index nur auf der Spalte 'v2ps.V2FertigArtikelId', also bleibt ihm nichts anderes übrig, als diesen V2PS_IDX3 Index zu verwenden. Oder, der exilant hat uns was verheimlicht.

[Namenloser]

Achso, ich dachte ich hätte irgendwo gelesen, dass es noch einen separaten Index gäbe. Aber dann ist das ja auch geklärt.

[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.

[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)

[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.

[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