Firebird IN, EXISTS Langsam

[Gruber_Hans_12345]

So, habe schon mal vor langer zeit gefragt, und verwende im Moment den aktuellen Firebird 2.5, und habe bei einem SQL Statement ein Speedproblem (nicht so schlimm, aber möchte es verstehen, wie und ob man es schneller machen kann)

Verienfacht gesagt ich habe 2 Tabellen
"Artikel" und "Positionen"
ich möchte alle Artikel rausbekommen die nicht in der Position Tabelle vorhanden ist (dort können die auch öfters vorkommen)

Die Versuche waren

Code:

SELECT A.ID FROM ARTIKEL A WHERE A.ID IN (SELECT T.ARTIKEL_ID FROM V_AUFTRAG_PO T WHERE A.ID = T.ARTIKEL_ID GROUP BY T.ARTIKEL_ID)

Code:

SELECT A.ID FROM ARTIKEL A WHERE A.ID NOT IN (SELECT T.ARTIKEL_ID FROM V_AUFTRAG_PO T WHERE A.ID = T.ARTIKEL_ID GROUP BY T.ARTIKEL_ID)

Code:

SELECT A.ID FROM ARTIKEL A WHERE NOT EXISTS (SELECT T.ARTIKEL_ID FROM V_AUFTRAG_PO T WHERE A.ID = T.ARTIKEL_ID)

Code:

SELECT A.ID FROM ARTIKEL A WHERE EXISTS (SELECT T.ARTIKEL_ID FROM V_AUFTRAG_PO T WHERE A.ID = T.ARTIKEL_ID)

dauern alle ca. 26 sekunden

das alleinige

Code:

SELECT A.ID FROM ARTIKEL A

dauert sammt Fetchall ein paar ms und liefert ca 2500 Rekord

das alleinige

Code:

SELECT T.ARTIKEL_ID FROM V_AUFTRAG_PO T GROUP BY T.ARTIKEL_ID

dauert sammt Fetchall auch nur ein paar ms und liefert ca 1200 Rekords


nur die kombination in welcher form auch immer, dauert über 25 Sekunden ...

Hat wer eine Idee, wie man das beschleunigen kann?

[TBx]

ein weiteres Statement, daß Du mal austesten könntest:

Code:

SELECT A.ID  
  FROM ARTIKEL A 
  LEFT JOIN V_AUFTRAG_PO T ON (A.ID = T.ARTIKEL_ID)
  WHERE T.ARTIKEL_ID IS NULL
  GROUP BY A.ID

Sonderlich performant sind solche Abfragen leider meist nicht. Zu dem obigen Statement sollten Indizes auf ARTIKEL.ID und V_AUFTRAG_PO.ARTIKEL_ID liegen.

Gruß aus dem hohen Norden

[mkinzler]

Wobei eigentlich die Abfarge mit NOT EXISTS performanter sein sollte, passende iIndizes voausgesetzt

[jobo]

Zitat:

Wobei eigentlich die Abfarge mit NOT EXISTS performanter sein sollte

Ist das so?
Ich dachte, "EXISTS" deutet darauf hin, dass die Datenbank nach einem Treffer die Suche abbrechen könnte,
"NOT EXISTS" entsprechend. Eine DB-Implementierung von "EXISTS" könnte darauf abgestellt sein.
Eine gesuchte, vollständige Differenz klingt nicht nach dem gedachten Einsatzgebiet von "EXISTS". Der Join mit is NULL Prüfung scheint mir da sinnvoller.

Ich hab kein Firebird am Start, nur laut gedacht.

[mkinzler]

Aus der Referenz

Zitat:

Zitat von http://www.firebirdsql.org/manual/nullguide-predicates.html#nullguide-pred-exists-singular

NOT EXISTS always returns the opposite of EXISTS: false or true, never NULL. NOT EXISTS returns false immediately if it gets a true result on the subquery's search condition. Before returning true it must step through the entire set.

[Gruber_Hans_12345]

Zitat:

Zitat von TBx (Beitrag 1080143)

ein weiteres Statement, daß Du mal austesten könntest:

Code:

SELECT A.ID  
  FROM ARTIKEL A 
  LEFT JOIN V_AUFTRAG_PO T ON (A.ID = T.ARTIKEL_ID)
  WHERE T.ARTIKEL_ID IS NULL
  GROUP BY A.ID

Sonderlich performant sind solche Abfragen leider meist nicht. Zu dem obigen Statement sollten Indizes auf ARTIKEL.ID und V_AUFTRAG_PO.ARTIKEL_ID liegen.

Gruß aus dem hohen Norden

Also Indexe liegt auf A.ID (primär Index)
DAs mit dem zusätzlichen Index werde ich morgen gelich mal probieren, obs wirklich vielleicht nur daran lag ...

Das mit dem Left OUTER JOIN geht zwar ein bisschen schneller aber auch nicht viel
Das problem ist das in der realen Anwendung die zugelinkte Tabelle noch 2 weitere benötigt, dann wird es mit dem JOIN auch wieder sehr komplex

