SQL Ersatz für SETGT/READE Kombi

[mwithake]

Hallo,

ich bin auf der Suche nach einem performanten SQL Ersatz für die RPG SETGT/READPE Kombination zur Ermittlung von Sätze mit einem "gültig ab" Datum.

Die Situation kennen sicherlich einige: Es gibt ein Tabelle mit Sätze die ein "gültg ab" Datum haben. Um für ein Datum nun den richtigen Satz zu finden, bediene ich mich der SETGT und READPE Operationen:

setgt (yKey:yDatum) tabelle;
readpe (yKey) tabelle;      
if not %eof();                             
  return yWert;                       
endif;

Ich suche nun eine performante Lösung dafür in SQL.

Um das gleiche Ergebnis für die Problematik zu bekommen, fallen mir zwei Varianten ein um den Wert zu ermitteln:

Variante 1:

SELECT wert                  
FROM   Tabelle
WHERE  key=:yKey                  
       AND gueltig_ab<=:yDatum        
ORDER BY gueltig_ab DESC              
FETCH FIRST ROW ONLY

Variante 2:

SELECT wert                              
FROM   tabelle                 
WHERE  key=:yKey                            
       AND gueltig_ab=                           
          (SELECT MAX(gueltig_ab)                
           FROM   tabelle      
           WHERE  key=:yKey                  
                  AND gueltig_ab<=:yDatum        
          );

Ich habe ein kleine Testfall aufgebaut, der die Routinen unter gleichen Bedingungen mehrere 1000x durchführt. Dabei kommen folgende Zeiten raus:

SETGT/READPE: 1,788
SQL Variante 1: 13,381
SQL Variante 2: 12,445

Die SQL-Variante mit dem Subselect ist etwas schneller als die Variante mit ORDER BY. Beide sind aber deutlich langsamer als die SETGT/READPE-Variante.

Hat jemand noch eine Idee, wie mit SQL diese Problematik besser gelöst werden kann?

Gruß
Martin

[Fuerchau]

Deine SQL's sind ja soweit OK, aber du benötigst halt einen absteigenden Index.
Dann wird SQL auch wieder schneller.

[BenderD]

... ein zusätzliches Feld gültig_bis (kann auch per Trigger gefüllt werden) hilft da Wunder...

D*B

[mwithake]

@Fuerchau Ein absteigender Index hat keine Änderungen gebracht.
@BenderD Was muss mit dem Feld gemacht werden?

[Fuerchau]

Das Problem ist ja, dass ein Gültig-Ab immer für alle vergangenen Daten gilt.
SQL baut also immer ein Resultset mit mehreren Sätzen zusammen obwohl nur einer benötigt wird.
Mittels "Order by .. desc fetch first 1 rows only" solltest du hier einschränken.

Die bessere Methode ist tatsächlich, ein "Gültig-Bis"-Feld einzuführen.
D.h., hier kann es rein technisch nur 1 Satz geben wenn das Feld auch Unique-Key ist.
Es kann ja eigentlich nicht mehrere Gültig-Bis geben die am selben Tag enden.

[mwithake]

Fetch first row only ist ja schon angegeben.

Die Keys sind unique, auch bei gültig ab. Es gibt jeweils nur einen Satz mit gültig ab. Auch ist die Tabelle, mit der ich die Zeiten teste, sehr klein. Trotzdem sind die großen Zeitunterscheide.

[Fuerchau]

Wobei ich mich frage, welche Zeiten du hier hast.
Bei SETGT/REDPE fast 2 Sekunden?
Dann hast du da ganz andere Probleme, das sollte auch eher 0,001 Sekunden dauern.
Wenn das 2 Millisekunden sind, dann würde ich mich um die 13 Millisekunden auch nicht sorgen.
Ggf. ist das nämlich nur der 1. Zugriff mit SQL-Analyse und Datei-Open.
Im Wiederholungsfall (ODP bleibt) wird SQL dann auch schneller.
Mach das mal ein paar 100 oder 1000 Mal.
SQL hat natürlich immer einen Overhead und ist beim Einzelsatz lesen langsamer.
Beim Einzelsatz brauch man aber auch keinen Cursor, hier kann man direkt einen "select ... into ... from ..." verwenden und spart somit die Open/Fetch/Close. Das bringt auch schon mal was.

