Ausführunsplan / SQL Optimizer

[exilant]

Hallo zusammen,

ich habe da ein Problem mit einer harmlosen Query:

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

Ein ganz simpler Fall. Betriebsaufträge bestimmter Stati werden mit Artikelstammdaten verknüpft. Selbstverständlich funktioniert diese Abfrage und liefert das korrekte Ergebnis. Aber wenn ich mir den Ausführungsplan in IBExpert ansehe steht da:

Plan:
PLAN JOIN (V2FERTIGARTIKEL NATURAL, V2PS INDEX (V2PS_IDX3))

NATURAL??? In der Tabelle V2FERTIGARTIKEL ist das Feld ID vom Typ integer und der Primärschlüssel. Der dazugehörige Index heisst RDB$PRIMARY46. Warum wird dieser Index nicht benutzt? Bei größeren Datenmengen könnte das zum ernsten Problem werden. Ober habe ich da ganz grundsätzlich missverstanden oder übersehen?

Vielen Dank,
Martin

[gmc616]

Ist v2ps.v2fertigartikel ebenfalls integer? Liegt dort ebenfalls ein index drüber?

Oder versuch doch mal so:

Code:

select 
    v2ps.par,
    v2ps.v2fertigartikel,
    v2fertigartikel.artikelbezeichnung
from 
    v2ps,v2fertigartikel 
where 
    v2ps.status in ('U','A')
and v2ps.v2fertigartikel=v2fertigartikel.id

[exilant]

@gmc616: Danke für deine Antwort. Natürlich ist das Feld vom Typ integer. Leider nicht als FK definiert obwohl es an dieser Stelle Sinn machen würde. Aber das sollte keinen Einfluss auf den Optimizer haben.
Deine Abfrage führt zum gleichen Plan. Es ist also egal ob mit JOIN oder WHERE die Tabellen verbinde. Es bleibt bei NATURAL.
Ich bin echt ratlos.

[gmc616]

Sorry, ich bin in FB nicht fit, nutze ihn garnicht.

Die Überlegung war, dass evtl. die beiden IDs unterschiedliche Integer (smallint, longint etc.) sein könnten und deswegen FB einen eigenen Index zusammen baut.
Die Erfahrung zeigt auch, das manchmal ein einfaches umbauen des Statements die SQL-Engine dazu veranlasst, anders zu arbeiten ( z.B. die andere Tabelle indiziert ).

z.B. einfach mal umdrehen

Code:

v2fertigartikel.id=v2ps.v2fertigartikel

oder das FROM umstellen ...

oder evtl. einen FK zusätzlich über v2fertigartikel.id

ist aber alles nur geraten, wie erwähnt, kenne/nutze ich FB nicht.

[exilant]

Die Felder sind beide als INTEGER NOT NULL deklariert. Ein Umdrehen des Vergleiches habe ich auch schon versucht. Das bringt nichts. Ich wäre aber auch schreiend aus dem Fenster gesprungen wenn das funktioniert hätte....
Ich werde jetzt mal das mit dem FK probieren. Allerdings laufen ein paar ältere Anwendungen auf der Datenbank. Da bin ich mir nicht sicher welche Seiteneffekte ich mir mit der Deklarion des FK einhandle. Ich sehe die Exceptions durch constraints violations schon vor meinem geistigen Auge.
Schaun mer mal.
Trotzdem Danke,
Martin

[Namenloser]

Kann es sein, dass auf v2ps.status auch ein Index ist, und er diesen im ersten Schritt (bevor er den eigentlichen Join durchführt) verwendet, um die Zeilen von v2ps anhand der where-Bedingung einzugrenzen? Dann kommen anschließend die ids unsortiert heraus, sodass er keinen Merge-Join durchführen kann (und daher von den Indizes auf den id-Spalten nicht profitiert).

Vorausgesetzt, ich habe diesen Query Plan überhaupt richtig interpretiert... habe auf die Schnelle irgendwie keine Dokumentation dazu gefunden, wie diese Ausgabe überhaupt zu lesen ist.

Ich würde auf jeden Fall mal die Where-Bedingung testweise weglassen, um zu gucken, was passiert.

[Dejan Vu]

