SQL Techniken in Verbindung mit Subfile

[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;

[Fuerchau]

"im Gegensatz zu einem Scroll Cursor" ???
Ein sensitiver cursor wird dies wohl kaum machen, sonst wäre er nicht sensitive.
Und offset muss eben immer mehr überlesen, je weiter man nach hinten kommt.
Limit kann ich im Prinzip durch aufhören des Lesens auch erreichen;-).

[harkne]

@Bender,

genau das ist im Programm bereits drin. Der Benutzer kann auf 3 verschiedene Spalten klicken zum Sortieren und hat im Header Bereich mehrere Auswahlmöglichkeiten. Außerdem gibt es ein Feld zum Positionieren auf die sortierte Spalte. Das geht auch alles. Bisher wurde die Subfile immer seitenweise nach vorn gefüllt, dies wollte ich jetzt umstellen auf nur das Füllen was man sieht. Nur das ist die Änderung im Programm.
Die Umstellung deshalb, weil beim Positionieren bisher das ganze Resultset durchgelesen werden musste bis man an der entsprechenden Stelle war. Beim Positionieren wurde dann auch gleichzeitig die Subfile mitgefüllt. Habe ich haber 2,5 Mio Sätze dann dauert das und vor allem ist bei Satz 10000 in der Subfile Schluss. Deshalb die Umstellung auf eine Subfile in der nur gefülllt ist was man sieht und ich hatte gehofft dass dann ja die Positionierung schneller geht da ich ja direkt aufsetzen kann. Dann ist mir aber das Problem mit dem Rückwärtsblättern aufgefallen da ja keine Daten vorher im Recordset da sind. Deshalb kam meine ursprüngliche Frage.

@Fuerchau
Hatte ich ja bereits geschrieben dass selbstverständlich bevor der Benutzer eine Auswahl eingibt, nochmal geprüft werden muss ob der Satz da ist oder nicht. Ich habe ja auch geschrieben, dass ich kein Problem damit habe wenn die Sätze die durch andere Anwender geändert hinzugefügt oder gelöscht wurden nicht gleich aktuell sind. Aber bitte. Wenn ich selbst einen Satz lösche oder auch hinzufüge über das Programm, dann hätte ich in der Anzeige auch gerne die Änderungen. Bei einer vorwärts gefüllten Datei ist das aber etwas problematisch da ich das ja manuell machen muss. Deshalb haben wir auch bisher (ohne SQL) bei kleinen Datenmengen wieder neu eingelesen oder aber eben eine Subfile verwendet in der nur das drin steht was man auch sieht. Auch das Positionieren ist mit SETLL und READ unproblematisch aber wenn wie bei diesem Programm es verschiedene Sortiermöglichkeiten und Auswahlen gibt, dann würde ich gerne nicht alles im Quellcode abfragen müssen sondern verwende SQL
Für mich ist es jetzt nur wichtig wie kann ich ein SQL auf eine große Datenmenge absetzen, dass es so schnell wie möglich ein Ergebnis liefert.

@Hauser
Ich spekuliere nicht ich frage und probieren kann ich nur Sachen die ich weiß. Deshalb bin ich der vielen Antworten hier sehr dankbar. Denn damit kann ich viele Sache probieren. Aber es ist nun mal so dass wenn ich ein SQL zum 1. mal absetze es langsamer ist als beim 2. mal. Wenn ich jetzt verschiedene Techniken probiere und er mir bei einer Technik wegen des Abrufs der vorherigen Technik ein schnelleres Ergebnis liefert bin ich nicht viel schlauer, deshalb auch meine Fragen. Natürlich werde ich alles versuchen zu probieren, aber auch der Code sollte am Ende noch wartbar bleiben.
Das mit dem LIMIT ist aktuell auch mein Favorit. Bis jetzt. Aber ist der OFFSET wirklich nötig, wie gesagt kann man auch auch den >= mit ins SQL machen. Natürlich weiß ich mal wieder nicht was schneller ist :-)
Vielen Dank für den Quellcode

@alle
Vielen lieben Dank für die vielen Vorschläge und Anregungen und Hilfe. Ich habe in 2 Tagen einiges dazu gelernt.

Viele Grüße Harald

[Fuerchau]

Ein SQL ist immer beim 1. Mal langsamer als ab dem 2. Mal.
Beim 1.Mal müssen Zugriffswege analysiert werden sowie diverse andere Dinge.
Wenn der SQL nicht geändert wird, ist es beim 2. Mal deshalb schneller, weil eben alles bereits optimiert wurde.
Änderst du den SQL (weil er ja dynamisch ist), fängt eben alles wieder von vorne an.
Wenn ein SQL dann geschlossen wird, werden beim 1. Mal alle betroffenen Dateien ebenso geschlossen Beim Schließen des 2. Mal bleiben dann die geöffneten Dateien auch offen.

