Abfrage-Statement mit View fehlerhaft

[Jens Delling]

Hallo, ich bin neu hier und eventuell wurde das Thema hier schon erörtert. Dann gleich sorry vorab...

Problem:
Nach der Umstellung von Firebird 3 auf 5.02 laufen einige Abfragen extrem lang.

Wenn man eine View in der Abfrage joint werden meist (nicht immer bei der gleichen Abfrage in der gleiche Datenbank ohne Datenänderungen!!!)
zu jeder Datenzeile alle Datensätze aus der view geladen.

Beispiel:
CREATE OR ALTER VIEW VIEW_AU_PRUEFUNG(
KUNDE,
LIEFERTERMIN,
MENGE,
EINHEIT)
AS
select
coalesce(k.kund_a_name1, '') || ' [' || coalesce(k.kund_a_nr, '') || ']',
iif(bp.bpos_d_liefertermin > cast('01.01.1900' as Date), bpos_d_liefertermin,
iif(b.bele_d_lieferdate > cast('01.01.1900' as Date), bele_d_lieferdate, NULL)),
bpos_n_menge,
ARTI_A_EINHEIT,
from
belege b
left outer join belegpos bp on bp.bpos_a_typ = 'AU' and bp.bpos_n_nr = b.bele_n_nr
left outer join Kunden k on k.kund_a_nr = b.bele_a_kundennr
[left outer join ARTIKEL x on ARTI_A_NR = bp.bpos_a_artikelnr
left outer join artikelwerte w on w.ARWE_A_NR = bp.bpos_a_artikelnr
left outer join touren t on t.tour_n_nr = b.bele_n_tour
where
b.bele_a_typ = 'AU' and b.bele_l_erledigt = 'N' and b.bele_l_gesperrt = 'N'
and coalesce(bp.bpos_n_menge,0) > 0 and coalesce(bp.bpos_n_menge,0) - coalesce(bp.bpos_n_mengegelief,0) > 0
order by
bp.bpos_a_artikelnr

bei dieser Abfrage ist alles gut, Laufzeit 1 Sekunde.
Die Statistik sagt in meiner Testdatenbank 9000 Abfragen auf Artikel und 9000 auf Artikelwerte.

Wenn ich aber statt auf die Tabelle ARTIKEL und ARTIKELWERTE auf eine View zugreife die relevante Daten aus den beiden Tabellen enthält
bekomme ich in der Statistik 240000000 Zugriffe auf die Tabelle Artikel und die Abfrage läuft mehrere Minuten.

Bis Firebird 4 gab es diesen Effekt nicht, das Problem tritt ab dem 5.01 auf.
Dies aber auch nur in bestimmten Konstellationen.
Es scheint das ab FB 5 etwas über die Suchoptimierung läuft.
(die Statistik für alle Indexe wurde neu berechnet)

Die gleiche Datenbankstruktur mit anderen Datensatzanzahlen in Belegen und Artikel reagiert anders.
Da tritt dieser Effekt nicht auf.

Hat jemand damit Erfahrungen ?

Wir verwenden Views schon häufig und können nicht vorhersagen bei welchem Kunden von uns dann Abfragen extrem lange laufen.
Die Alternative wäre alle Views zu eliminieren, oder Firebird 5 nicht einzusetzen.
Das kann ja auch nicht die Lösung sein.

[bfuerchau]

Es steht und fällt immer mit der Index-Erstellung.
Für die Join-On-Beziehungen müssen Indizes vorhanden sein, damit die Abfrage überhaupt performt.

Für die Where-Klausel sollte ebenso ein Index erstellt werden:

b.bele_a_typ = 'AU' and b.bele_l_erledigt = 'N' and b.bele_l_gesperrt = 'N'
and coalesce(bp.bpos_n_menge,0) > 0

Da ja nur Positionen mit einer bpos_n_menge > 0 benötigt werden, lass den Coalesce einfach weg, dann kann der Index auch das Feld bpos_n_menge aufnehmen.
bpos_n_menge > 0 liefert nur true, wenn das Feld auch nicht NULL ist.

Die Hauptprobleme treten immer dann auf, wenn die Tabellen größer werden. Bei wenigen 1000 Zeilen passiert fast alles im Speicher.

[Jens Delling]

Hallo,
danke für die Antwort.
Für die Felder in der Where-Klausel sind Indexe vorhanden. OK, das coalesc könnte man weglassen.
Darum geht es in meiner Frage aber gar nicht.
Die Abfrage hat bis Firebird 3 mit einer Abfragezeit < 1 Sekunde funktioniert.
Mit dem Firebird 5 dauert es > 1 Minute.

Das Problem ist die Abfrage einer View.
Die View ARTIKELFULL enthält Felder aus der Tabelle ARTIKELWERTE und ARTIKEL

Wenn ich die beiden Tabellen getrennt in der Abfrage aufnehme geht es schnell. (3000 Zugriffe auf Artikel)
Wenn ich die View joine, lädt er zu jeder Auftragsposition immer alle Artikel. (241000000 Zugriffe auf Artikel)
Die View greift auf die beiden Tabellen zu, die jeweils korrekt indexiert sind.

Das Problem tritt, wie gesagt, nur unter Firebird 5.02 auf.
Wir arbeiten seit 20 Jahren mit Firebird und nutzen IBExpert als Analysetool.

[bfuerchau]

Ja, das wundert mich dann schon. Dann solltest du eine Fehlermeldung an das FB-Team schicken.
U.U. wird die Indexauswertung in der View nicht korrekt behandelt.
Was sagt denn IBExpert zur Indexverwendung (Plan)?
Werden die Indizes der View korrekt angewendet?
Wie ist die Selektivity der Indizes?
Was passiert, wenn du den SQL der View mal manuell als Derived Table einbettest.

[martin.koeditz]

Wir hatten gestern ein ähnliches Phänomen festgestellt. Eine Abfrage mit LEFT JOIN funktioniert einwandfrei. Mit INNER JOIN (sollte grundsätzlich schneller sein) dauert die Abfrage deutlich länger.

In unserem Fall werden wir ein Update auf die neueste Firebird-Version 5.0.3 ausführen und nochmals testen.

[bfuerchau]

Der Inner Join ist nur dann schneller, als der Left Join, wenn der Index des On auf beiden Seiten existiert.
Verständlich wird es dann, wenn die Table-Folge (from => Inner) umgedreht wird.

[martin.koeditz]

Der Inner Join ist nur dann schneller, als der Left Join, wenn der Index des On auf beiden Seiten existiert.
Verständlich wird es dann, wenn die Table-Folge (from => Inner) umgedreht wird.

Das ist in unserem Szenario genau der Fall. Dennoch ist der INNER JOIN "extrem" langsam. Wenn dies im 5.0.3 noch immer so ist, werde ich das an die Entwickler melden.

[bfuerchau]

Gut, dass wir noch immer mit 3.0 ohne Einschränkungen alles machen können, was wir benötigen .

[vr2]

Der Optimierer schaut bei Firebird 5 genauer hin als bei FB4 und davor. Bis FB4 reichte das bloße Vorhandensein eines Index, damit er auch benutzt wird. Ob er aktuell war (set statistics), spielte keine große Rolle. Das ist nun anders und ist uns auch einige Male auf die Füße gefallen. Falls ihr Statements habt, die bisher gute Performance hatten und mit FB5 nicht mehr, vergleicht die verwendeten Pläne. Die hängen jetzt nicht mehr nur davon ab, ob ein Index existiert, sondern auch, ob er aktuell ist oder ob in Tabellen ausreichend Daten sind, selbst das macht nun einen Unterschied. Das Ziel war sicher, Abfragen präziser zu optimieren, mehr Kontext ins Spiel zu bringen, aber es hat auch die Komplexität erhöht und damit die Wahrscheinlichkeit von Fehlentscheidungen des Optimierers. Im Mittel ist FB5 etwas schneller als seine Vorgänger, aber an manchen Stellen greift er noch gehörig daneben.

Die ein oder andere (unvermeidliche) Planregression in FB5 wurde bereits behoben, aber wenn ihr welche habt und testcases, packt die in den Firebird tracker, davon haben wir alle was.