[NEWSboard IBMi Forum]
Seite 1 von 3 1 2 ... Letzte
  1. #1
    Registriert seit
    Mar 2019
    Beiträge
    33

    Durch SQL-Trigger eine 60-fache Laufzeit des betroffenen Progamms.

    Hallo Zusammen,
    ich habe aktuelle das Problem, dass sich die Laufzeit einer Statistikberechnung von aktuell ca. 23 Min, durch die Erstellung eines SQL-Triggers für Insert und eines SQL-Triggers für den Update, auf die Ergebnistabelle rechnerisch, auf einen Tag verlängert.

    Also die Statistikberechnung erzeugt im ersten Schritt eine Liste aller Vorgänge, die relevant sind (ca. 6,7 Mio Datensätze). Für diesen ersten Schritt benötigt das System derzeit ca. 12 Minuten.
    Das macht Rechnerisch ca. 550.000 Sätze in der Minute. Die Tabelle in der die Sätze geschrieben werden ist eine DDS beschriebene Datei.
    Die Tabelle in die ich die Sätze duplizieren möchte ist per SQL create erzeugt.
    In der alten Dateibeschreibung sind 2 Datumfelder und 2 Uhrzeiten in nummerischer Form zu speichern.
    Im ersten Schritt werden die Daten aber nicht gesetzt. Somit ist das jeweilige Datum und die Uhrzeit = 0.
    In der neuen Tabelle setze ich das Datum mit einer Benutzerdefinierten Funktion in einen Timestamp um, aber nur Wenn das Datum nicht 0 ist (case when).
    Der Insert mit einem Select auf das entgültige Ergebnis dauert mit RUNSQLSTM ca. 17 Minuten.
    Soweit so gut:
    Jetzt habe ich einen einfachen Insert-Trigger und einen Update-Trigger, der mit begin atomic und IF old <> new geänderte Sätze in der neuen Tabelle aktualisiert, gebaut.

    Nach der Aktivierung der Trigger musste ich den Job abbrechen und die Trigger deaktivieren, da schon im ersten Schritt (also nur Insert der Schlüsselfelder, ohne Datum und Uhrzeit) nach einer halben Stunde nur knapp 500.000 Sätze geschrieben wurden.
    Das Programm, das die alte Datei befüllt ist ein RPG-Programm, dass noch mit GOTO's arbeitet aber, das RPG-Programm sollte doch damit nichts zu tun haben, da die Trigger ja an der Datei hängen, oder?

  2. #2
    Registriert seit
    Apr 2019
    Beiträge
    43
    Hallo,

    ein Trigger ist hier glaube ich nicht sinnvoll.
    Ja sollte wenig mit dem GOTO Programm zu tun haben.

    Paar Gedanken:
    Warum die neue Datei nicht per CL CRTDUBOBJ erstellen?

    Warum dupliziert man erst eine Datei per SQL um danach noch ein Update zu machen?
    Könnte man die benutzerdefinierte Funktion nicht beim Insert nutzen? (Damit das Update wegfällt)

    Könnte man die Formatierung von Datum in Timestamp nicht mit einer SQL Funktion lösen, damit man die im Insert direkt nutzen kann?

    Könnte man die 6,7 Mio Datensätze nicht mit einem zusätzlichen Key versehen und dann nur noch eine neue Datei erstellen, die den Key + Datumsfelder + Uhrzeiten beinhaltet? (Redundanzen vermeiden)

    Ist die neue Datei nur eine Hilfsdatei/Arbeitsdatei oder wird die täglich von vielen Programmen später genutzt?

  3. #3
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Da liegt aber dein Trigger eben im Argen.
    Ohne Beispiel-Code kann man nur Raten:
    "geänderte Sätze in der neuen Tabelle aktualisiert"
    Dann prüfe mal, ob da nicht ein Index für den Update fehlt, was dann zu dieser extremen Laufzeit führt.

    Hinweise kann man sehr schön bei eingeschaltetem DEBUG-Mode sehen.
    Was i.Ü. auch sehr viel Zeit kostet sind typische NULL-Fehler.
    Im SQL können alle Variablen auch NULL sein, was dann beim Update/Insert fehlschlagen kann.
    Im ILERPG gibt es keine Variablen, die wahlweise NULL oder einen Wert haben. Dies wird mit NULL-Anzeigern gelöst (bei RLA via %nullind).
    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

  4. #4
    Registriert seit
    Mar 2019
    Beiträge
    33
    Wie beschriben ist nur der einfache INSERT-Trigger relevant, ein Update findet hier noch garnicht statt. Das Datum ist 0!
    Trigger:
    create trigger MIVGES000U after insert on MIVGES00 referencing new as nnn for each row mode db2sql
    insert into ANALYSES.total_rental_days_0 values(nnn.MGFIRNUM,
    nnn.MGKUNNUM,
    nnn.MGFILNUM,
    nnn.MGVERNUM,
    nnn.MGKFZNUM,
    nnn.MGKFZART,
    nnn.MGKFZGRU,
    nnn.MGBERGRU,
    nnn.MGUMSGES,
    nnn.MGUMS001,
    nnn.MGUMS002,
    nnn.MGUMS003,
    nnn.MGUMS004,
    nnn.MGUMS005,
    nnn.MGTAGGES,
    nnn.MGBERSTD,
    case when nnn.MGDATICO != 0
    then BUBITOOLS.getTimestamp((DIGITS(nnn.MGDATICO) || DIGITS(nnn.MGTIMICO) || '00'), '*YMD')
    end,
    case when nnn.MGDATICI != 0
    then BUBITOOLS.getTimestamp((DIGITS(nnn.MGDATICI) || DIGITS(nnn.MGTIMICI) || '00'), '*YMD')
    end,
    substr(digits(nnn.MGDATICI), 1, 4),
    nnn.MGKILGEF);

    Ich glaube nicht das beim Insert ein Index hilfreich wäre, da er die Daten gelifert bekommt und nur ein anhängen eines neuen Datensatzes veranlasst.

    Was ich aber gefunden habe ist, dass der Wert QQRYDEGREE auf *NONE steht.
    Der hat wohl was mit der Parallelverarbeitung zu tun.

    Auszug aus der Hilfe zum Systemwert:
    Es gibt zwei Arten von Parallelverarbeitung: Parallele Ein-/Ausgabeverarbeitung und Symmetric Multiprocessing (SMP). Bei der parallelen E/A-Verarbeitung kann der Datenbankmanager für jede Abfrage mehrere Tasks verwenden. Die Verarbeitung der Zentraleinheit (CPU-Verarbeitung) erfolgt weiterhin seriell.

    Vielleicht liegt es ja daran?
    Oder gibt es noch eine Einstellmöglichkeit für die Triggerverarbeitung nach dem Motto fire and forget?

  5. #5
    Registriert seit
    Mar 2019
    Beiträge
    33
    Der Trigger dient nur für den Übergang um einen richtige Timestamp zu haben.
    Diese Datei ist ja nur eine von vielen Läufen, auf bassis derer wieder weitere Auswertungen laufen.
    Die neue Tabelle soll dann die Basis sein.
    Wenn ich zum Ursprung der Auswertungen vordringe, dann sollen eigentlich alle RPG-Programme durch entsprechende View's ersetzt werden.

  6. #6
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    Bei einem Trigger wird ein dynamischer Programm-Aufruf ausgeführt, was natürlich Zeit kostet!
    Eventuell sollte man den Trigger nur einmal pro Statement aktivieren (FOR EACH STATEMENT) aktivieren.
    Damit würde man die Anzahl der dynamischen Programmaufrufe auf 1 reduzieren.
    Die Änderungen erfolgen dann in dem Trigger, d.h. die Datensätze stehen als Tabelle zur Verfügung und dann kann man mit einem ganz normalen Cursor durch die Datensätze lesen und die Änderungen hingerlegen.
    Des weiteren würde mich interessieren was hinter BUBITOOLS.getTimestamp steckt.
    Vielleicht auch wieder ein Programm? Sollte es sich um eine Funktion in einem Service-Programm handeln, sollte dies mit Aktivierungsgruppe *CALLER erstellt sein.
    Sollte es sich um eine SQL-Funktion handeln, sollte DETERMINISTIC die Funktion mit DETERMINISTIC erstellt worden sein.
    ... und warum nicht direkt mit SQL konvertieren und zwar gleich beim Insert und nicht erst durch einen Trigger? Timestamp(Digits(NumYYYYMMDD) concat Digits(NumHHMMSS))

    Die Frage, die sich mir aber als aller erstes stellt, warum wird eine so riesige Tabelle dupliziert?
    Kann an dieser Stelle nicht mit zusätzlichen Views, die die Datums- und Zeit-Werte konvertieren gearbeitet werden?

    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
    Mar 2019
    Beiträge
    33
    Hallo Brigitta,
    die SQL-Funktion ist mit DETERMINISTIC erstellt. Ausserdem wird diese Funktion doch zu diesem Zeitpunkt noch garnicht aufgerufen, da im Trigger diese nur aufgerufen wird wenn das Datum != 0 ist.
    Beim Insert sind die Datumsfelder aber 0 und in der Kopie sollen die Timestamps dann null sein.
    Das funktiuoniert auch.

    Ich habe den oben erwähnten Systemwert auf unserer Testmaschine (ein Prozessor weniger Arbeitsspeicher als die Produktion und nur mechanische Platten) QQRYDEGREE auf *OPTIMIZE gestellt.
    Es schein so zu sein, dass dieser Wert schon Auswirkungen hat. Nach einer Stunde hatte diese Maschine vorgestern nur ca 200.000 Sätze geschrieben. Jetzt hat sie aber schon 750.000 Sätze inserted.

    Bei Views bekomme ich auf den Maschinen ebenfalls Probleme, da diese ja als Berechnungsgrundlage für weitere Statistikauswertungen.

    FOR EACH STATEMENT hört sich interesant an und werde ich auch noch ausprobieren.
    Aber jetzt werde icherstmal abwarten was der Query-Degree bringt.

    Ausserden würde ich als nächsten Schritt einfach mal auf der Testmaschine die DS-Datei durch eine 1:1 DDL-Definierte Tabelle ersetzten.

    Das Problem mit dem Datum habe ich auf dem System mit vielen Tabellen, wo ich auch keinen Quellcode für habe. Deshalb diese Funktion.

    Zur Info hier die Einstellungen der Funktion, die aber erst beim2ten Durchlauf (UPDATE) zum tragen kommen:

    returns timestamp
    language SQL
    specific getTimestamp
    deterministic
    reads SQL data
    called on null input
    allow parallel
    set option alwblk = *allread,
    alwcpydta = *optimize,
    commit = *none,
    decresult = (31, 31, 00),
    dftrdbcol = *none,
    dyndftcol = *no,
    dynusrprf = *user ,
    srtseq = *hex

    Brigitta, wir kennen uns von Bucher Reisen, dort hast du uns SQL und SQLRPG noch unter dem 6er Relase gezeigt. Bei Bucher hatte ich dieses merkwürdige Verhalten nicht gehabt. Also gehe ich davon aus, dass irgend eine Systemeinstellung hier anders ist. Nur Welche?

    Das erste was mir aufgefallen ist, ist das der CHGQRYA die Tabelle QAQQINI in der QUSRSYS erwartet, diese aber nur in der QSYS vorhanden ist. Dann gibt es in der Tabelle viele Parameter, die ich nicht direkt einem SYSVALUE zuordnen kann. Hast Du da Erfahrungswerte ?

    Andreas Funke

  8. #8
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Und was machen deine BUBITOOLS was SQL nicht kann?
    Auch hier wird u.U. Zeit verbraten (ACTGRP, *INLR, ...).

    Ein dynamischer Call in SQL kostet genauso viel wie ein statischer Call, da sich SQL die Adresse beim 1. Aufruf merkt und nicht jedes mal neu ermittelt.
    Die RPG-Runtime bzw. MI per INZ ermittelt ja die Call-Adressen auch bereits beim Aufruf.
    Jeder Folgecall ist somit kaum langsamer als ein GOTO. Wenn dem nicht so wäre könnte man SQL nämlich gleich vergessen, bei dem was da so alles aufgerufen wird.

    QQRYDEGREE funktioniert, wenn man auch Parallelfeature aktiviert hat. Ich weiß nicht, ob das nicht immer noch kostenpflichtig ist. Das hat nichts mit parallelem SQL in mehreren Jobs zu tun.
    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 2019
    Beiträge
    33

    Vielen Dank erstmal...

    ... für die schnellen Antworten.

    - Also ich Ändere erstmal den SYSVAL QQRYDEGREE,
    - dann wandel ich die Datei in eine SQL definierte Tabelle (1:1) - caching!?
    - Als nächstes kommt ON EACH STATEMENT anstelle von EACH ROW.

    Gibt es noch andere Vorschläge?

    Kurz zum Produktivsystem:


    • System: 8286-42A
    • 1 x LPAR auf dem System, Kein VIOS
    • Prozessor: EPXF, 7 Core freigeschaltet
    • Hauptspeicher: 512 GB
    • HDD: 6200 GB mit internen Flash-Drive - Type 9 x 700 GB - Modell 59C2
    • OS: V7R1 mit TR11

  10. #10
    Registriert seit
    Mar 2019
    Beiträge
    33

    Noch eine Anmerkung zur Funktion getTimestamp

    Diese Funktion ist eine SQL-Funktion und läuft auch (und grade da) bei dem Insert mit select von der fertig berechneten Tabelle. Ich weiß, dass ich dieses Statement am ende des CL's aufrufen kann um mir den Ärger zu ersparen.
    Aber ich möchte ja auch mal History-Tabellen schaffen, die an jeder relevanten Tabelle mit einem Trigger versorgt hängen.
    Wenn ein Insert-Trigger also schon solche Laufzeitverlängerungen verursacht funktioniert das nicht. Deshalb der Aufwand.

  11. #11
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Achtung: bei ON EACH STATEMENT must du die Daten, die gerade insertet wurden erst wieder lesen, was der Gesamtperformance eher abträglich ist, denn an deinem Insert ändert sich ja nichts.
    Das Lesen "from inserted" kommt da noch dazu.
    Zusätzlich muss die DB die temporäre "Inserted"-Tabelle schreiben. Also gleich doppelter Performanceverlust.

    ON EACH STATEMENT lohnt sich nur, wenn man nicht für jeden Satz eine Operation durchführen muss.

    Kodierst du einen einzelnen Insert, wird der Trigger 1x gerufen. Die Daten stehen in "Inserted" als Kopie und nicht in den Programmvariablen.
    Kodierst du einen "insert into .... select from..." wird der Trigger wieder 1x aufgerufen.
    Auch hier stehen alle Daten noch mal in "Inserted" zur Verfügung.
    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

  12. #12
    Registriert seit
    Mar 2002
    Beiträge
    5.286
    Zitat Zitat von Lucky662 Beitrag anzeigen
    Das Problem mit dem Datum habe ich auf dem System mit vielen Tabellen, wo ich auch keinen Quellcode für habe. Deshalb diese Funktion.
    Andreas Funke
    ...dieses Detailproblem lässt sich auch mit einer Umsetzungstabelle und Views lösen.
    Zentral wäre für mich allerdings zuerst die massive Stundendifferenz zu verstehen, bevor man versucht im nachgelagerten Bereich zu optimieren.

    D*B
    AS400 Freeware
    http://www.bender-dv.de
    Mit embedded SQL in RPG auf Datenbanken von ADABAS bis XBASE zugreifen
    http://sourceforge.net/projects/appserver4rpg/

Similar Threads

  1. SQLRPGLE und Fehlerbehandlung zur Laufzeit
    By linguin in forum NEWSboard Programmierung
    Antworten: 11
    Letzter Beitrag: 10-08-17, 13:51
  2. Eine Marke, eine Halle, eine Messe: IT & Business - Ende September in Stuttgart
    By Isabella Pridat-Zapp in forum Archiv NEWSboard Events
    Antworten: 0
    Letzter Beitrag: 10-09-15, 13:50
  3. SQL-Trigger an PF
    By Sebastian85 in forum NEWSboard Programmierung
    Antworten: 10
    Letzter Beitrag: 11-03-15, 08:26
  4. Laufzeit-Probleme nach Release-Wechsel
    By B.Hauser in forum IBM i Hauptforum
    Antworten: 3
    Letzter Beitrag: 08-02-02, 18:18
  5. Trigger / ILE RPG
    By Frank Pusch in forum IBM i Hauptforum
    Antworten: 3
    Letzter Beitrag: 17-05-01, 10:34

Tags for this Thread

Berechtigungen

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