Und wie bereits gesagt: Deine Anforderung erledigst du am schnellsten mit einem scrollable sensitive Cursor. Nach dem Löschen oder verändern positionierst du eben um N Sätze rückwärts und füllst neu.
Beim vorwärst blättern liest du einfach weiter.
Beim umsortieren oder ändern des Filters machst du einen neuen Open.

Dein Problem mit dem Key >= xxx löst du ja auch nicht wenn du dir nicht in einer Tabelle die Schlüssel je Seite merkst (was ich in anderen Programmen schon durchaus gesehen habe).
Du musst dir dann die Keyvalues in einer DIM-Struktur je Seite den 1. Schlüssel merken und kannst beim rückwärts blättern dann korrekt wieder positionieren.
Dazu bedarf es allerdings auch bei der Sortierung einer Ergänzung zu größerer Eindeutigkeit (z.B. einer Identity-Spalte), denn wenn du 3 Seiten lang den Namen "Müller" angezeigt hast, möchtest du beim Rückwärtsblättern ja auch die 2. Seite von "Müller" ansehen.
Da eine DS (auch mit Dim) auf 16MB beschränkt bist, kannst du bei einer Schlüssellänge von 100 immerhin noch 160.000 Seiten verwalten.
Ich denke nicht, dass dies ein User schafft, alle Seiten sinnvoll durch zu blättern. Der Tag hat ja nur 86400 Sekunden.

Beim Sensitive Cursor positionierst du einfach 2xN Zeilen zurück. Wenn dann ein Satz dazwischen eingefügt wurde ist das letztlich ja auch egal.

[harkne]

Also ich werde das jetzt im 1. Step wie folgt versuchen.

Bei der ersten Anzeige gibt es ja noch keine vom Benutzer festgelegte Sortierung oder Auswahl also werde ich ein SQL ausführen mit LIMIT 15. Dies werde ich immer machen wenn eine Umsortierung oder eine Selektion von Seiten des Benutzers erfolgt.

Beim ersten vorwärtsblättern, werde ich das gleiche SQL ohne LIMIT und dann immer 15 Sätze beim vorwärtsblättern vorlesen (Scrollable sensitive Cursor fetch next)

Beim rückwärts blättern das gleiche in umgkehrer Reihenfolge (fetch prior)

Jetzt halt die Problematik wenn er positionierten möchte.
Falls er positioniert werde ich das SQL wieder absetzen diesmal aber mit WHERE Keyfeld >= Benutzereingabe

Das geiche wie oben.

Falls er rückwärts blättert (und positioniert hatte) werde ich die Variante für einen 2. Cursor von Bender verwenden. Bei dem die Daten vor der Positionierung in umgekehrter Reihenfolge gelesen werden aber mit der selben vorgehensweise wie beim 1. Cursor.

Bitte kurz abnicken :-)

[Fuerchau]

Muss ich das verstehen?
Warum Limit 15 wenn du doch nach 15 Zeilen fertig mit dem Lesen bist?
Beim Blättern nach vorne einfach weiter lesen, beim Blättern rückwärts einfach 30 Zeilen rückwärts positionieren und wieder füllen.
Bei einem Filter mit "Where"-klausel ergänzen und wieder öffnen.
Bei einer Sortierung mit "order by" ergänzen und wieder neu öffnen.
Beim Blättern brauchst du nichts zu ändern.

Aber warum einfach, wenns auch kompliziert geht;-).

[harkne]

Das genau will ich doch machen, klar ob man bei der ersten Seite ein LIMIT15 macht oder nicht kann man sich überlegen. Das Problem ist und bleibt die Positionierung. Wenn ich jetzt die erste Seite anzeige und der User sagt ich möchte zu Müller positionieren. Soll ich dann das Telefonbuch durchlesen bis ich bei Müller bin?

[Fuerchau]

Das halte ich eher für einen Filter. Das kann man einem User auch erklären.
Wenn man mit "Müller" aufsetzt, wird erst ab Müller gelesen. Ein Rückwärtsblättern würde ich da ausschließen.
Suche ich was anderes als Müller, gebe ich einen neuen Suchbegriff ein.

Ich würde die Eingaben auch als Filter deklarieren. Wenn kein "Müller" da ist, wird auch kein "Neumeier" mehr angezeigt.

Das ist doch wie auf Webseiten (wie z.b. Fahrzugsuche) auch: Wenn es zu viele Ergebnisse in der Suche sind soll man weiter einschränken.