Langsames SQL wegen großer IN() Anweisung

[xenofob]

Mit der Abfrage werden denke ich keine Daten gebuffert.
Vielleicht liegt das an der Where-Bedingung im join..

Schon mal das probiert?

select feld1 from tabelle1 
where key1 in 
(Select key2 from Tabelle2 where tabelle2.nummer in (csvstring))

[B.Hauser]

Wie sieht es mit den Indices aus?
Habt Ihr einen Index für die 2. Tabelle mit NUMMER (1.Schlüssel) und dann KEY (folg.Schlüssel)?
Wenn nein leg mal an.

Birgitta

[andreaspr@aon.at]

Ich würde es mit einem LEFT JOIN machen und die Reihenfolge des JOINs ändern:

select feld1 from tabelle2
left join tabelle1 on key1=key2
where tabelle2.nummer in (12345, 24575, 58713, <... 2000 weitere Werte ...>, 87548)

lg Andreas

[Fuerchau]

"left" ist der Default, wenn man das beim Join (wie oben gezeigt) weglässt.
Hinzu kommt, dass eigentlich ein Inner-Join benötigt wird, da die Where-Klausel nicht auf NULL abfragt.

Ich stimme da eher Birgitta zu.
Vielleicht kann das dann per "exists" (automatisch) aufgelöst werden.
Der Index kann entweder über "Key2, Number" oder umgekehrt erstellt werden.

Ich kann mir andererseits nicht vorstellen, dass Hibernate hier so schlecht ist. D*B kann da ggf. mehr dazu sagen.
Vielleicht stimmt das Datenmodell nicht ganz?
Außerdem besteht die Gefahr, dass der SQL irgendwann größer 32K wird (falls die Grenze noch besteht).

[BenderD]

... Hibernate ist so gut oder so schlecht wie man es verwendet. Auch hier sitzt das Problem eher vor dem Bildschirm.

[xenofob]

Left join ist definitiv nicht der default join..
Ohne "Schnittmengenrichtungsangabe" (left, right, outer.. ) ist inner der default.

https://stackoverflow.com/questions/...and-inner-join

[dschroeder]

Vielen Dank für eure Antworten. Leider hat kein Vorschlag eine Beschleunigung gebracht. Ich habe das Statement umformuliert und auch die Indizes nochmal kontrolliert und neue angelegt. Hat alles keinen Effekt. Der Index Advisor verlangt auch keine weiteren Indizes mehr.

Wenn ich das Statement mit der großen IN-Anweisung alleine ausführe, ist das Ergebnis sehr schnell da (weniger als 1 Sekunde). Sobald der join mit den anderen Tabelle dazukommt (egal in welcher Form: join, subselect, CTE) wird es langsam.

Wir haben noch etwas herausgefunden. Wenn man dir Anzahl der IN-Elemente verringert, wird es ab einem bestimmten Punkt signifikant schneller. Bis 1024 Elemente scheint das ganze schnell zu gehen. Wenn man die Anzahl auf 1025 erhöht, wird es wieder extrem langsam.

Aber mein Kollege programmiert seine Anwendung bereits um, sodass er das Problem ganz anders löst. Unser akutes Problem ist damit behoben.

Nochmals vielen Dank für alle Tipps.

Dieter

[dschroeder]

Außerdem besteht die Gefahr, dass der SQL irgendwann größer 32K wird (falls die Grenze noch besteht).

Unter 7.3 ist die maximale Länge eines SQL Statement 2 MB.

[Fuerchau]

Bzgl. des Joins stimmts, Inner ist der default.
Allerdings bzgl. Where-Klausel ist das nicht relevant, da zumindest die DB2/400 dann automatisch sowieso einen Inner Join macht.
Bewusst einen Left Join, der durch where zum inner wird, würde ich da sowieso nicht einführen.

Die 1024 belegt nur, dass die Entwickler nicht mit mehr Elementen gerechnet haben und bis 1024 ggf. mit einer schnellen internen Hash-Tabelle arbeiten. Ab 1025 wird u.U. eine sequentielle Suche daraus.
Da kann man sicherlich mal eine Fehlermeldung an die IBM schreiben.

[dschroeder]

Ich habe nochmal etwas getestet: Die Variante mit dem ... where exists ... ist etwa doppelt so schnell wie alle anderen Varianten (ca. 2,5 Sekunden).

Aber wie gesagt, mein Kollege baut das jetzt alles ganz anders.

[Fuerchau]

Manchmal macht es auch Sinn, den SQL einfach umzudrehen:

select feld1 from tabelle2
[inner] join tabelle1 on key1=key2
where tabelle2.nummer in (12345, 24575, 58713, <... 2000 weitere Werte ...>, 87548)

und über Tabelle3.Nummer ggf. einen Index zu erstellen.