Submits und SQL

[dholtmann]

Guten Tag zusammen,

kann mir jemand erklären warum ein Programm, welches quasi nur SQLs ausführt,
im Submit je Abfrage um ein Vielfaches schneller ist als per call?
Mit etwas Geschwindigkeitsbonus habe ich ja gerechnet, aber nicht mit so großem Unterschied pro Abfrage.
Hier ein paar Vergleichswerte:
OHNE Submit:
2016-07-06-09.31.59.584 BEGINN Anzahl der Auträge
2016-07-06-09.31.59.684 ENDE Anzahl der Aufträge
2016-07-06-09.31.59.686 BEGINN Letzer Auftrag
2016-07-06-09.32.00.022 ENDE Letzter Auftrag
2016-07-06-09.32.00.022 BEGINN Anzahl der Auträge
2016-07-06-09.32.00.025 ENDE Anzahl der Aufträge
2016-07-06-09.32.00.025 BEGINN Letzer Auftrag
2016-07-06-09.32.00.350 ENDE Letzter Auftrag
2016-07-06-09.32.00.350 BEGINN Anzahl der Auträge
2016-07-06-09.32.00.350 ENDE Anzahl der Aufträge
2016-07-06-09.32.00.351 BEGINN Letzer Auftrag
2016-07-06-09.32.00.677 ENDE Letzter Auftrag
2016-07-06-09.32.00.677 BEGINN Anzahl der Auträge
2016-07-06-09.32.00.677 ENDE Anzahl der Aufträge
2016-07-06-09.32.00.678 BEGINN Letzer Auftrag
2016-07-06-09.32.00.997 ENDE Letzter Auftrag

MIT Submit:
2016-07-06-09.22.30.427 BEGINN Anzahl der Auträge
2016-07-06-09.22.30.457 ENDE Anzahl der Aufträge
2016-07-06-09.22.30.459 BEGINN Letzer Auftrag
2016-07-06-09.22.30.474 ENDE Letzter Auftrag
2016-07-06-09.22.30.474 BEGINN Anzahl der Auträge
2016-07-06-09.22.30.477 ENDE Anzahl der Aufträge
2016-07-06-09.22.30.477 BEGINN Letzer Auftrag
2016-07-06-09.22.30.478 ENDE Letzter Auftrag
2016-07-06-09.22.30.478 BEGINN Anzahl der Auträge
2016-07-06-09.22.30.478 ENDE Anzahl der Aufträge
2016-07-06-09.22.30.478 BEGINN Letzer Auftrag
2016-07-06-09.22.30.478 ENDE Letzter Auftrag
2016-07-06-09.22.30.478 BEGINN Anzahl der Auträge
2016-07-06-09.22.30.479 ENDE Anzahl der Aufträge
2016-07-06-09.22.30.479 BEGINN Letzer Auftrag
2016-07-06-09.22.30.479 ENDE Letzter Auftrag
2016-07-06-09.22.30.479 BEGINN Anzahl der Auträge
2016-07-06-09.22.30.479 ENDE Anzahl der Aufträge

In Summe ergibt das wenige Sekunden für den Submit und eine volle Stunde ohne.

Zwar freut mich das Ganze, dennoch bleibe ich ratlos

[Pikachu]

„Morgens halb zehn in Deutschland“

OHNE Submit:
2016-07-06-09.31.59.584 BEGINN Anzahl der Auträge
2016-07-06-09.31.59.684 ENDE Anzahl der Aufträge
2016-07-06-09.31.59.686 BEGINN Letzer Auftrag
2016-07-06-09.32.00.022 ENDE Letzter Auftrag
2016-07-06-09.32.00.022 BEGINN Anzahl der Auträge
2016-07-06-09.32.00.025 ENDE Anzahl der Aufträge
2016-07-06-09.32.00.025 BEGINN Letzer Auftrag
2016-07-06-09.32.00.350 ENDE Letzter Auftrag
2016-07-06-09.32.00.350 BEGINN Anzahl der Auträge
2016-07-06-09.32.00.350 ENDE Anzahl der Aufträge
2016-07-06-09.32.00.351 BEGINN Letzer Auftrag
2016-07-06-09.32.00.677 ENDE Letzter Auftrag
2016-07-06-09.32.00.677 BEGINN Anzahl der Auträge
2016-07-06-09.32.00.677 ENDE Anzahl der Aufträge
2016-07-06-09.32.00.678 BEGINN Letzer Auftrag
2016-07-06-09.32.00.997 ENDE Letzter Auftrag

[KingofKning]

Meinst Du damit Batch und Interaktiv?

GG

[andreaspr@aon.at]

OHNE Submit:
2016-07-06-09.31.59.584 BEGINN Anzahl der Auträge
2016-07-06-09.31.59.684 ENDE Anzahl der Aufträge
2016-07-06-09.32.00.022 BEGINN Anzahl der Auträge
2016-07-06-09.32.00.025 ENDE Anzahl der Aufträge
2016-07-06-09.32.00.350 BEGINN Anzahl der Auträge
2016-07-06-09.32.00.350 ENDE Anzahl der Aufträge
2016-07-06-09.32.00.677 BEGINN Anzahl der Auträge
2016-07-06-09.32.00.677 ENDE Anzahl der Aufträge

Für mich ist es hier schwer etwas zu erkennen.
Es ist aber generell so dass der 1. Aufruf immer länger dauert (DB schreibt in den RAM/Cache usw.) und der gleiche Aufruf dann schneller ist.
Im Bereich der Millisekunden kann es dann dennoch manchmal zu größeren unterschieden kommen. Da ist es aber egal ob Bach oder Interaktiv.

Wenn ich raten soll, würde ich mal behaupten, dass du als 1. die SQLs mit CALL aufgerufen hast und danach im Batch?

lg Andreas

[BenderD]

... ohne das SQL Statement kann man da nur raten. Eine mögliche Ursache ist, dass im Batch eher for all records optimiert wird, interaktiv eher für den ersten Satz. Genaueres sieht man, wenn man den Job vor Aufruf des SQL unter Debug setzt, dann protokolliert der Optimizer, was er sich so denkt und was er da vorhat.

D*B

[Fuerchau]

