Left Join macht Probleme

[Jens Schumann]

Hallo,
ich habe ein Problem mit einem Left Join. Es ist mir unerklärlich.
Ich arbeite unter P4 HT, 3GHz, 1GByte Arbeitsspeicher.

tab_1 hat ca 250.000 Datensätze
tab_2 hat ca. 4.000 Datensätze
Da es sich um relative wenig Datensätze handelt ist das Ganze noch mysteriöser

Diese Query wird einwandfrei ausgeführt. Dauer ca 30 Sekunden.

SQL-Code:

SELECT tab_1.JAHR, tab_1.KENNER, tab_1.ENTITY, tab_1.SORTER,
           tab_1.DIVISION, tab_1.PARTNER, tab_1.BWA,
           tab_1.POSNR, tab_2.BEZ
FROM tab_1 INNER JOIN tab_2 ON (tab_1.posnr=tab_2.gvkonto) and (tab_1.kenner=tab_2.kenner) 
ORDER BY tab_1.JAHR, tab_1.KENNER, tab_1.ENTITY, tab_1.SORTER

Access 2003 schafft die Abfrage in ca 15 Sekunden :shock:

Wenn ich aus dem Join eine Left Join mache stürzt der Firebird scheinbar ab.
Ob die Query mit der IBOConsole oder über IBX abschicke ist egal.
(Sollte hier auch egal sein)

SQL-Code:

SELECT tab_1.JAHR, tab_1.KENNER, tab_1.ENTITY, tab_1.SORTER,
           tab_1.DIVISION, tab_1.PARTNER, tab_1.BWA,
           tab_1.POSNR, tab_2.BEZ
FROM tab_1 LEFT JOIN tab_2 ON (tab_1.posnr=tab_2.gvkonto) and (tab_1.kenner=tab_2.kenner) 
ORDER BY tab_1.JAHR, tab_1.KENNER, tab_1.ENTITY, tab_1.SORTER

Access 2003 schafft die Abfrage in ca 15 Sekunden :shock:

Um der Sache auf den Grund zu gehen habe ich eine neue Datenbank registriert und dort zwei Tabllen
angelegt. TabelleA hat 11 und TabelleB hat 3 Datensätze. Damit funktioniert der Left Join :shock: :shock: :shock:

SQL-Code:

SELECT tabellea.id,tabellea.feld1,tabelleb.feld1 as Name
FROM tabellea LEFT JOIN tabelleb ON tabellea.feld1=tabelleb.feld1
 ORDER BY tabellea.feld1, tabellea.id

Hat schon jemand gehört, dass der Firebird Probleme mit einem Left Join hat?
[EDIT] Ob ich LEFT JOIN oder LEFT OUTER JOIN schreibe macht keinen Unterschied[/EDIT]

[Robert_G]

Zitat:

Zitat von Jens Schumann

Hat schon jemand gehört, dass der Firebird Probleme mit einem Left Join hat?
[EDIT] Ob ich LEFT JOIN oder LEFT OUTER JOIN schreibe macht keinen Unterschied[/EDIT]

Jupp, hatte ich.
Das Problem ist wohl, dass der rudimentäre Optimizer von FB oder IB keine nested loops kennt und sortierte Indizes nicht mit in den Query plan berücksichtigt.
Beispiel:
Du vergleichst X.A mit Y.B und auf beiden liegt ein sortierter Index. Die Datenbank hätte somit sehr einfach die Möglichkeit sämtliche Wertekombinationen nur einmal vergleichen zu müssen (wofür liegen die Indizes wohl sonst sortiert vor? :roll: ), Firebird interessiert das nicht die Bohne. Der wird sich fleißig einen Wolf rödeln und immer alles mit jedem vergleichen...

Besonders krass zeigt sich der Mangel bei sowas:

SQL-Code:

SELECT X
FROM   Y
WHERE  Z in (SELECT A
             FROM   B
             WHRE   C = :C)

Firebird wird die sub query FÜR JEDEN DATENSATZ ausführen. :shock: Ich kenne kein anderes ernsthaftes DBMS, dass sich sowas erlaubt.

[Hansa]

Wenn es mit sehr wenigen Datensätzen geht und mit sehr vielen nicht, dann liegt die Vermutung nahe, daß es an den Daten selbst liegt. Zuerst einmal würde ich die Felder nicht einzeln angeben, sondern vorerst den * verwenden. Würde mich nicht wundern, wenn es dann geht. 8)

@RG: an Firebird wird das kaum liegen. Interessant wäre auch mal die genaue Fehlermeldung.

[Robert_G]

Zitat:

Zitat von Hansa

Wenn es mit sehr wenigen Datensätzen geht und mit sehr vielen nicht, dann liegt die Vermutung nahe, daß es an den Daten selbst liegt.

Quatsch!
Irgendwann wird er sich rekursiv so tief im Stack vergraben haben, dass er anfangen muss nur noch kleinere chunks zu bearbeiten.
Da er diese dann ständig wieder vorkramen muss um sie mit den Ergebnissen eines anderen zu vergleichen wird es ab einer bestimmten Anzahl von Datensätzen nunmal langsam.

Zitat:

Zitat von Hansa

Zuerst einmal würde ich die Felder nicht einzeln angeben, sondern vorerst den * verwenden. Würde mich nicht wundern, wenn es dann geht. 8)

Zitat:

Zitat von Hansa

@RG: an Firebird wird das kaum liegen. Interessant wäre auch mal die genaue Fehlermeldung.

Die beiden Aussagen beißen sich irgendwie. Schließlich wirfst du Firebird erst vor, dass er zu blöd wäre eine normale SELECT Clause abzufragen um danach zu behaupten, dass es nicht an FB liegen kann. :gruebel: :freak:
Beides klingt nach Kaffeesatzlesen und nicht nach einer Aussage von jemandem, der sich mit der Arbeitsweise seiner Datenbank auseinandergesetzt hat. :roll:

@Jens, bei FB kannst du ziemlich viel Leistung gewinnen, wenn du dir deine performancekritischsten Tabellen zusammensuchst und ihre Größe pro Datensatz bestimmst.
Danach stellst du dann die Pagesize ein. Somit wird sich FB die Daten in mundgerechten Häppchen von der Platten holen können. ;)

[Hansa]

Jetzt aber mal halb lang ! Für mich ist Kaffeesatzlesen, irgendwelche Vermutungen anzustellen, ohne die Fehlermeldung zu kennen. 8) Die soll er mal angeben. Ich fange jedenfalls nicht an, vorher über Datenbank Eingeweide rumzulabern und den Fehler da zu suchen und noch zu meckern. :lol:

Wie oft wird nach Bugs gefragt, bei denen sich kurze Zeit später herausstellt, daß es ein eigener Programmier-Fehler war ? Ich behaupte in 99,9 % der Fälle (zumindest was Delphi betrifft). Meistens wird das allerdings im Nachhinein nicht zugegeben. Firebird ist zudem sehr stabil.

Wenn irgendwo ein Bug liegt, dann eventuell bei den Zugriffskomponenten. Da Jens FB 1.5 benutzt sind die IBX sehr verdächtig. Mit FB 1.5 würde ich die nicht mehr verwenden.

Völliger Firlefanz ist das Rumschrauben an der Page Size. Die spielt eine untergeordnete Rolle und ist nur intern für die DB relevant. Es ist normalerweise unmöglich, daß eine falsche Größe ein Programm abstürzen läßt. :shock:

[Jens Schumann]

Hallo Hansa,
es kommt keine Fehlermeldung. Der Firebird hört einfach nicht auf die Query zu bearbeiten. Nach dem die Abfrage 3 Stunden lief habe ich die IBOConsole abgewürgt.
D.h. es passiert scheinbar nichts.

Ich glaube nicht das es an IBX liegt. Bislang habe ich nur gute Erfahrungen im Zusammenspiel IBX und Firebird gemacht. In diesem Fall schickt IBX o. die IBOConsole ja nur die Query zum Server. Die eigentliche Arbeit macht doch der Firebird.

Beide Tabellen haben keinen Index.

[Hansa]

* getestet ? Was sagt der SQL-Monitor ?

[imp]

Hallo,

da ein LEFT JOIN anscheinend ausreichend ist:

Wie verhält sich die Laufzeit wenn du den LEFT JOIN folgender Massen notierst?

Delphi-Quellcode:

SELECT tab_1.JAHR, tab_1.KENNER, tab_1.ENTITY, tab_1.SORTER,
           tab_1.DIVISION, tab_1.PARTNER, tab_1.BWA,
           tab_1.POSNR, 
           (SELECT tab_2.BEZ FROM tab_2 WHERE (tab_1.posnr=tab_2.gvkonto) and (tab_1.kenner=tab_2.kenner)) AS BEZ
FROM tab_1
ORDER BY tab_1.JAHR, tab_1.KENNER, tab_1.ENTITY, tab_1.SORTER

Ich hatte auch mal das Problem, das Aggregationen mittels LEFT JOIN total langsam liefen. Mit obiger Schreibweise klappte es hingegen einwandfrei.

Gruss,
Frank

[Stevie]

Zitat:

Zitat von Robert_G

Ich kenne kein anderes ernsthaftes DBMS, dass sich sowas erlaubt.

Ist es bei Oracle nicht auch so, dass wenn 2 Tabellen miteinander gejoint werden und die eine im Verhältnis zu der anderen wenig Datensätze (z.B. 250000 zu 4000) enthält, auf die "kleine" Tabelle mit einem full table scan zugegriffen wird, egal, ob da ein Index drauf liegt oder nicht??? :stupid: Ok, Oracle braucht für die Abfrage oben auch nicht lang, das stimmt. :wink:

P.S. Und such nicht immer alle Fehler in Firebird!!! :evil: Wir wissen inzwischen, dass du es nicht leiden kannst! :roll:

[Robert_G]

Zitat:

Zitat von Stevie

P.S. Und such nicht immer alle Fehler in Firebird!!! :evil: Wir wissen inzwischen, dass du es nicht leiden kannst! :roll:

Mache ich nicht, zum Beispiel bastel ich mir gerade ein Tool, dass mir selbst etwa Arbeit abnehmen soll. Da ich dafür auf keinen DB Server angewiesen sein will nehme ich FB embedded. :thuimb:
FB hat aber nunmal seine Macken, die werden IMHO nur zu gerne vernachlässigt bzw. die User wissen noch nichtmal, dass sie existieren. ;)
Das was da oben steht IST ganz einfach eine solche Macke. ;)
Wenn ich mal etwas krass klinge, dann liegt das oft daran, dass ich oft nicht kapieren kann, warum man auf Gedeih und Verderben FB einsetzen will. Obwohl Konkurrenz DBs á la pgSQL den Job besser und einfacher erledigen würden. ;)

@OT
Zum full Table scan in Ora:
Abfrage

SQL-Code:

SELECT A, B
FROM   X, Y
WHERE  X.A = Y.ID and
       not B between Y.LowerBound and Y.UpperBound and
       C is null

X hat etwa 10.000 Einträge.

A, B & C sind indiziert. A ist ein FK auf eine Tabelle mit 100 Einträgen (Y). B ist ein Messwert, enthält etwa 2000 unterschiedliche Werte.
C ist eine Beschreibung, die ein mögliches Ausreißen vom Normbereich des Wertes erklärt...

Wenn du das jetzt abfragst wird Oras Optimizer über B & C stolpern. Ein reiner Index read wäre bei den Spalten totaler Blödsinn, da die Verknüpfung der Indizes mit den wirklichen Daten zu aufwendig wäre.
Der Vergleich von A wird aber über den Index gelöst. Denn so kann er sehr schnell die Möglichkeiten reduzieren. ;)
Es ist also kein richtiger FTS, auch wenn er als solcher in der plan table auftaucht. ;)

[Jens Schumann]

Zitat:

Zitat von imp

Hallo,

da ein LEFT JOIN anscheinend ausreichend ist:

Wie verhält sich die Laufzeit wenn du den LEFT JOIN folgender Massen notierst?

Delphi-Quellcode:

SELECT tab_1.JAHR, tab_1.KENNER, tab_1.ENTITY, tab_1.SORTER,
           tab_1.DIVISION, tab_1.PARTNER, tab_1.BWA,
           tab_1.POSNR, 
           (SELECT tab_2.BEZ FROM tab_2 WHERE (tab_1.posnr=tab_2.gvkonto) and (tab_1.kenner=tab_2.kenner)) AS BEZ
FROM tab_1
ORDER BY tab_1.JAHR, tab_1.KENNER, tab_1.ENTITY, tab_1.SORTER

Ich hatte auch mal das Problem, das Aggregationen mittels LEFT JOIN total langsam liefen. Mit obiger Schreibweise klappte es hingegen einwandfrei.

Gruss,
Frank

COOL - funktioniert einwandfrei. Das Ergbnis liegt auf meinem Laptop(PIII 1 GHz) in ca 17 Sekunden vor.

[CasaN]

hi,

17 sekunden? das ist doch ne halbe ewigkeit! schon mal mit einer prozedur -> for select's versucht??

mfg
casan

[Jens Schumann]

Zitat:

Zitat von CasaN

hi,

17 sekunden? das ist doch ne halbe ewigkeit! schon mal mit einer prozedur -> for select's versucht??

mfg
casan

for select's :gruebel: was ist denn das?

[CasaN]

schon mal ne prozedur erstellt?

[Jens Schumann]

Zitat:

Zitat von CasaN

schon mal ne prozedur erstellt?

Ja :oops:

[CasaN]

zb.

for select name from members into :name do -- für alle ergebnisse aus der abfrage
begin
for select text from beitrag into :text do -- für alle ergebnisse aus der abfrage
begin
suspend; -- ausgeben
end;
end;

als ergebnis kommen alle namen aus members mit texten aus beitrag in einer tabellenform:

member1 - text1
member1 - text2
member2 - text1
member3 - text1
member4 - text1
member5 - text2
member5 - text3
usw.

[Jens Schumann]

Hallo,
so wie es aussieht lag das Problem zwischen meinen Ohren. Mit einem Index auf der
richtigen Splate hat sich die Ausführungszeit von 30 Sekunden auf 2 Sekunden reduziert.