Im Moment habe ich nur noch die idee eventuell die aktuellen werte obs einträge in den V_AUFTRAG_PO gibt oder nicht in eine temporäre Tabelle oder irgendwie anders zu machen ....

[jobo]

@mkinzler
Die Beschreibung klingt gut :)
Für einen einzelnen Datensatz ist es ja sicherlich auch richtig.
Aber was macht der Optimizer, wenn das not exists gegen tausende oder mehr Artikel gefahren wird? Immer wieder los rennen und suchen .. ?

@Gruber_Hans_12345
1500 -2000 Datensätze sind nicht viel.
Wenn das V_AUFTRAG_PO im echten Leben so komplex ist, lohnt es sich vielleicht, erst die Differenz zu bilden (die mglw. noch zu viele Datensätze enthält) und anschließend auf dem Ergebnis die "komplexen " Joins zu fahren.

Also ungefähr

Code:

select * from (
  SELECT A.ID
  FROM ARTIKEL A
  LEFT JOIN POSITION T ON (A.ID = T.ARTIKEL_ID)
  WHERE T.ARTIKEL_ID IS NULL
  GROUP BY A.ID
  ) Dif
where .. komplexe Bedingungen

Hier gibts vielleicht noch tolle Hinweise zum Optimizer.

[Gruber_Hans_12345]

Zitat:

Zitat von jobo (Beitrag 1080191)

@mkinzler
Die Beschreibung klingt gut :)
Für einen einzelnen Datensatz ist es ja sicherlich auch richtig.
Aber was macht der Optimizer, wenn das not exists gegen tausende oder mehr Artikel gefahren wird? Immer wieder los rennen und suchen .. ?

Also von denzeiten her, würde ich sagen, ja er macht das - leider - das ist ja das problem hier :(

Zitat:

Zitat von jobo (Beitrag 1080191)

@Gruber_Hans_12345
1500 -2000 Datensätze sind nicht viel.
Wenn das V_AUFTRAG_PO im echten Leben so komplex ist, lohnt es sich vielleicht, erst die Differenz zu bilden (die mglw. noch zu viele Datensätze enthält) und anschließend auf dem Ergebnis die "komplexen " Joins zu fahren.

Also ungefähr

Code:

select * from (
  SELECT A.ID
  FROM ARTIKEL A
  LEFT JOIN POSITION T ON (A.ID = T.ARTIKEL_ID)
  WHERE T.ARTIKEL_ID IS NULL
  GROUP BY A.ID
  ) Dif
where .. komplexe Bedingungen

Hier gibts vielleicht noch tolle Hinweise zum Optimizer.

Also das mit dem group habe ich auch schon probiert, das hat leider auch gar nichts gebracht, entweder hilft dann hier der index soviel, das es nicht mehr soviel unterschied macht,
Hätte es auch schon als STORED PRCOEDURE probiert, aber noch gescheitert ;)

Als reines Script im Client abgelegt, zuerst alle ARTIKEL IDs laden, dann die Liste mit den verwendeten laden, davon per Delphi ein DIFF machen, und dann noch mal ein SELECT -> ist um welten schneller, aber leider zu komples, für viele sachen :(

[jobo]

Ich glaub, Du hast mich falsch verstanden. Es ging mir nicht ums Group, das innere Statement ist ja nur eine Kopie des von mir favorisierten Statement von TBx. Ich hab lediglich das "V_AUFTRAG_PO" gegen "POSITION" getauscht, um zu verdeutlichen, dass Du den komplexen View erstmal beiseite lässt.

Hast Du Dir mal den Optimizer Link angeschaut?

[sx2008]

Wenn wir mal folgende Abfrage anschauen:

SQL-Code:

SELECT A.ID FROM ARTIKEL A WHERE NOT EXISTS (SELECT * FROM V_AUFTRAG_PO T WHERE T.ARTIKEL_ID = A.ID)

dann kann diese Abfrage "rasend schnell" ablaufen, wenn auf dem Feld V_AUFTRAG_PO.ARTIKEL_ID ein Index existiert.
Die SQL-Engine sollte in diesem Fall nur den Index der Tabelle V_AUFTRAG_PO befragen ob ein bestimmter Schlüssel vorhanden ist oder nicht.
Die Unterabfrage SELECT * FROM V_AUFTRAG_PO wird dabei im Grunde genommen überhaupt nicht ausgeführt.
Dafür ist es natürlich erforderlich, dass Firebird so schlau ist nur den Index abzurufen und Zugriffe auf die Tabelle V_AUFTRAG_PO zu unterlassen.
Manchmal (je nach DBMS) erkennt die Engine diese Abkürzung nur wenn in der Unterabfrage SELECT * FROM ... steht.
Im Prinzip kann man in der Unterabfrage auch SELECT V_AUFTRAG_PO.ARTIKEL_ID FROM V_AUFTRAG_PO schreiben; das Ergebnis ist theoretisch gleichwertig zu SELECT * FROM V_AUFTRAG_PO.

Irgendwie stösst SQL hier an seine Grenzen, weil man in der Unterabfrage eine Feldliste angeben muss, die doch im Zusammenhang mit EXISTS nie benützt wird.