Vielleicht ist die Tabelle 'v2FertigArtikel' zu klein?

[exilant]

Zu klein ist so eine Sache. Im Augenblick sind knapp 8.000 Datensätze drin.
Die Performance der Abfrage ist im Augenblick auch kein Problem. Der Firebird läuft auf einer Mödermaschine unter Ubuntu Server. Granatenschnell. Aber die Datenmenge wird bald steil nach oben gehen. Ich rechne mit mehr als 1.500 neuen Einträgen/Monat ab Ende des Jahres.
Und es geht auch um das Grundsätzliche. Ich sehe da ein Problem.

[exilant]

@Namenloser: Ja. Auf Feld STATUS gibt es einen Index. Warum das aber dazu führen sollte dass der Index für den PK in v2fertigartikel nicht benutzt wird ist mir nicht klar.
Es ist eine simple 1-1 Beziehung.
Aber trotzdem Danke.

[p80286]

Vielleicht ist hier etwas für Dich zu holen?

Gruß
K-H

P.S.
Das Umstellen der Tabellenreihenfolge im FROM kenn ich noch von den älteren MSSQL-Servern, da waren Abfragen noch handoptimiert.

[tsteinmaurer]

Neben dem Ausführungsplan wären auch noch IO Statistiken in Form von Indexed vs. Non-Indexed Reads auf den Tabellen interessant.

Regel Nummer 1 beim Performance testen ist das Ganze mit einer repräsentativen Datenmenge zu machen. Füll dir die Tabellen mal mit einem Datengenerator an und dann schau mal, ob sich hier was ändert.

Thomas

[Namenloser]

Zitat:

Zitat von exilant (Beitrag 1267778)

@Namenloser: Ja. Auf Feld STATUS gibt es einen Index. Warum das aber dazu führen sollte dass der Index für den PK in v2fertigartikel nicht benutzt wird ist mir nicht klar.

Weil der Nutzen des Index beim Join, soweit ich weiß, hauptsächlich darin besteht, dass durch ihn beide Relationen bezüglich der Verknüpfungs-Spalte sortiert sind. Dadurch kann man einen recht effizienten Merge-Join durchführen.

Folgende Tabellen als Beispiel:

Code:

Tabelle X    Tabelle Y
 A B          A C
---------    ---------
 1 42         2 0
 2 27         3 1
 3 40         4 2
 5 38         5 3
 7 39         7 4
 9 41         9 5

Beide Tabellen haben einen Index auf Spalte A (aufsteigend sortiert), und die Abfrage ist

Delphi-Quellcode:

select A, B, C from X join Y on X.A = Y.A

.

Man geht nach folgendem Pseudocode vor:

Code:

CursorX := Zeiger auf erste Zeile von Tabelle X.
CursorY := Zeiger auf erste Zeile von Tabelle Y.

while (not CursorX.EOF and not CursorY.EOF) do
begin
  if CursorX.A = CursorY.A then
    Elemente von CursorX und CursorY konkatenieren und Zeile ausgeben
  else if CursorX.A > CursorY.A then
    CursorY eine Zeile weiter nach unten bewegen
  else if CursorX.A < CursorY.A then
    CursorX eine Zeile weiter nach unten bewegen
end;

(Zur Vereinfachung gehe ich hier mal davon aus, dass A in beiden Tabellen unique ist. Man kann den Algorithmus aber auch für nicht-unique Fälle verallgemeinern).

Das ist ein recht effizienter Join-Algorithmus, allerdings nur dann wirklich effizient, wenn die es für die Eingabemengen schon sortiert sind. Im obigen Beispiel ist das der Fall dank des Index.

Wenn die Abfrage jetzt aber stattdessen wäre

Delphi-Quellcode:

select A, B, C from X join Y on X.A = Y.A where X.B in (42, 27, 38, 39)

und auf B ein Index liegt, dann kann es sein, dass der Optimizer sich entscheidet erst mal nach der Bedingung zu filtern und dabei den Index auf B zu nutzen.

Zwischenergebnis:

Code:

Tabelle X'   Tabelle Y
 A B          A C
---------    ---------
 2 27         2 0
 5 38         3 1
 7 39         4 2
 1 42         5 3
              7 4
              9 5

