[NEWSboard IBMi Forum]
Seite 1 von 2 1 2 Letzte
  1. #1
    Registriert seit
    Jan 2012
    Beiträge
    1.102

    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

  2. #2
    Registriert seit
    Mar 2002
    Beiträge
    5.286
    ... wenn die Werte aus einem Resultset stammen, kann man einen inner join draus machen.
    AS400 Freeware
    http://www.bender-dv.de
    Mit embedded SQL in RPG auf Datenbanken von ADABAS bis XBASE zugreifen
    http://sourceforge.net/projects/appserver4rpg/

  3. #3
    Registriert seit
    Jan 2012
    Beiträge
    1.102
    Vielen Dank für die Anwort.

    Ich glaube aber, genau das geht nicht. Die Werte werden, soweit ich weiß, per Programmcode ermittelt.

  4. #4
    Registriert seit
    Jan 2012
    Beiträge
    1.102
    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).

  5. #5
    Registriert seit
    Apr 2019
    Beiträge
    43
    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))

  6. #6
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    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
    Birgitta Hauser

    Anwendungsmodernisierung, Beratung, Schulungen, Programmierung im Bereich RPG, SQL und Datenbank
    IBM Champion seit 2020 - 4. Jahr in Folge
    Birgitta Hauser - Modernization - Education - Consulting on IBM i

  7. #7
    Registriert seit
    Aug 2003
    Beiträge
    1.508
    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

  8. #8
    Registriert seit
    Feb 2001
    Beiträge
    20.206
    "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).
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  9. #9
    Registriert seit
    Mar 2002
    Beiträge
    5.286
    ... Hibernate ist so gut oder so schlecht wie man es verwendet. Auch hier sitzt das Problem eher vor dem Bildschirm.
    AS400 Freeware
    http://www.bender-dv.de
    Mit embedded SQL in RPG auf Datenbanken von ADABAS bis XBASE zugreifen
    http://sourceforge.net/projects/appserver4rpg/

  10. #10
    Registriert seit
    Apr 2019
    Beiträge
    43
    Left join ist definitiv nicht der default join..
    Ohne "Schnittmengenrichtungsangabe" (left, right, outer.. ) ist inner der default.

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

  11. #11
    Registriert seit
    Jan 2012
    Beiträge
    1.102
    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

  12. #12
    Registriert seit
    Jan 2012
    Beiträge
    1.102
    Zitat Zitat von Fuerchau Beitrag anzeigen
    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

  1. Wandlungsfehler wegen PGMINFO
    By svit in forum IBM i Hauptforum
    Antworten: 14
    Letzter Beitrag: 18-06-15, 10:08
  2. Frage wegen DDS, CONCAT Funktion
    By a.wojcik in forum NEWSboard Programmierung
    Antworten: 24
    Letzter Beitrag: 16-01-15, 16:18
  3. Antworten: 3
    Letzter Beitrag: 21-05-14, 09:57
  4. Optimierung SQL Anweisung
    By Cassius in forum IBM i Hauptforum
    Antworten: 3
    Letzter Beitrag: 05-03-02, 20:28
  5. Antworten: 1
    Letzter Beitrag: 19-12-00, 07:43

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • You may not post attachments
  • You may not edit your posts
  •