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.

[Gruber_Hans_12345]

Zitat:

Zitat von jobo (Beitrag 1080217)

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?

JA, das problem ist die komplexe Abfrage mit 3 Tabellen per Joins ist in der inneren SQL - Also dort wo geschaut wird, ob diese Artikel in der Positions Tabelle verhanden sind (Verlinkt mit Auftrag Tabelle und einer Auftrags informations Tabelle) deshalb ist es da problematisch ...

... habs zwar gelesen, aber hab mir nicht wirklich was raussuchen können, was mir helfen könnte in diesem fall


Ich habe einfach das problem, das so eine "primitive" sache, wo es eigentlich nur darum geht eine SELECT zu machen (die 0.05 sekunden dauert) und von dieser dann ID's aus einer anderen Abfrage abzuziehen (die auch 0.05 sekunden dauert) - das dann 26 sekunden dauert ... das geht enfach nicht in meinen kopf, das man das nicht mit "normalen einfachen" SQL Mitteln zumindest auf 0.5 Sekudnen oder so optimieren kann
Und das müsste ja eigentlich auch OHNE einen Index auf Artikel_ID funktionieren ... (Schon klar ich werde morgen den Index drauf legen ... aber ist mir noch immer nicht ganz logisch, das es nicht ohne den geht ... )

[omata]

Deine DB-Struktur sieht also folgendermaßen aus...

SQL-Code:

CREATE TABLE artikel (
  artikel_id int,
  CONSTRAINT PK_artikel 
    PRIMARY KEY (artikel_id)
);

CREATE TABLE position (
  position_id int,  
  CONSTRAINT PK_position 
    PRIMARY KEY (position_id)
);

CREATE TABLE artikel_position (
  artikel_id int,
  position_id int,  
  CONSTRAINT PK_artikel_position 
    PRIMARY KEY (artikel_id, position_id),
  CONSTRAINT FK_artikel_position_artikel 
    FOREIGN KEY (artikel_id) REFERENCES artikel (artikel_id),
  CONSTRAINT FK_artikel_position_position 
    FOREIGN KEY (position_id) REFERENCES position (position_id)
);

Ist das richtig?

[Gruber_Hans_12345]

Also das ist die original Abfrage

Code:

SELECT A.ARTIKELNUMMER 
FROM ARTIKEL A
/*Hier wäre noch joins drinnen, aber die habe ich mal weggelassen, da diese keine zeit in anspruch nehmen */
WHERE A.ID NOT IN
(
SELECT T.ARTIKEL_ID
FROM AUFTRAG VA, AUFTRAG_INFO AF, POSITIONEN T
WHERE (AF.ID = VA.INFO_ID) AND (T.AUFTRAG_ID = VA.ID) AND (VA.MesseSTATUS > 0)
AND ((CAST(AF.MesseBis AS Date) >= CURRENT_TIMESTAMP) And (Cast(AF.MesseVon AS Date) <= CURRENT_TIMESTAMP + 10))
)

[EDIT}Also das erzeugen eines Index auf POSITIONEN(ARTIKEL_ID) hat es gebracht ... nun dauerrts ca 0.05 sekunden :) ... ich verstehe zwar nicht warum er das braucht aber, vielleicht merke ich es mir nun .... [/EDIT]

[omata]

Schade, das du keine Fragen beantwortest. Naja, dein Problem ist ja gelöst, verstehen wird auch überbewertet.

[Gruber_Hans_12345]

Zitat:

Zitat von omata (Beitrag 1080269)

Schade, das du keine Fragen beantwortest. Naja, dein Problem ist ja gelöst, verstehen wird auch überbewertet.

Also dachte mit meiner original Abfarge wäre es so halbwegs geklärt wie die DB aussieht ....

so versuche es noch einmal :

Code:

CREATE TABLE ARTIKEL (
  ID INTEGER NOT NULL
, ARTIKELNUMMER VARCHAR(100)
, PRIMARY KEY("ID"));

CREATE TABLE AUFTRAG (
  ID INTEGER NOT NULL
, MesseBis TIMESTAMP
, MesseVon TIMESTMAP
, PRIMARY KEY("ID"));

CREAT TABLE AUFTRAG_INFO  (
  ID INTEGER NOT NULL
, INFO_ID INTEGER
, MESSESTATUS INTEGER
, PRIMARY KEY("ID"));

CREATE TABLE POSITIONEN  (
  ID INTEGER NOT NULL
, AUFTRAG_ID INTEGER
, ARTIKEL_ID INTEGER
, PRIMARY KEY("ID"));

[idefix2]

Zitat:

ich verstehe zwar nicht warum er das braucht aber, vielleicht merke ich es mir nun

Die SQL Engine muss doch für jede Artikel id prüfen, ob diese Artikel id in der Positionen Tabelle (nicht) vorkommt. Für jede Artikel id muss sie also die Positionen Tabelle durchgehen und schauen, ob die Artikel id vorkommt. Wenn ein Index auf die Artikel id vorhanden ist, geht das fast in Nullzeit, andernfalls muss sie aber im Schnitt die halbe Positionen Tabelle durchsuchen für Artikel Ids, die sie findet, und immer die ganze Tabelle, wenn eine Artikel Id nicht vorkommt, weil ja erst nach dem Durchsuchen der ganzen Tabelle feststeht, dass die Artikel id nicht vorkommt.

[tsteinmaurer]

Hallo,

es sind hier keine Foreign-Key Constraints angegeben. Würden FK-Constraints angelegt worden sein, dann wären automatisch auch auf den entsprechenden FK-Felder Indizes sein und das Performance-Problem wäre nie entstanden.

Nur so ein Gedanke.

Thomas

[omata]

Zitat:

Zitat von tsteinmaurer (Beitrag 1080322)

Würden FK-Constraints angelegt worden sein, dann wären automatisch auch auf den entsprechenden FK-Felder Indizes sein und das Performance-Problem wäre nie entstanden

Genau darauf wollte ich hinaus :zwinker: