SQL Techniken in Verbindung mit Subfile

[harkne]

Mein Problem war ja eigentlich dass ich beim Positionieren mit >= gearbeitet habe und dann der Scroll Cursor vorher gar nichts mehr hat zum lesen. Aber ich habe mich jetzt auch davon verabschiedet und setze einfach das SQL ab und versuche dann mit einem eurer Vorschläge an die entsprechende Stelle zu gelangen, wobei mir LIMIT und OFFSET noch fremd sind, da muss ich mal lesen und den Satz von BenderD muss ich auch erst mal Wort für Wort aufdröseln. Das mit dem Zurückblättern wollte ich genauso machen wie Fuerchau das beschreibt nur dann muss ich das komplette SQL absetzen ohne >= und beim Positionieren von einem Wert halt solange lesen bis ich dort bin. Ob ich das mit dem ScrollAble Cursor mache, weiß ich noch nicht, da habe ich wie bereits von Fuerchau erwähnt schlechte Erfahrung bei großer Datenmenge gemacht. Kann sein dass ich die Aktualisierung "händig" mittels CHAIN mache. Wobei ich dann halt auf die gelöschten Sätze und der Anzahl zurücklesender Sätze das selbst steuern muss. Ich schau mir jetzt mal alles an, was ihr geschrieben habt. Vielen Dank für Eure Zeit und Mühe.

Viele Grüße Harald

[harkne]

Ich hab das verwechselt. SCROLLABLE mache ich ja. Aber werden da die Daten tatsächlich aktualisiert? Brauche ich da nicht ein SENSITIVE oder so?

[BenderD]

und den Satz von BenderD muss ich auch erst mal Wort für Wort aufdröseln.

... out of my head:

PrepareStringUp = 'select * from myfile where Feld1 >= ? and Feld2 >= ? order by Feld3;
PrepareStringDown = 'select * from myfile where Feld1 < ? and Feld2 < ? order by Feld3 descending;

exec sql declare CUp cursor for SUp;
exec sql declare CDown cursor for SDown;

exec sql prepare SUp from PrepareStringUp;
exec sql prepare SDown from PrepareStringDown;

open CUp using :feld1, :feld2;

fetch Cup for 15 rows into :fetchrecDS;

close CUP;

analog rückwärts mit CDown.

Bei den zwei Vergleichsfeldern muss man aufpassen, ob die einfache Kopplung mit and, das ist, was man haben will oder ob das etwas komplizierter wird.

[harkne]

@BenderD
So war auch ein gedanklicher Ansatzweg von mir, bei der Umsetzung hat es dann etwas gehapert, aber das hier sieht gut aus. Das gleich mit 2 Cursor zu machen ist natürlich auch gut. 2 Resultsets. Beim rückwärtsblättern im 1 Resultset und keine Daten mehr mit dem 2. Resultset weiter machen und umgekehrt.

Danke für die Hilfe. Tja wer die Wahl hat hat die Qual.

Ich hab mich jetzt erst mal für die andere Variante mit nur einem SQL und dann halt von Anfang an entschieden. Aber das kann je nach Aufwand sich noch ändern :-)

[Fuerchau]

Stimmt, Sensitive ist für Aktualisierungen. Allerdings macht Scrollable ohne Sensitive wegen der Performance keinen Sinn, da sonst immer erst eine Kopie erstellt werden muss.

Was die Paginierung angeht, solltest du dich nicht um den Filter (Where) kümmern sondern nur um den Order By. Der Filter ist ja Users Wunsch und schränkt die Daten ein.
Einzig die dynamische Sortierung ist dein Problem bei der Subfileanzeige.

Neben Limit/Offset gibts noch eine Alternative in der Kombination mit D*B's Ansatz (wird auch öfters von Birgitta vorgeschlagen:

select * from (
select .....
,count(*) over () as Anzahl
,row_number() over (order by ....) Zeile
where UserFilter
) x
where Zeile between ? and ?
order by Zeile
fetch first n records