Da der Index auf B benutzt wurde, sind die Zeilen von X' nun möglicherweise in einer anderen Reihenfolge und nicht mehr nach A sortiert. Folglich kann der Join nun nicht mehr so effizient mit dem Merge-Join-Algorithmus durchgeführt werden. Man könnte jetzt natürlich X' einfach noch mal extra nach A sortieren, aber möglicherweise ist es effizienter, gleich einen ganz anderen Join-Algorithmus zu verwenden, z.B. Hash-Join. Dabei nützt einem dann allerdings auch der Index auf Tabelle Y nichts mehr, weshalb er nicht benutzt wird.


Ist aber nur geraten, ich hab keine Ahnung, was der Optimizer intern macht und was für Algorithmen er verwendet.

[Dejan Vu]

Dann brauch ich aber keinen B+ Baum. Ich habe das so umgesetzt:

Code:

CursorX auf 1.Zeile
while not CursorX.EOF do
  if CursorY.FindFirst(CursorX.A) then
    repeat
      Output CursorX.joined(CursorY);
    until not CursorY.FindNext(CursorX.A);
    CursorX.GotoNext

Wobei CursorX meist auf die Tabelle zeigt, die kleiner ist bzw. bei der alle 'A'-Werte insgesamt schneller zu lesen sind (z.B. weil die Tabelle kleiner ist, oder ein Index vorliegt oder beides oder oder oder).

Der Optimizer wird diese Strategie verwenden, wenn er für das Suchen des korrespondierenden Eintrags eine schnelle B*-Suche verwenden kann (fast O(1)) und Indexe für beide Tabellen auf 'A' vorhanden sind und die Tabelle 'X' nicht all zu viele unterschiedliche 'A' besitzt. Im Extremfall ist die eine Tabelle nur 1 Zeile lang, dann habe ich den korrespondierenden Eintrag aus der anderen Tabelle in O(1) gefunden. Bei deiner Version ist das O(n).

Es kann allerdings sein, das 'dein' Scan schneller ist. Z.B. wenn beide Tabellen in etwa gleich groß sind und es viele unterschiedliche 'A' in beiden Tabelle gibt. Es wird bestimmt Heuristiken geben, die ausrechnen, wann welche Schleife verwendet wird (der 'Optimizer'). Wozu sind denn sonst die Indexstatistiken gut?

Dein Algorithmus bricht blöderweise ab, wenn ein Schlüssel 'A' in X größer ist als der größte in Y (wenn also CursorY.EOF = true) ist. Aber ich weiß, wie Du das meinst. Blöd ist der jedenfalls nicht, der Algo.

[hoika]

Hallo,

reduzier mal deine Query schrittweise

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


select
v2ps.par,
v2ps.v2fertigartikel,
v2fertigartikel.artikelbezeichnung
from v2ps
inner join v2fertigartikel on (v2ps.v2fertigartikel=v2fertigartikel.id)
// das fliegt erst mal raus where v2ps.status in ('U','A')

Vielleicht mag er das IN nicht ?

Das ginge ja auch über OR.

Von welcher FB-2.5-Version reden wir eigentlich ?


Heiko

[Namenloser]

Zitat:

Zitat von Dejan Vu (Beitrag 1267791)

Dein Algorithmus bricht blöderweise ab, wenn ein Schlüssel 'A' in X größer ist als der größte in Y (wenn also CursorY.EOF = true) ist.

Dann gibt es ja auch keine weiteren Datensätze mehr, für die die Join-Bedingung noch erfüllt sein könnte. X und Y sind ja beide sortiert; d.h. man einen Schlüssel A in X erreicht, der größer ist als alle Schlüssel in Y, dann wären alle nachfolgenden Schlüssel von X erst recht größer als alle Schlüssel in Y (und folglich nicht gleich).

Aber du hast natürlich recht, dass die beste Strategie immer von den Daten selbst abhängt. Es kann daher eben z.B. auch schneller sein, einen stumpfen Table-Scan durchzuführen als den Index zu benutzen, weil konsekutives Lesen schneller ist als Random-Access.

[Dejan Vu]

