Ausführunsplan / SQL Optimizer

[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

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

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 von Dejan Vu:

Zitat von exilant:

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