-
Langsames SQL wegen großer IN() Anweisung
Hallo,
hat vielleicht jemand eine Idee, um folgendes SQL Problem zu lösen:
Wir haben eine SQL Abfrage, die per Programm generiert wird (auf Java Seite per Hibernate). Die Abfrage dauert sehr lange (ca. 5 Sekunden), obwohl nur ein paar tausend Datensätze selektiert werden.
Woran das ganze liegt, ist uns schon klar:
Das Statement hat in etwa folgende Form:
Code:
select feld1 from tabelle1
join tabelle2 on key1=key2
where tabelle2.nummer in (12345, 24575, 58713, <... 2000 weitere Werte ...>, 87548)
Das ganze geht schnell, wenn die IN-Anweisung nicht zu viele Werte beinhaltet. 20 Werte sind überhaupt kein Problem. Aber wird haben da manchmal über 2000 Werte in der IN-Klausel. Dann dauert das extrem lange.
Visual Explain und der Index Advsior konnten keine Ratschläge geben. Vielleicht hat jemand von Euch ja noch eine Idee, wie man die lange IN-Anweisung anders kodieren könnte.
Dieter
-
... wenn die Werte aus einem Resultset stammen, kann man einen inner join draus machen.
-
Vielen Dank für die Anwort.
Ich glaube aber, genau das geht nicht. Die Werte werden, soweit ich weiß, per Programmcode ermittelt.
-
Ich habe eben mal (in einer interaktiven SQL Oberfläche) etwas rumgetestet. Wenn ich die Anweisung so zerlege, dass ich zunächst die Daten nur aus der tabelle2 selektiere und das Ergebnis in eine temporäre Tabelle in QTEMP speichere, dauert das ca. 1 Sekunde.
Wenn ich dann den join der tabelle1 gegen die QTEMP Tabelle laufen lasse, dauert es nochmal ca. 0,3 Sekunden. Insgesamt ist es also deutlich schneller als der Aufruf in einer einzigen Anweisung mit join.
Aber ich habe dann anstatt einer, leider zwei einzelne SQL Anweisungen. Ich weiß nicht, ob das in der Java Anwendung so einfach funktioniert.
Wenn ich quasi genau das gleiche mit einer common table expression mache, dauert es wieder lange (> 5 Sekunden).
-
Mit der Abfrage werden denke ich keine Daten gebuffert.
Vielleicht liegt das an der Where-Bedingung im join..
Schon mal das probiert?
Code:
select feld1 from tabelle1
where key1 in
(Select key2 from Tabelle2 where tabelle2.nummer in (csvstring))
-
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
-
Ich würde es mit einem LEFT JOIN machen und die Reihenfolge des JOINs ändern:
Code:
select feld1 from tabelle2
left join tabelle1 on key1=key2
where tabelle2.nummer in (12345, 24575, 58713, <... 2000 weitere Werte ...>, 87548)
lg Andreas
-
"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).
-
... Hibernate ist so gut oder so schlecht wie man es verwendet. Auch hier sitzt das Problem eher vor dem Bildschirm.
-
Left join ist definitiv nicht der default join..
Ohne "Schnittmengenrichtungsangabe" (left, right, outer.. ) ist inner der default.
https://stackoverflow.com/questions/...and-inner-join
-
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
-
Zitat von Fuerchau
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.
Similar Threads
-
By svit in forum IBM i Hauptforum
Antworten: 14
Letzter Beitrag: 18-06-15, 09:08
-
By a.wojcik in forum NEWSboard Programmierung
Antworten: 24
Letzter Beitrag: 16-01-15, 15:18
-
By dino in forum IBM i Hauptforum
Antworten: 3
Letzter Beitrag: 21-05-14, 08:57
-
By Cassius in forum IBM i Hauptforum
Antworten: 3
Letzter Beitrag: 05-03-02, 19:28
-
By MKnapp in forum IBM i Hauptforum
Antworten: 1
Letzter Beitrag: 19-12-00, 06:43
Berechtigungen
- Neue Themen erstellen: Nein
- Themen beantworten: Nein
- You may not post attachments
- You may not edit your posts
-
Foren-Regeln
|
Erweiterte Foren Suche
Google Foren Suche
Forum & Artikel Update eMail
AS/400 / IBM i
Server Expert Gruppen
Unternehmens IT
|
Kategorien online Artikel
- Big Data, Analytics, BI, MIS
- Cloud, Social Media, Devices
- DMS, Archivierung, Druck
- ERP + Add-ons, Business Software
- Hochverfügbarkeit
- Human Resources, Personal
- IBM Announcements
- IT-Karikaturen
- Leitartikel
- Load`n`go
- Messen, Veranstaltungen
- NEWSolutions Dossiers
- Programmierung
- Security
- Software Development + Change Mgmt.
- Solutions & Provider
- Speicher – Storage
- Strategische Berichte
- Systemmanagement
- Tools, Hot-Tips
Auf dem Laufenden bleiben
|
Bookmarks