[NEWSboard IBMi Forum]
  1. #1
    Registriert seit
    Nov 2007
    Beiträge
    371

    sql performance

    Hallo zusammen ,

    hat jemand nen Tipp wie man folgendes SQL performanter gestalten kann ?? Indizes etc alles vorhanden . Braucht aber ewig ..

    Select * from TEST01 WHERE

    (TEST01.LAUFNUMMER in
    (select DATEI02.PK FROM DATEI02
    join HEADER on HEADER.LAUFNUMMER = DATEI02.PK
    WHERE HEADER.TIMESTAMP > '2019-07-08 00:00:00' AND HEADER.TIMESTAMP < '2019-07-15 00:00:00' ))

    OR (TEST01.LAUFNUMMER in
    (select DATEI02.PK FROM DATEI02 WHERE DATEI02.TIMESTAMP >
    '2019-07-17 00:00:00' AND DATEI02.TIMESTAMP < '2019-07-24
    00:00:00') )



    VG

  2. #2
    Registriert seit
    Apr 2019
    Beiträge
    43
    Select * from TEST01 Where
    TEST01.LAUFNUMMER in
    (Select DATEI02.PK From Datei02

    inner join HEADER on HEADER.LAUFNUMMER = DATEI02.PK

    Where HEADER.TIMESTAMP between '2019-07-08 00:00:00' and '2019-07-15 00:00:00'

    OR DATEI02.TIMESTAMP between
    '2019-07-17 00:00:00' and '2019-07-24 00:00:00')

  3. #3
    Registriert seit
    Sep 2005
    Beiträge
    385
    Wieso in
    wieso nicht Join wenn TEST01.LAUFNUMMER=DATEI02.PK

    macht der Optimizer sicher selbst aber das ist doch besser als das IN
    Der ILEMax

  4. #4
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Das Problem ist doch, dass der Join als "Left join" definiert ist und per Where-Klausel ein Inner Join draus gemacht wird.

    Und was heißt schon "alle Indizes"?

    Du solltest den SQL etwas vereinfachen:

    Select * from TEST01 WHERE

    exists
    (select * FROM HEADER
    WHERE TEST01.LAUFNUMMER = HEADER.LAUFNUMMER
    and HEADER.TIMESTAMP > '2019-07-08 00:00:00' AND HEADER.TIMESTAMP < '2019-07-15 00:00:00' )

    OR exists
    (select * FROM DATEI02
    WHERE TEST01.LAUFNUMMER = DATEI02.PK
    and DATEI02.TIMESTAMP > '2019-07-17 00:00:00' AND DATEI02.TIMESTAMP < '2019-07-24
    00:00:00')

    Zusätzlich benütigst du einen Index jeweils über "LAUFNUMMER/PK, TIMESTAMP".

    Ein "in (Select ...)" baut i.d.R. erst eine Liste aller Werte auf, die dann sequentiell durchsucht wird.
    Im Zweifel sogar pro Satz der 1. From-Klausel, was die extreme Laufzeit erklärt.
    Du willst aber nur wissen, ob überhaupt etwas vorkommt.
    Der "exists" sucht hier aber über Index-Only-Access, da ja die Daten gar nicht benötigt werden und hört beim 1. Vorkommen bereits auf.
    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

  5. #5
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    Ich denke nicht, dass der EXISTS die beste Lösung ist.
    Die Erfahrung hat gezeigt, dass der Optimizer besser optimieren kann, wenn alle JOINs an einer Stelle erfolgen.

    Deshalb sollte, wie bereits in den vorherigen Antworten beschrieben, der JOIN nicht in den WHERE-Bedingungen, sondern in der FROM-Anweisung erfolgen. Vor allem sollte die 2 malige Verknüpfung mit der DATEI02 vermieden werden.

    Wichtig ist, dass Indices über die LaufNummer bzw. PK und die Zeitmarke vorhanden sind.

    Versuch's mal mit dem folgenden Statement
    Code:
    Select a.* 
       from TEST01 a 
            Left Outer Join DATEI02 b on     a.LaufNummer = b.PK
                                         and b.Timestamp between '2019-07-17-00.00.00.000000'
                                                             and '2019-07-24-00.00.00.000000'
            Left Outer Join HEADER h  on     a.LaufNummer = h.LaufNummer
                                         and h.Timestamp between '2019-07-08-00.00.00.000000'
                                                            and  '2019-07-15-00.00.00.000000';
    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

  6. #6
    Registriert seit
    Nov 2007
    Beiträge
    371
    Danke für die Antworten . Ich hab heute Abend selber mal rumexperimentiert an diesem SQL. Habe es auch nur zum drüberschauen bekommen und dachte mir auch mit dem IN ist nicht optimal.

    Bin dann auch auf die Lösung von Birgitta gekommen . 2 Joins aber die where bedingung mit dem Timestamp steht bei mir unten . Sollte aber nichts ändern .

    Das von Fuerchau probiere ich mal aus und schaue mal was der Visual Explain dazu sagt.


    Join vs Exists . Immer ein spannender Vergleich.

  7. #7
    Registriert seit
    Apr 2019
    Beiträge
    43
    Ab wann ist ein Left Join performanter als ein IN oder Exists?
    Muss es da irgendeine bestimmte Konstellation geben?

    Hab öfters Lösungen mit Left Joins ausprobiert und die waren bisher immer wesentlich langsamer als normale IN oder Exists Anweisungen.

    In Google gibts dafür auch einige Performance-Tests (MSSQL) bzgl. IN, Exists und Left Joins..
    Left Joins schneiden da grundsätzlich schlechter ab.

    Sind wir hier auf der DB2 und gibts deshalb andere Spielregeln?

  8. #8
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    Die Antwort ist immer "it depends".
    ... und MSSQL ist sicher keine Db2 for i.
    ... zumal ich auch nicht gesagt habe, dass ein LEFT OUTER Join performanter ist, sondern lediglich, dass die Optimierung einfacher ist, wenn alles an einer Stelle konzentriert ist.

    Es gibt zwar einen SQL Standard, den sich alle Datenbanken-Hersteller einzuhalten verpflichtet haben. Aber zum einen gibt es aktuell keine einzige Datenbank, die den Standard zu 100% abdeckt (weder die großen wie ORACLE und Db2 ... und schon gar nicht MSSQL), zum zweiten haben die einzelnen Datenbanken-Hersteller Features integriert, die nicht Standard sind und zum dritten gibt es meines Wissens keine eindeutigen Regeln, nach denen optimiert werden soll.

    Sicher die Verwendung von Binary Radix Tree Indexes zum Schnellen auffinden der Daten ist in jeder Datenbank integriert, aber in welcher Datanbank außer den Db2-Datenbanken werden EVI (Encoded Vector Indexes) eingesetzt.

    Db2 for i ist außerdem die einzige Datenbank, die im Betriebssystem integriert ist. Darin unterscheidet sie sich im übrigen auch von den anderen Db2 Datenbanken (LUW / z/OS). Deshalb kann bei der Optimierung bzw. beim schnellen Zugriff auf die Datei auf ganz andere Methoden zurückgegriffen werden als in anderen Datenbanken.

    Des weiteren parst der Query-Optimizer der Db2 for i die SQL-Statements die SQL-Statements vor der eigentlichen Optimierung und kann diese mit Hilfe einer ganzen Reihe von Methoden und Algorithmen umschreiben. Bei einfachen Abfragen, sollte der Optimizer die LEFT OUTER JOINs und die Abfragen in den WHERE-Bedingungen genau gleich optimieren. Ob er dann dabei die LEFT OUTER JOINS in WHERE-Bedingungen umsetzt oder umgekehert oder eine ganz andere Syntax verwendet bleibt dahingestellt.
    Da der Optimizer jedoch ein dummes Programm (oder auch viele dumme Programme und Prozeduren) ist, ist es vermutlich einfacher einen JOIN in den FROM-Anweisungen als einen vielleicht auch noch verschachtelten Sub-Select in den WHERE-Bedingungen.

    Auf alle Fälle solltest Du nicht Äpfel (MSSQL) mit Birnen (Db2 for i) vergleichen, sondern die Messungen und Vergleiche ob LEFT OUTER oder JOIN in den WHERE-Bedinungen schneller ist oder nicht auf der IBM i durchführen.

    ... ach ja, und dann gibt es da ja auch noch eine ganze Reihe von anderen Faktoren, die die Performance beeinflussen können, Hardware, Release, Memory, Pool, Optionen in der Abfrage-Options-Datei, Optimierungsziel etc.

    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

  9. #9
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Manchmal beginne ich hier zu verzweifeln:

    Fakt 1:
    Wenn die Where-Klauses die Einschränkung auf eine Join-Table macht, wird automatisch ein Inner Join draus!
    Warum? Weil die Prüfung auf ein Left-Join-Feld die NULL-Werte ausschließt. Dies optimiert der Optimiser in der Regel ebenso automatisch zu einem Inner-Join.
    Wenn du den Teil der Where-Klausel in die On-Klausel des Left-Jons packst, wirst du sehen, dass eben auch die Daten kommen, die nicht im Left join vorhanden sind und somit NULL-Werte liefern.
    D.h., dass Birgittas Select ganz einfach mit einem "Select * from Test01" abgekürzt werden kann, da ja aus den Join-Tabellen gar keine Felder benötigt werden, die nur bei gefundener Bedingung Werte ungleich NULL aufweisen.

    Fakt 2:
    Ein Join, nun egal ob Left/Inner, kann neben einer 1:1 aber auch eine 1:N-Beziehung liefern.
    Auf Grund des Ursprungselects, gehört ja der Join eigentlich als Inner Join kodiert, da die Ursprungs Whereklausel ja auf jeden Fall per "in" die Existenz geprüft hat.

    Birgittas Select, geändert auf Inner Join, geht also von der Annahme aus, dass per Join die Laufnummer innerhalb der Zeit nur 1x vorkommt.
    Da das ebenso auch für den 2. Join gilt, darf in dieser Konstellation die Laufnummer entweder nur im 1. Join oder nur im 2. Join vorkommen.
    Ansonsten wird die Zeile aus TEST01 mindesten 2x geliefert.
    Im Moment mag das vielleicht so sein, aber gilt dies generell für dieses Datenmodell?

    Nun stellt sich mir die Frage, ob in den Daten tatsächlich nur eine 1:1-Konstallation vorliegt.
    Wenn die Laufnummer aber eben mit verschiedenen Zeitmarken vorkommt, ist das eine 1:N-Beziehung und die Zeile aus Test01 wird mehrfach geliefert.
    Ebenso gilt, dass ein "A->Join B and A->Join C" mindestens eine 1:2-Beziehung ist und zu einer Verdoppelung der Datenzeile TEST01 führen kann.

    Fakt 3:
    Eine Prüfung mittels "In" versus "Exists" in einer Where-Klausel kann nie einfach mit einem Join ersetzt werden. Insbesonders dann, wenn ich u.U. mehrere In/Exists, wie oben, benötige.

    Und hier gilt eben wieder die Index-Prämisse:
    Habe ich einen Index über das Feld der Beziehung und die Where-Klausel des Subselects, ist ein Exists immer schneller als ein "in", da nur 1x zugegriffen werden muss und hier noch ein Index-Only-Access erfolgen kann und wahrscheinlich wird.

    Ein "in" baut im Gegensatz zu Exists je Ergebniszeile die Liste immer wieder neu auf um diese dann sequentiell zu durchsuchen. Dies ist u.U. auf der DB2/400 gar nicht zu merken, wenn die Ergebnisliste sehr kurz ist und auf Grund des DB-Caches keine Plattenzugriffe nötig sind.
    Beim SQL-Server z.B. ist dies nicht der Fall. Hier geht dieser bei komplexeren Suchen per "in" durchaus in die Knie.

    Ich persönlich verwende den "In" nur noch mit Konstanten ansonsten verwende ich nur den [not] Exists.
    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

  10. #10
    Registriert seit
    Apr 2019
    Beiträge
    43
    Zitat Zitat von Fuerchau Beitrag anzeigen
    Manchmal beginne ich hier zu verzweifeln:


    Ein "in" baut im Gegensatz zu Exists je Ergebniszeile die Liste immer wieder neu auf um diese dann sequentiell zu durchsuchen. Dies ist u.U. auf der DB2/400 gar nicht zu merken, wenn die Ergebnisliste sehr kurz ist und auf Grund des DB-Caches keine Plattenzugriffe nötig sind.
    Beim SQL-Server z.B. ist dies nicht der Fall. Hier geht dieser bei komplexeren Suchen per "in" durchaus in die Knie.
    Ist das tatsächlich so?
    Warum nicht erst die Liste der IN Bedingung als distinct aufbauen und dann das Ergebnis mit der "Basistabelle" fetchen?
    Das ist doch der effizienteste Weg zum Ergebnis zu kommen?
    Hast du vielleicht nachvollziehbare Quellen zu der Thematik IN/Exists ?

  11. #11
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    SQL arbeitet nicht mit temporären Tabellen für die Abfrage, so wie wir uns das immer vorstellen.
    Meisten werden aus Performancegründen nur fehlende Indizes aufgebaut.
    Das Resultset kann schon mal in einer temporären Tabelle landen, aber das ist auch nicht sehr häufig so.
    Dies merkt man dann daran, dass beim Lesen eines Resultsets bei komplexen Gruppierungen immer wieder Pausen auftreten wo dann nichts passiert.
    Beim STRSQL z.B. kommen viele Ergebnisse erst sehr schnell. Es macht sich aber kaum jemand die Mühe, das Ergebnis mal durchzublättern.
    Da steht der Job und in der Statuszeile werden plötzlich Milliarden Zugriffe gezählt, obwohl doch gar nicht soviele Quelldaten vorhanden sind. Dies liegt dann z.B. am fehlenden Index, die Tabelle ist u.U. zu klein als das es sich lohnt und so wird dann mal eben für jede Masterzeile ein Tablescan pro Jointable durchgeführt.
    Wer z.B. glaubt, dass eine "Common Table Expression" (CTE) eine tatsächliche Zwischentabelle darstellt der irrt sich. Diese dient ausshließlich dafür uns das Tippen zu erleichtern.
    Alternativ könntest du auch je CTE entsprechend oft eine "Derived Table" einbauen, also "select * from (select * from B) A", was in der Performance auf dasselbe rausläuft.
    Ich habe schon viele SQL's analysiert und umgebaut bis hin zu wirklichen temporären Tabellen in der QTEMP. Also CTE-Ausdrücke vorab in die QTEMP gestellt, einen Index dazu und dieses Ergebnis (oder auch mehrere) dann im endgültigen Query zusammengefasst.
    Somit konnte ich Laufzeiten von Minuten auf Sekunden reduzieren.
    Eine große Hilfe ist meist einfach der DEBUG-Modus mit den Hinweisen und Vorschlägen.
    Auch die Zugriffspfadanalyse eines SQL's ist da schon aussagefähig. Immerhin wird da nie darauf hingewiesen, dass tatsächlich eine Tabelle erstellt wird sondern immer nur, welche Indizes verwendet, wann ein Tablescan durchgeführt wird und welche Pfade empfehlenwert wären.
    Dies kann man dann auch z.B. per DSPJOB, Auswahl 14, sehen wie da die Dateizugriffe hochgezhählt werden. Teilweise erfolgen da für einen "Select into ", also 1 Ergebniszeile, durchaus mehrere 1000 Zugriffe!
    Diese Empfehlungen sind i.Ü. nie statisch sondern können sich je nach Datenvolumen plötzlich ändern.
    Bis 100.000 Zeilen gings noch gut, bei 500.000 Zeilen schon weniger und bei 1.000.000 wurde plötzlich gestreikt. Wohl gemerkt, immer beim selben SQL, allerdings über die Jahre.
    Was Birgitta da mit "die Datenbank ist ja bereits im System enthalten" für einen Vorteil sieht (außer halt die Lizenzkosten) weiß ich nicht, aber das ist kein echter Vorteil.
    Es kommt immer darauf an, wie gut ist SQL implementiert.
    Die AS/400 ist da gewachsen und hat auch wirklich eine sehr gute Datenbank. Allerdings basiert sie immer noch auf dem guten alten PF/LF-System, denn es sind immer noch native-Zugriffe ohne SQL möglich! M.a.W.: SQL ist eine Zwischenschicht zwischen RPG/COBOL/usw. und eben den originären QDBPUT und QDBGET-Befehlen. Deshalb ist ein RLA (Record Level Access) immer noch in vielen Punkten messbar schneller. Während SQL noch bei der Optimierung ist, ist das RPG schon fertig.
    Vergleiche da einen simplen CHAIN mit einem "Select * into : HostDS from Myfile where K1='A' and K2=5 and ...".

    Andere DB-Systeme haben diesen Overhead nicht und sind deshalb auch nicht unbedingt schlechter.
    Wobei zugegebn, der Microsoft-SQL-Server auch nicht meine erste Wahl ist.

    Und was soll der Hinweis auf Vector-Indizes (EVI's)?
    Mir ist doch egal, welche Art der Indizierung eine DB macht, Hauptsache sie macht es gut.
    Die DB2 verwendet auch schon mal durchaus mehrere Indizes, erstellt eine "RRN"-Bit-Map per XOR und das Ergebnis sind die RRN's, die zu verarbeiten sind.

    Was das Umschreiben der SQL's angeht, so bin ich da auch schn bei Release-Wechseln reingefallen.
    Da gab es bis V5R4 den "Select * from TEIL where TEFIRM = '1' and TEWKNR = 000 and ..."
    Nun, wer Infor-XPPS kennt sieht, dass die Hochkommata fur das Werk 000 fehlen, das hat allerdings keiner gemerkt, da der Optimizer das auf Grund des Feldes TEWKNR erkannt hat, die Hochkomma selber dazufügt und den Index verwendete.
    Dann kam V6R1 und der Optimzer änderte seine Priorität.
    Sämtliche Konstanten werden zuerst analysiert, in interne Hostvariablen kopiert und Parametermarker gesetzt. Auf Grund der Typisierung DEC(3, 0) wurde die Abfrage geändert in "cast(TEWKNR as dec(3, 0)) = ?".
    Das Ergebnis war nun:
    1. Keine Verwendung eines Indizes
    2. Durchführung eines Tablescans
    3. Irgend ein Idiot hatte mal in das Feld TEWKNR einen nicht numerischen Wert reingestellt und der SQL starb immer genau an dieser Stelle.
    Leider hat dies ein paar Tage niemand bemerkt, da ja der Programmierer davon ausging, dass dieser SQL auf Grund der Datenlage nie auf einen Fehler laufen konnte und deshalb keinen SQLCODE abfragte.
    Soviel zum Optimizer und dem Umschreiben.

    Und zu guter Letzt:
    Leider ist das meiste der SQL-Interna nicht dokumentiert. Man kann sich mal die Mühe eines Jobtraces (Call-Aufrufe) machen bei einem SQL-Programm oder einem RLA-Programm. Man kommt da aus dem Staunen nicht heraus.
    Schade, ich hatte mal bei V5 oder V6 einen SQL-Fehler gemeldet. Hierzu bekam ich irgend eine Anweisung für erweiterte Ausgaben des SQL-Optimizers, die ich mir leider nicht gemerkt habe!
    Da bekam man einen schönen Spool mit über 100 Seiten, der den gesamten Analysevorgang und auch die Umbauten protokollierte. Da kam man aus dem Staunen wirklich nicht mehr heraus.

    Und was deine IN-Liste angeht, ja so würde ich das machen, SQL spart sich aber wirklich das Aufbauen von temporären Tabellen.

    Fazit:
    Ich finde SQL wirklich gut und es erleichtert ungemein die Arbeit.
    Aber man muss sich genau überlegen was und wie man es macht, denn SQL ist auch nur von Menschen programmiert.
    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

Similar Threads

  1. Antworten: 6
    Letzter Beitrag: 24-02-19, 22:15
  2. System Performance Analyse und Performance Tuning
    By Bernstein in forum NEWSboard Server Job
    Antworten: 0
    Letzter Beitrag: 05-08-14, 18:34
  3. IFS-Performance
    By NorBo in forum IBM i Hauptforum
    Antworten: 6
    Letzter Beitrag: 29-04-03, 16:12
  4. Performance
    By mk in forum IBM i Hauptforum
    Antworten: 1
    Letzter Beitrag: 27-06-02, 10:32
  5. Frage zu QRY-Performance
    By hs in forum IBM i Hauptforum
    Antworten: 4
    Letzter Beitrag: 27-08-01, 13:29

Berechtigungen

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