Die sog. Olap-Funktionen sind explizit für schnelle Ausführung entwickelt (einen Index über die Sortfelder sollte man trotzdem haben).
Du nummerierst die Zeilen entsprechend der gewünschten Sortierung.
Anschließend filterst du die gewünschten Zeile über deine Seitenlänge.
Der Sort nach Zeilennummer bringt dir die gewünschte Reihenfolge, da die Sortierung ja bereits erfolgt ist.

Vorteil: Du kannst auch nach Spalten sortieren, die nicht im Ergebnis leigen müssen.
Da die IBM i durchaus mehrere 10.000 Zeilen/Sekunde lesen kann, sollte die Performance kein Problem sein, und wer hat schon Lust, mehrere 100 Seiten durchzublättern.

Das Verfahren ist i.Ü. auch Sensitive, da ja ständig neu abgefragt wird.

[harkne]

@FUERCHAU
das mit OVER kannte ich nicht auch nicht das mit row_number. Hab mir jetzt ein bischen was durchgelesen im Internet. Wenn ich das aber richtig verstehe wird immer wieder das SQL ausgeführt. Immer und immer wieder bei jedem blättern. Würde das der Sensitive auch machen oder aktualisiert er nur selbst an der Stelle an der er es benötigt?

Für was ist der COUNT(*) in der Abfrage?

Für fetch first n records bräuchte ich aber dann eine DS mit Mehrfachvorkommen um gleich alle Sätze aufzunehmen, würde auch ein einfacher FETCH NEXT in einer Schleife gehen oder geht das dann auf die Performance?

[BenderD]

... alles overkill. Der Blockfetch dürfte das schnellste sein! ein blockfetch von 1000 Sätzen dauert genauso lange wie ein fetch. Das ganze Gedöns mit Olap und fetch first 15 only beim Cursor braucht es alles nicht, beim reopen wird positioniert und gut ist.

D*B

[Fuerchau]

Wie immer gibts ja viele Möglichkeiten.
Der Count(*) über alles gibt dir bereits mit dem 1. Satz die Anzahl Gesamtzeilen. Damit kannst du dem User schon mal ausrechnen, wieviele Seiten es denn werden;-).

Und wie Dieter schon sagt:
Den Prepare machst du immer nur dann, wenn sich die Sortierung oder der Filter ändert.
Du hast aber Recht, dass der SQL bei jedem Open neu ausgeführt wird.
Allerdings merkt sich die IBM i den gewählten Weg so dass ab dem 2. Open es etwas schneller geht.

Was die Gesamtperformance angeht, so ist das seitenweise Lesen einer Subfile immer schlecht.
Einfacher ist es, die Subfile beim vorwärtsblättern die nächsten Zeilen zu füllen. Das rückwärtsblättern erledigt die Subfile selber.
Bei Änderung der Sortierung wird neu prepared und die Subfile wieder von Anfang gefüllt.

Wenn man Spaß hat, kann man sich den Schlüssel zur aktuellen Subfilesatz (1. Zeile der Subfileseite, steht in der INFDS), ja merken und solange füllen, bis der positionierte Schlüssel wieder erreicht wird.

Die User werden dann schon merken, dass ständiges Umsortieren u.U. schlecht für die Suche ist.
Ich bin da auch eher für eine sinnvolle Filterung, so dass der Zwang zum Blättern reduziert wird.

[harkne]

Eine Frage noch zu FETCH und LIMIT

Wenn ich eine SQL ausführe und anschließend einen FETCH mache ist es doch so dass er erst das komplette Recordset zusammen stellt und ich mich mit dem FETCH nur innerhalb diesem bewege?

Verhält sich der LIMIT genauso, da er ja bereits im SQL mit dabei ist. Wenn ich also jetzt einen ORDER BY auf ein Feld mache für dass es einen Index gibt dann sollte der LIMIT doch schneller gehen oder?

@Baldur, das mit vorwärts füllen hatte ich die ganze Zeit was machst Du aber wenn er rückwärts blättert und in der zwischenzeit wurde ein Satz gelöscht? Da bekomme ich keine Steuerung so wäre er einfach weg. Damit könnte ich sogar noch leben, ich muss ja eh wenn eine Auswahl eingegeben wurde prüfen ob der Satz noch da ist. Aber wenn er selbst Auswahl 4 zum Löschen eingibt sollte der zumindest in der Übersicht weg sein.
Auch habe ich nachwievor die Begrenzung auf 10000 Records in der Subfile. Ich weiß wer so blöd ist so lange blättert der hat es auch verdient nicht alles angezeigt zu bekommen :-) Aber es ist eine Einschränkung.

Meine Überlegung war jetzt vielleicht doch es so zu machen dass ich am Anfang ganz normal die ersten 15 Sätze einlese. Also mit SELECt * FROM Tabelle ORDER BY Key1 LIMIT15.
Die Schlüsselwerte des letzten Satzes habe ich ja.
Blätter er jetzt nach vorn SELECT * FROM Tabelle WHERE Key 1 > letzer Wert in der Subfile ORDER BY Key 1 LIMIT 15
Umgekehrt blättert er rückwärte
SELECT * FROM Tabelle WHERE KEY1 < erster Wert in der Subfile ORDER BY KEY1 DESCEND LIMIT15
Wenn der Benutzer auf den Key positionieren will wäre das dann auch einfach.
Natürlich muss ich die Schlüssel anpassen je nach dem nach was der Benutzer sortieren möchte.

Aber wäre das SQL hier nicht am schnellsten? Ich meine 15 Sätze auf eine geschlüsselte Datei? Vorausgesetzt er baut nicht erst wieder das komplette Recordset zusammen.

Die aktuellen Dateiwerte hätte ich dann auch immer.
Vielleicht muss ich die ganzen Varianten mal probieren.

Ich spreche aktuell von einer Datei mit 2,5 Mio Sätze. Da am Anfang keine Vorselektion erfolgt gibt es für den Start kein WHERE. Ab dann natürlich schon wenn der Benutzer was im Subfile Header eingibt.

In meinem Fall spreche ich auch nur von einer Datei ohne JOINS zu anderen Dateien.

Für die Sortiermöglichkeiten lege ich logische Dateien an.

[BenderD]

... man kann das auch mit überschaubarem Aufwand völlig dynamisieren, sprich: Sortierung und Filter vom Benutzer beliebig ermöglichen. Dann wird halt das SQL Statement im Programm zusammengebastelt, prepared und ausgeführt. Bei dieser Variante gibt es auch kein vorwärts/rückwärts/Satz gelöscht Problem.
Wer mal eine SQL Schulung bei mir gemacht hat, erinnert sich vielleicht an das Übungsprojekt in den Aufgaben.
Bei ordentlicher Modularisierung kann man da wesentliche Teile noch rauszentralisieren.
Wenn für die Sortierungen Indexe da sind, brummt das auch ordentlich.

D*B

PS: Wer mal eine RPG Schulung bei mir gemacht hat, erinnert sich vielleicht noch an das Übungsprojekt, dies in vorhandene Subfile Programme nachträglich einzubauen.

[Fuerchau]

Solange von der Tabelle keine temporäre Kopie gemacht werden muss lädt der Cursur nur die Daten, die du abrufst. Die Gründe können vielfältig sein. Du kannst das erkennen, wenn dir im Job "*TEMPxxx"-Tabellen als geöffnete Dateien angezeigt werden.
Limt begrenzt das Ergebnis, so dass ein Fetch dann endet, der SQL muss mit Open neu ausgeführt werden.
Fetch ruft die Daten aus den geöffneten Tabellen ab. Deshalb hast du auch je nach Komplexitiät einer Abfrage mal Pausen zwischendurch.
Mittels Fetch n rows ist die Anzahl der DB-Aufrufe minimiert, so dass du Daten etwas schneller abrufst.