(abbrechen) Autsch, ja. Stimmt.

[exilant]

Zunächst mal vielen Dank für die vielen Antworten.
Zunächst @tsteinmaurer:

Klar. Man sollte mit einer Datenmenge testen die dem worst-case nahekommt. Das werde ich auch tun.
Aber zunächst mal folgendes:

Die Statistik indexed vs. natural reads habe ich mir angesehen und verstehe nicht wirklich was da vor sich geht.
Situation ist wie beschrieben folgende:

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

Die Abfrage wie gehabt:

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

Ausgabe des Plans (IBExpert)
PLAN JOIN (V2FERTIGARTIKEL NATURAL, V2PS INDEX (V2PS_IDX3))

Statistik:
NonIndexed Reads (v2fertigartikel): 7.782 !!!!!
Indexed Reads (v2ps): 241.934 !!!

Der vom Plan verwendete Index V2PS_IDX3 indexiert die Felder (V2FERTIGARTIKEL,V2FARBE), ist also völlig unbrauchbar im Kontext der Abfrage.
Entweder spinnt die Ausgabe des IBExpert oder der Optimizer erzeugt seinen Plan nach sehr esoterischen Kriterien.

@hoika

Das Query reduziert habe ich. Ebenfalls das IN durch ein OR ersetzt. Es ändert genau garnichts.
Ich bin echt verwundert.

[exilant]

Zum Pseudocode: Vielleicht bin ich da zu naiv, aber in den alten Zeiten in denen man noch selbst mit Indexen 'rumhantierte hätte meine Lösung wie folgt ausgesehen:


foreach status in menge
seek status in v2psindex
while v2psindex.staus = status;
seek v2ps.v2fertigartikel in v2fertigartikelindex
ergebnismenge.add ( v2ps.par, v2fertigartikel.artikelbezeichnung)
next in V2PsIndex;
end
end


Ergibt (Anzahl gesuchter Stati) Suchevorgänge im Index von v2ps, in meinem Fall genau zwei.
Dann noch (Anzahl gefundener passender stati ) Suchvorgänge im Index v2fertigartikel. In meinem Besispiel wären das 864.
Beide Tabellen verfügen über genau die Indexe die benötigt werden die Abfrage optimal auszuführen

Grüße,
Martin

[Dejan Vu]

Zitat:

Zitat von exilant (Beitrag 1267854)

Der vom Plan verwendete Index V2PS_IDX3 indexiert die Felder (V2FERTIGARTIKEL,V2FARBE), ist also völlig unbrauchbar im Kontext der Abfrage.

Wieso? V2FERTIGARTIKEL wird doch im JOIN verwendet.

[exilant]

Zitat:

Zitat von Dejan Vu (Beitrag 1267875)

Zitat:

Zitat von exilant (Beitrag 1267854)

Der vom Plan verwendete Index V2PS_IDX3 indexiert die Felder (V2FERTIGARTIKEL,V2FARBE), ist also völlig unbrauchbar im Kontext der Abfrage.

Wieso? V2FERTIGARTIKEL wird doch im JOIN verwendet.

v2ps_idx3 ist ein Index der Tabelle v2ps. Wozu ich ich im Kontext der Anfrage diesen Index benötigen sollte
beleibt unklar. Die Indexierten Felder V2FERIGARTIKEL und V2FARBE sind kein Kriterium für die erwartete Ergebnismenge aus v2ps. Hier bilden die Sätze mit den gesuchten Stati das gewünschte Ergebnis.

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

Zitat von exilant (Beitrag 1267854)

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:

Zitat von exilant (Beitrag 1267913)

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:

Zitat von exilant (Beitrag 1267913)

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

Zitat von Namenloser (Beitrag 1267916)

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:

Zitat von exilant (Beitrag 1267740)

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

Zitat:

Zitat von mkinzler (Beitrag 1267925)

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

Zitat:

Zitat von Dejan Vu (Beitrag 1267917)

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

[Dejan Vu]

Zitat:

Zitat von exilant (Beitrag 1267924)

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:

Zitat von mkinzler (Beitrag 1267925)

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

[exilant]

Zitat:

Zitat von Dejan Vu (Beitrag 1267929)

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.