[Fuerchau]

Nachtrag:
Prüfe mal im Debug-Modus ob dein Desc-Index überhaupt verwendet wird.

[BenderD]

@Fuerchau Ein absteigender Index hat keine Änderungen gebracht.
@BenderD Was muss mit dem Feld gemacht werden?

... dann holt man sich die Sätze mit:
SELECT wert
FROM Tabelle
WHERE key=:yKey
AND :yDatum between gilt_ab and gilt_bis
das sollte schneller sein und macht vor allem das Leben einfacher, da sich das im View Layer abbilden lässt.
Beim neuesten schreibt man dann in gilt_bis Highval rein.
D*B

[mwithake]

Das muss wohl am SQL-Overhead liegen.

@Fuerchau: Die Werte sind schon für 100.000 Aufrufe (wie in der ersten Frage angegeben). Das "Into" hatte ich zur Übersicht rausgenommen, die Aufrufe fanden mit SELECT INTO statt. Visual Explain zeigt auch an, das der absteigende Index nicht genommen wird.

@Bender: Die Abfrage mit gültig_bis und BETWEEN dauerte (ohne Index Optimierung und View) etwas länger als die beiden anderen. Vielleicht sind die Anzahl der Sätze, die derzeit in der Tabelle vorhanden sind, auch zu gering um einen Unterschied festzustellen.

Ich hatte die Frage einfach mal in dem Raum gestellt, da ich immer wieder auf diese Art der Abfrage stoße. Vielleicht hätte es ja einen eleganteren SQL Weg gegeben, dieses Problem zu lösen (RPG hat ja auch Operationen für diesen Art des Zugriffs).
Besonders in SQL-Abfrage, die einen Wert aus der "Gültig_AB" Tabelle dazu holen müssen, wäre es interessant. Die Abfrage ist dann sehr kompliziert, Select mit Unterselect auf "Gültig_AB" Tabelle und darauf wieder Unterselect für die MAX() Funktion. Mehrere 1000 Sätze kommen dann schnell zustande, welche die Unterabfrage dann wieder auslösen und den Geschwindigkeitsunterschied bemerkbar machen.

Sicherlich kann man die Funktion auch mit Stored-Procedure implementieren (mit allen möglichen Optimierung, Deterministic etc). Aber hier wäre der Optimizer wieder ein bisschen außen vor, falls es doch mal diese Art der Abfragen optimieren könnte. Und um die Abfrage in der Procedure schnell zu bekommen müssten man wieder auf RPG zurückgreifen, was wir in unsere Java-Umgebung nicht mehr so gerne möchten.

[B.Hauser]

Für Einzelsatz-Zugriffe ist SQL im Vergleich zu RPG um einiges langsamer, das ist bekannt. Der Faktor 1:10 erscheint mir allerdings etwas hoch.

Nur so ein paar Fragen am Rande.
Befinden sich die aufrufende Prozeduren und die rufenden Prozeduren im gleichen Modul?
Befinden sich die aufgerufenen Prozeduren in einem Service Programm oder sind die Module gebunden?
In welcher Aktivierungsgruppe werden die Caller und aufgerufenen Prozeduren ausgeführt?
Wie wird die Option CLOSQLCSR im Umwandlungsbefehl (oder SET OPTION Statement) der (embedded) SQL Module gesetzt? *ENDMOD oder *ENDACT?

Birgitta

[mwithake]

Die Prozedur befindet sich im einem Serviceprogramm, das mit ACTGRP(*CALLER) umgewandelt wird. Der Parameter CLOSQLCSR im CRTSQLRPGI, mit das Serviceprogramm erstellt wird, steht auf *ENDACTGRP, SET OPTION wird nicht verwendet.
Das aufrufende Programm hat die Aktivierungsgruppeneinstellung *NEW.