Und was auch nicht zu verachten ist, falls es das noch gibt, die unterschiedlichen CPW's Batch/Interactiv.
Falls du uns die volle Wahrheit gepostet hast, dauert das im Dialog 1,4 Sekunden, im Batch 0,05 Sekunden.
Wo kommt deine Stunde her?
Dafür benötigst du schon ca. 2500 Aufrufe dieser Routine.
Was deine Zeitmessung noch außen vor lässt, wie lange dauert es vom SBMJOB bis der Job startet und dann die SQL's ausführt? Diese Zeit ist real noch hinzuzurechnen.
Und dann, wie Dieter schon anmerkte, was machen die SQL's genau.
Wieviele Ressourcen hast du aktuell offen (also ODP's), die SQL nach passendem durchsuchen muss?
Im Batch dauert das Suchen dann halt nicht so lange.

Wie sieht's mit der "Zeitscheibe" aus (Jobausführungsattribute)?
Im Dialog erfolgt der Wechsel zwischen den Job's häufiger als im Batch. Dadurch verlängert sich zusätzlich die Gesamt-Laufzeit.

Es gibt viele Stellschrauben um den Batch zu bevorzugen, da diese Jobs möglichst schnell beendet werden und die Ressourcen für den Dialog wieder frei werden.
Dafür gibts dann z.B. Workmanagement-Guides um hier noch das Letzte herauszuholen.

[dholtmann]

Es mag auch noch etwas länger gedauert haben.
Die Stunde war nur ein Schätzwert, jedoch werden die Einträge im Protokoll mit der Zeit in immer kürzeren Abschnitten geschrieben (ohne Batch).
Es gibt insgesamt 15038 Aufrufe.
START Zeitstempel: 2016-07-06-17.09.07.079
END Zeitstempel: 2016-07-06-17.09.12.591
Was dann diesmal sogar ~5 Sekunden waren.
Hier mein Programmcode:

exec sql declare Kundecursor cursor for                        
            select Kfirma, KKunde                                       
            from Kunde;                                                
exec sql open Kundecursor;                                     
dow sqlcode = 0;                                              
  exec sql fetch Kundecursor into :DS_Kunde;              
                                                              
  if zon_Kunde = 0;                                      
    iter;                                                     
  endif;   
                                                   
  Prottext = ds_timestamp + ' BEGINN Anzahl der Auträge';     
  exec sql insert into ECPPROT (PROTOKOLL) VALUES(:Prottext); 
                                                              
  exec sql declare counter cursor for   
    select count(*) from (                      
       select Count(*)                           
       from auftrag                              
       where AKunde = :zon_Kunde and        
                AFirma = :zon_firma
       group by Aauftragsnummer)                          
as Ergebnis;                               
exec sql open counter;                     
exec sql fetch counter into :zon_Anzahl;     
exec sql close counter;          
                                                                
ds_timestamp = %char(%timestamp());                             
Prottext = ds_timestamp + ' ENDE Anzahl der Aufträge';          
exec sql insert into ECPPROT (PROTOKOLL) VALUES(:Prottext);     
                                                                
ds_timestamp = %char(%timestamp());                             
Prottext = ds_timestamp + ' BEGINN Letzer Auftrag';             
exec sql insert into ECPPROT (PROTOKOLL) VALUES(:Prottext);     
                                                                
exec sql declare Auftrag_cursor cursor for
            select EKunde, AKundenname, EFirma, EAuftragsnummer,   
                 EJahr, EMonat, ETAG                            
          from Eintrag                                             
          join (select AKundenname, AFirma, AAuftragsnummer      
          from Auftrag)                                             
          as Auft                                                  
          on EFirma = AFirma and                                   
              EAuftragsnummer = AAuftragsnummer                                                                 where EKunde = :zon_kunde                           

          group by EKunde, AKundenname,                                 
                       EFirma, EAuftragsnummer, EJahr, EMonat, ETag
          having sum(EMenge) > 0 and max(digits(EJahr) concat     
                                                          digits(EMonat) concat                       
                                                          digits(ETag)) =                           
             digits(EJahr) concat                       
             digits(EMonat) concat                       
             digits(ETag);                            
exec sql open Auftrag_cursor;                                     
exec sql fetch Auftrag_cursor into :ds_auftrag;                   
exec sql close Auftrag_cursor;                                    
                                                               
ds_timestamp = %char(%timestamp());                            
Prottext = ds_timestamp + ' ENDE Letzter Auftrag';             
exec sql insert into ECPPROT (PROTOKOLL) VALUES(:Prottext);    
                                                               
if ds_auftrag.zon_auftragsnummer <> 0;                                   
  exec sql insert into KundenSumme (HFirma, HKundennnummer, HKundenname,   
                               HAuftragsnummer, HAnzahl)    
                       values(:ds_auftrag.zon_firma,       
                                 :ds_auftrag.zon_Kundennummer,       
                                 :ds_auftrag.zon_Kundenname,       
                                 :ds_auftrag.zon_Auftragsnummer,       
                                 :zon_anzahl);                 
endif;                                                    
clear ds_auftrag;      
  clear zon_anzahl;              
  clear ds_kunde;         
enddo;

Es müssten in dem Submit nur die hier angesprochenen Dateien offen sein,
und manuell auch nicht viel mehr.

Ich hoffe ihr könnt damit mehr anfangen,
und vielen Dank schon einmal!

[Fuerchau]

Da musst du, wie Dieter schon sagte, das Programm per Debugmode (also nur STRDBG) laufen lassen, dann bekommst du Hinweise, wo die Performance verloren geht.
Da du aber nicht weißt, was sonst so auf der Maschine läuft, sind Zeiten zwischen 1-5 Sekunden durchaus normal.
Du kannst nur etwas abkürzen, wenn du nur 1 Datensatz benötigst. In diesem Fall wird ggf. anders optimiert.
Bei einem Datensatz kannst du direkt per
select f1, f2, ... into : F1, : F2, ...
from ....
verwenden.
Bist du dir nicht sicher, ob es wirklich nur 1 Satz gibt, dann ergänze mit "fetch first 1 rows only".
Ebenso ist ggf. ein "optimize for 1 row" ebenso möglich.

Jetzt jeden einzelnen SQL zu analysieren ist bei den Laufzeiten ggf. suboptimal.
Beispiel doppeltes Aggregat:
select count(*) into : MyCount
from (select distinct Auftrag from ...) x

Ein Problem sind die Vergleiche der zusammengesetzten Schlüssel die keinen Index verwenden.
Hier hilft ggf. ein "calculated Index", der aus deinen 3 Feldern wieder 1 macht.
Ansonsten vermisse ich im Auftrag_Cursor die Where-Einschränkung auf den Kunden.

Für die Verwendung von Einzelschlüsseln hilft manchmal ein Zusammenbau mit Zerlegung:

select * from (select key, f1 concat f2 concat f3 as key2 from taba ....) a
join tabb y on a.key=b.key and substr(x.key2, 1, n) = b.f1 and ...

Hier sind auch Typecasts wieder auf Decimal/Numeric ebenso wieder möglich. SQL ist in der Lage, dann wieder einen Index zu verwenden.

[dholtmann]

Vielen Dank für die Ratschläge,
ich werde das so mal umsetzen, die Funktionen kannte ich zum Großteil noch nicht.
"Ansonsten vermisse ich im Auftrag_Cursor die Where-Einschränkung auf den Kunden."
Die where Einschränkung ist da, wurde in meinem Post nur äußerst unglücklich formatiert.
(man muss ganz nach rechts scrollen).