Die Begründung der Aktualität ist für mich keine, denn durch die heutigen Apps und Browser sind Daten immer nur eine Momentaufnahme. Du musst schon neu laden um zu aktualisieren.
Das ist nun überhaupt nichts neues mehr.

Und was machst du, wenn der User gar nicht blättert? Dann können ebenso Daten bereits geändert, neue hinzugefügt oder angezeigte gelöscht worden sein.
Das, was du vorhast ist im Endeffekt kein Komfort, da das Arbeiten mit der Subfile dann nur länger dauert (soweit messbar). Und F5 zu drücken (oder Refreshabuttons) ist man spätestens mit der Einführung des Internets gewöhnt. Unter Umständen muss man sogar schon mal den Browsercache leeren um die aktuelle Version des Anzeigeteils zu bekommen.

Für dein Beispiel eignet sich tatsächlich ein scollable sensitve Cursor am besten mit dem du beliebig vorwärts und rückwärts positionieren kannst und immer aktuelle Daten hast.
Bei änderungen des Filters oder Sortierung öffnest du dann neu.

[B.Hauser]

Was sollen denn die ganzen Spekulationen?
Probiert's doch aus!

Mit Limit und Offset wird das Ergebnis begrenzt (beim Open wird natürlich das ganze SQL ausgeführt, wie sollte sonst bekannt sein wieviele Datensätze vorhanden sind - beim Limit und Offset ggf. nur bis zum Ende angeforderten Daten). Die Datensätze sind natürlich immer aktuell, da beim OPEN alles aktualisiert wird.
Allerdings geschieht das in Form von allen möglichen zwischen gespeicherten Pointern und temporären Objekten etc.
Die Materialisierung, also die Ausgabe der tatsächlichen (abgerufenen Daten) erfolgt so spät wie möglich, also erst kurz vor der Ausgabe. Das bedeutet, dass (im Gegensatz zu einem Scroll Cursor) eben nicht die ganzen Datensätze gelesen und geladen werden. Deshalb können mit Limit und Offset die Abfragen tatsächlich (beschleunigt bzw. optimiert werden).

Hier ein kleines Beispiel zum Ausprobieren (im Übrigen mit Statischem SQL, aber Multiple-Row Fetch).

//------------------------------------------------------------------------------
DCL-DS RefDSSales Qualified Template;                                           
  SalesDate  Date;                                                              
  CustNo     VarChar(15);                                                       
  Name       VarChar(35);                                                       
  Amount     Packed(11: 2);                                                     
End-DS;   
//------------------------------------------------------------------------------
DCL-C GblMaxRows       Const(100);                                              
                                                                                
DCL-S Index1      Uns(3);                                                       
DCL-S Index2      Uns(3);                                                       
DCL-S Text        Char(50);                                                     
DCL-S NbrRows     Int(5);                                                       
DCL-S StartDate   Date(*ISO)   Inz(D'2000-01-01');                              
                                                                                
DCL-DS DSSales    LikeDS(RefDSSales) Dim(GblMaxRows);                           
                                                                                
DCL-DS PGMSDS     PSDS Qualified;                                               
  MsgText         Char(80) Pos(91);                                             
  MsgTextK        Char(50) Pos(91);                                             
End-Ds;                                                                         
//------------------------------------------------------------------------------
Exec SQL Set Option Commit=*NONE, DatFmt=*ISO;                                  
                                                                                
