[NEWSboard IBMi Forum]
Seite 1 von 2 1 2 Letzte
  1. #1
    Registriert seit
    Nov 2018
    Beiträge
    35

    insert mit values macht immer table scan

    Hallo!

    Ich habe ein Problem mit SQL. Embedded in RPQ oder im Acces Client ausgeführt ist egal.

    Ich habe eine simple Tabelle mit ca 10 Feldern. Hauptsächlich Integer und ein varchar.
    Die Tabelle hat einen Primary key (integer) und ca 12000 Einträge.

    Wenn ich ein Visual Explain von einem insert mache, das etwa so aussieht:
    insert into tbzb (id, f1, f2, f3,...) values (....) macht das Statement immer einen Table-Scan und liest alle 12000 Einträge.

    Als Begründung steht im Explain, dass er keinen Index findet. Ich habe aber schon alle möglichen Indices in alken möglichen Varianten probiert. Advisen tut er auch nix.

    Ich komm hier irgendwie nicht weiter. Das Insert dauer jetzt schon bei der ersten Ausführung in einem Job ca 300ms.

    Wie kann ich den Table-Scan vermeiden und nicht alle Records lesen?

    BG

  2. #2
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Also ein Insert macht keinen Tablescan.
    Aber was gibst du bei den Values an?
    Hast du da ggf. einen Subselect oder einen Funktionsaufruf dabei?
    Poste mal den gannzen SQL.
    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

  3. #3
    Registriert seit
    Nov 2018
    Beiträge
    35
    Hier das Insert und darunter die Tabelle. Ich habe Screenshots angehängt mit dem Visual Explain

    In Diesem Fall hat die Tabelle gut 2000 Einträge


    INSERT INTO testlib3.tbzt(ztdatum) values('2028-12-22');

    CREATE TABLE TESTLIB3.TBZT (
    ZTDATUM DATE NOT NULL ,
    ZTJAHR INTEGER DEFAULT NULL ,
    ZTMONAT INTEGER DEFAULT NULL ,
    ZTTAG INTEGER DEFAULT NULL ,
    ZTWOCHENTAG FOR COLUMN ZTWT INTEGER DEFAULT NULL ,
    ZTWOCHE INTEGER DEFAULT NULL ,
    ZTTAGJAHR INTEGER DEFAULT NULL ,
    ZTQUARTAL INTEGER DEFAULT NULL ,
    ZTPERIODEWOCHE FOR COLUMN ZTPERW VARCHAR(8) CCSID 1141 DEFAULT NULL ,
    ZTPERIODE VARCHAR(7) CCSID 1141 DEFAULT NULL ,
    ZTGEJAHR INTEGER DEFAULT NULL ,
    ZTGEQUARTAL FOR COLUMN ZTGEQUAR INTEGER DEFAULT NULL ,
    ZTGEQUARTALVOLL FOR COLUMN ZTGEQUARV VARCHAR(8) CCSID 1141 DEFAULT NULL ,
    CONSTRAINT TESTLIB3.PK_ZT PRIMARY KEY( ZTDATUM ) )

    RCDFMT TBZT ;
    Miniaturansichten angehängter Grafiken Miniaturansichten angehängter Grafiken Click image for larger version. 

Name:	screen1.png 
Views:	20 
Size:	60,0 KB 
ID:	499  

    Click image for larger version. 

Name:	screen2.png 
Views:	15 
Size:	98,0 KB 
ID:	500  

    Click image for larger version. 

Name:	screen3.png 
Views:	10 
Size:	81,7 KB 
ID:	501  


  4. #4
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Nun, da nehme ich mal an, dass der Visual Explain ja von einem Select/Update ausgeht und keine Where-Klausel findet, von einem Tablescan ausgehen muss.

    Ein Insert schreibt aber einfach in die Tabelle und prüft allenfalls noch Constraints, wie in deinem Fall den Primary key.
    Wie kommst du nun auf die Insert-Zeit von 300ms?

    Du musst da folgendes beachten:
    Ein Insert muss implizit noch ein Open machen.
    Beim 1. Insert folgt ebenso auch ein Close.
    Erst ab der 2. Wiederholung wird der ODP (Open Data Path) nicht mehr geschlossen, so dass alle folgenen Inserts erheblich schneller sind.

    Prüfe also mal, ob dein Insert-Test nicht noch von wesentlichen anderen Faktoren abhängt:
    Ggf. FileHandler-Programm mit ACTGRP(*NEW) statt ACTGRP(*CALLER)?

    Die reine Insertzeit dürfte kaum 1 ms übersteigen.
    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
    Nov 2018
    Beiträge
    35
    Genau das ist der Effekt den ich habe un der mich etwas verunsichert/stört.

    Das erste insert in einem Job dauer immer so 300ms, alle weiteren Inserts gehen dann extrem schnell.
    Das mit dem open, Close und dem ODP hab ich schon so reproduziert (DM Monitor)

    Die FileHandler Thematik kann ich ausschliessen.
    Ich habe beim Programm ACTGRP(*NEW). Aber wenn ich nach dem ersten insert aus dem PGM rausgehe und dann wieder rein,dann ist das insert schnell. Es ist immer nur das erste insert nach dem Starten des Terminal-Jobs.

    Nun kommen mit 300ms auf einem System auf dem zum Testzeitpunk nur ich angemeldet bin schon extrem lange vor. Ist das normal bzw. muss man mit dem einfach rechnen, beim ersten insert?
    Wäre nativio (chain, write) da wesentlich schneller?

  6. #6
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Nein, das ist da auch nicht (wesentlich) schneller.
    Der Open/Close muss ja immer gemacht werden.
    Des weiteren ruft SQL eben immer den Optimizer auf um einen Zugriffspfad des impliziten Selects "values(...)" zu ermitteln.
    Und das kann schon mal dauern. Zumal dann, wenn der Insert nicht mit Hostvariablen sondern als dynamischer SQL mit eingebetten Werten arbeitet.
    In diesem Fall ist jedes mal ein Syntaxcheck erforderlich.
    Dabei werden sämtliche eingebetteten Inhalt von SQL in temporäre Hostvariablen ausgelagert und mit "?" ersetzt. Danach erfolgt die Analyse, wobei nun die Erkennung zuschlagen kann, dass dieser SQL schon analysiert ist.
    Deshalb sollten auch bei dynamischen SQL Hostvariablen oder eine SQLDA verwendet werden um performant zu arbeiten.

    Zu sehen ist das wie beim Autofahren. Bis das Auto mal auf 100 ist, dauert es halt je nach Leistung unterschiedlich lange. Und wenn dann ein Stau kommt, hast du das immer wieder.
    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

  7. #7
    Registriert seit
    Nov 2003
    Beiträge
    2.304
    Tritt das Problem auch bei Feldern mit anderen Datentypen auf?

  8. #8
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Seit wann ist Open/Close inhaltsabhängig;-)?
    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
    Nov 2018
    Beiträge
    35
    Ich habe das gestern und heute mit dem IBM Support diskutiert mit folgender Conclusio.

    Beim Insert wird tatsächlich sowas ähnliches wie ein Tables-Scan gemacht. Das Visual-Explain zeigt dann halt Table-Scan an.

    Die 300ms sind nicht extrem ungewöhnlich, beim ersten Ausführen. Das Problem ist dem IBM Support schon öfter mal untergekommen und wurde auch zutodeanalysiert.

    Mit dem Outcome, dass es vorallem auf Systemen auftritt, die sich den ganzen Tag langweilen.

    Wir haben sehr viel debuggt und auffällig ist schon, dass das insert mit strsql wesentlich schneller geht als über embedded sql (2x). Wirklichen Grund haben wir dafür aber nicht gefunden. Mein Sourcecode ist supersimpel.

    Was interessant is ist, dass das Einfügen in die selbe Tabelle mit Native - IO (RPG Write) beim ersten Mal immer unter 20ms dauert, also über 10x mal so schnell ist. Meistens sogar unter 10ms, also 30x so schnell.

    Deckt sich das mit euren Erfahrungen?

  10. #10
    Registriert seit
    Aug 2003
    Beiträge
    1.508
    Könnte durchaus sein, dass mit Native IO es etwas schneller geht.
    Das mit dem Testen ist jedoch so eine Sache ...
    Hast du den Native IO Test direkt nach dem SQL Test gemacht?
    Wenn die Tabelle einmal im RAM ist geht's grundsätzlich schon mal schneller und das ist auch das Problem bei Systemen die sich Langweilen, im Gegensatz zu System die aktiv sind.
    Dort sind Tabellen bzw. Objekte die öfters verwendet werden auch im RAM und dadurch schneller zugreifbar.
    Dann wäre auch noch die Frage wie die Zeitmessung bei Native IO ausgesehen hat? Vor dem Programm start oder im Programm? Wenn im Programm, hast du mit USROPN gearbeitet und den Startzeitpunkt vor dem OPEN gesetzt usw.

    lg Andreas

  11. #11
    Registriert seit
    Nov 2018
    Beiträge
    35
    Die Tests werden immer mit einem neuen Job ausgeführt. Ich melde mich im Terminal ab, schliesse den Terminal-Client und mache eine neue Session auf.

    Bei SQL reicht das damit das erste insert wieder langsam ist.

    Ich habe den rpg-code für die Native-Variante unten angehängt (Davor findest Du die Ausgabe). In dem Beispiel ist die Tabelle eine andere als oben in meinem Post, der Effekt ist aber genau der gleiche. Bei dieser Tabelle im Code unten dauer das insert mit embedded sql beim ersten mal immer ca 240ms. Wie man sehen kann brauch der RPG-Code ca 22ms.

    LG,
    Franz




    ----------------------------------------------

    DSPLY Start
    DSPLY 2018-12-20-17.34.21.269
    DSPLY 2018-12-20-17.34.21.291
    DSPLY End

    --------------------------------------------

    **free
    ctl-opt option(*DEBUGIO: *SRCSTMT) ccsid(*char:*jobrun) actgrp(*new) datfmt(*iso);


    dcl-f xxzb extfile(*extdesc) extdesc('TESTLIB3/TBZB') USAGE(*INPUT: *OUTPUT);


    dcl-s ts char(30) dim(10);

    dcl-s inp01 char(1);

    dsply 'Start' '*EXT' inp01;

    ts(1) = %char(%timestamp(*SYS : 3));

    zbid = 100002;
    zbdatum = %date();
    pvid =1;
    maid = 1;
    zbstatus = 0;


    write tbzb;
    ts(2) = %char(%timestamp(*SYS : 3));

    dsply ts(1);
    dsply ts(2);

    dsply 'End' '*EXT' inp01;
    return;

  12. #12
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Das ist mir schon klar.
    Aber:
    Hast du dir mal die Mühe gemacht einen TRCJOB durchzuführen?
    Da kannst du sehr schön verfolgen, was da so alles aufgerufen wird.

    Bei RLA werden direkt die "hart verdrahteten" CALL-Aufrufe der QDB-Routinen, QDBGET, QDBPUT, verwendet.
    Bei SQL startest du mit einem QSQxxx-Aufruf (Spool-Liste), der noch verschiedene weitere Routinen durchläuft bevor er endlich QDBPUT erreicht.
    Vergleiche diese Aufrufe mit dem 1. SQL-Insert und dem 2. SQL-Insert.
    Dann weißt du, wo die Zeiten verbraten werden.

    Im Vergleich zu RLA wird SQL nie vergleichbare Geschwindigkeiten erreichen können, da hier ein gigantischer Overhead betrieben werden muss.
    Zumal hier noch interne variabel definierte Felder gegenüber statischen Feldern in RPG gegenüber stehen. Alleine schon die Neudefinitionen der Hostvariablen ist eigentlich unnötig.
    Allerdings wird sich wohl kaum jemals noch jemand hinsetzen und einen komplexen SQL mit Joins, derived Tables, rekursive CTE's sowie Gruppierung, Filter, Aggregierung in RLA umzusetzen.
    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. Insert mit SQL Prozedur liefert Resultset mittels Final Table
    By Gutmann in forum NEWSboard Programmierung
    Antworten: 11
    Letzter Beitrag: 06-09-17, 08:55
  2. RPG SQL Insert VALUES(Datenstruktur)
    By ASFOURI in forum NEWSboard Programmierung
    Antworten: 7
    Letzter Beitrag: 02-11-16, 12:34
  3. VALUES Check auf Inputfeld ignoriert
    By camouflage in forum NEWSboard Programmierung
    Antworten: 4
    Letzter Beitrag: 18-08-15, 15:10
  4. %SCAN im CL Programm
    By Etherion in forum NEWSboard Programmierung
    Antworten: 10
    Letzter Beitrag: 06-11-13, 19:24
  5. SCAN bei ILE RPG ???
    By HoScHiE in forum IBM i Hauptforum
    Antworten: 3
    Letzter Beitrag: 06-09-01, 17:37

Berechtigungen

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