Ausführunsplan / SQL Optimizer

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

[Namenloser]

Zitat von 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.

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