*INLR = *On;                                                                    
Monitor;                                                                        
                                                                                
  For Index1 = 1 to 6;                                                          
      Select;                                                                   
      When Index1   = 1;                                                        
           NbrRows  = ListSales(DSSales: '10001': StartDate: 10);               
      When Index1   = 2;                                                        
           NbrRows  = ListSales(DSSales: '10001': StartDate: 10: *On);          
      When Index1   = 3;                                                        
           NbrRows  = ListSales(DSSales: '10001': StartDate:  3: *OFF: 9);      
      When Index1  >= 4 and Index1 <= 6;                                        
           NbrRows  = ListSales(DSSales: '10001': StartDate: 3: *On);           
      EndSl;                                                                    
                                                                                
      If NbrRows > *Zeros;                                                      
         Dsply ('Index: '+ %Char(Index1) +' '+ %Char(NbrRows) +' Rows');        
         For Index2 = 1 to NbrRows;                                             
             Text = 'Date: '    + %Char(DSSales(Index2).SalesDate) +            
                    ' CustNo: ' + %Trim(DSSales(Index2).CustNo) +               
                    ' Amount: ' + %Char(DSSales(Index2).Amount);                
             Dsply Text;                                                        
         EndFor;                                                                
      Else;                                                                     
         Dsply 'Not Found';                                                     
      EndIf;                                                                    
  EndFor;                                                                       
                                                                                
On-Error;                                                                       
  Dsply PGMSDS.MsgTextK;                                                        
EndMon;                                                                         
                                                                                
Return;                                                                         
//******************************************************************************
DCL-Proc ListSales;                                                             
                                                                                
  DCL-PI *N        Int(5);                                                      
     POutSales     LikeDS(RefDSSales) Dim(GblMaxRows);                          
     ParCustNo     VarChar(15)        Const  Options(*Trim);                    
     ParSalesDate  Date(*ISO)         Const;                                    
     ParNbrRows    Int(5)             Const  Options(*NoPass);                  
     ParNext       Ind                Const  Options(*NoPass);                  
     PInStart      Int(5)             Const  Options(*NoPass);                  
  End-Pi;                                                                       
                                                                                
  DCL-S RtnNbrRows  Int(5);                                                     
                                                                                
  DCL-S LocNbrRows  Int(5)            Inz(GblMaxRows);                          
  DCL-S LocMaxRows  Int(5);                                                     
  DCL-S LocNextNbr  Int(5)            Static;                                   
  //----------------------------------------------------------------------------
  If      %Parms >= %ParmNum(ParNext) and ParNext = *On;                        
  ElseIf  %Parms >= %ParmNum(PInStart) and PInStart > 0;                        
     LocNextNbr = PInStart;                                                     
  Else;                                                                         
     Clear LocNextNbr;                                                          
  EndIf;                                                                        
                                                                                
  If     %Parms >= %ParmNum(ParNbrRows)                                         
     and ParNbrRows >= *Zeros and ParNbrRows <= GblMaxRows;                     
     LocNbrRows = ParNbrRows;                                                   
  EndIf;                                                                        
                                                                                
  Exec SQL                                                                      
       Declare CsrC01 Cursor For                                                
          Select SalesDate, s.CustNo, CustName1, Amount                         
             from SalesX s join AddressX a on s.CustNo = a.CustNo               
                  Where     s.CustNo  = :ParCustNo                              
                        and SalesDate > :ParSalesDate                           
             Order By SalesDate, CustNo                                         
             Limit :LocNbrRows   Offset :LocNextNbr                             
             For Fetch Only;                                                    
                                                                                
  Exec SQL  Close CsrC01;                                                       
  Exec SQL  Open  CsrC01;                                                       
                                                                                
  LocMaxRows = GblMaxRows;                                                      
  Exec SQL  Fetch Next From CsrC01 For :LocMaxRows Rows                         
                                   into :POutSales;                             
  RtnNbrRows = SQLER3;                                                          
  LocNextNbr += SQLER3;                                                         
                                                                                
  Exec SQL  Close CsrC01;                                                       
                                                                                
  Return RtnNbrRows;                                                            
                                                                                
End-Proc ListSales;