Ausführunsplan / SQL Optimizer

[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

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

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