Große Datenmengen richtig verarbeiten bzw. Out of Memory

[Monday]

Hallo,

ich habe eine große Datenbank ( > 1 Mil. Datensätze ) und möchte die einzelnen Datensätze einzeln weiterverarbeiten.
Bei kleineren Datenmenge habe ich mir nie Gedanken über select & co gemacht. Bei größeren Datenmenge bekomme ich regelmäßig "Out of Memory" Probleme (Nicht zuletzt wegen geringen Arbeitsspeicher (4 GB)). Einzige Möglichkeit sehe ich, die Datensätze nur Häppchenweiße einzulesen. Schön finde ich das allerdings nicht, wie ich finde.

Gibt es noch effektivere Möglichkeiten als diese hier? Hier mein Grundgerüst:

Delphi-Quellcode:

var
temp , stop, a: integer;

begin
// temp ist der "eigene Datensatz Zähler"
//temp := 0; //Anfang der ID's

      Form1.ZQuery3.Close;
      Form1.ZQuery3.SQL.Clear;
      Form1.ZQuery3.SQL.Text := 'select min(id) as mm from tabelle1;';      
      Form1.ZQuery3.Open;
      temp :=  StrToInt(Form1.ZQuery3.FieldByName('mm').AsString);    



 while stop = 0 do begin  //Datensätze nur abschnittsweise laden
   Form1.ZQuery1.Close;
   Form1.ZQuery1.SQL.Clear;
   Form1.ZQuery1.SQL.Text := 'select id,*  from tabelle1 where id >= "'+IntToStr(temp)+'" Limit 100;';  // Limit 100: Ggf. verkleinern
   Form1.ZQuery1.Open;

   Form1.ZQuery3.Close;
   Form1.ZQuery3.SQL.Clear;
   Form1.ZQuery3.SQL.Text := 'select max(id) as mm from tabelle1';
   Form1.ZQuery3.Open;
   if (((StrToInt(Form1.ZQuery3.FieldByName('mm').AsString)-2)) <= temp)  then begin stop :=1; exit; end; // Wenn Ende erreicht von DB, dann Schleife beenden


  for a := 0 to ZQuery1.RecordCount-1 do begin 
  //  *** Hier die einzelnen Datensätze verarbeiten  ***
  end; //for


 end; //while



end;

Liebe Grüße

[mkinzler]

In diesem Code liegt viel im Argen.

Warum die Verweise auf eine Instanz eiesn Formulars (Form1). Wo steht dieser Code in Form1 oder in einem anderen Formular?

Was für einen Typ hat das Feld ID? Warum fragst Du es als String ab und wandelst es dann in einen Integer?
Warum fragst Du nicht am Anfang ab, was das Maximum ist sondern bei jedem Schritt?
Welches DBMS?
Warum keine parametrisierte Abfrage?

[hoika]

Hallo,

Delphi-Quellcode:

for a := 0 to ZQuery1.RecordCount-1 do begin 
  // *** Hier die einzelnen Datensätze verarbeiten ***
  end; //for

select id,*
Brauchst Du wirklich alle Felder der Tabelle?

Das könnte schon das Problem sein.
Hier werden ja nach DB alle Datensätze lokal heruntergeladen und zwischengespeichert.
Ändere das mal ab.

Delphi-Quellcode:

ZQuery1.UniDirectional:= True;
while not ZQuery1.Eof do
begin
  // Datensatz verarbeiten

  ZQuery1.Next;
end;

[p80286]

Wenn zu viele Daten von der DB zurück geliefert werden, warum arbeitest Du dann mit

SQL-Code:

'select id,* from tabelle1 where id ...

Zumindest das Feld ID ist dann doppelt.

Wenn Du schon ID zu irgendwelchen Berechnungen und Vergleichen benutzt (-2,<=), dann solltest Du wenigstens eine auf- oder absteigende Reihenfolge sicherstellen

SQL-Code:

order by id asc/desc

Über einen Integer der als Boolean mißbraucht wird, möchte ich mich nicht weiter auslassen.

Und wenn Du min(ID) bzw. max(ID) benötigst, dann kannst Du das zu Anfang Deiner Verarbeitung abfragen, und mußt nicht die ganze Zeit eine Connection offen halten.

Gruß
K-H

Edith:
nach dem Abholen der Datensätze muß der .RecordCount nicht zwangsläufig korrekt sein.

[jobo]

Die Frage wäre auch, was die Verarbeitung macht?
z.B.
- Selektierte Daten ändern
- neue erzeugen / ggF. in andere Tabelle
- irgendwas zählen
?

Viele solcher Maßnahmen können durch SQL erledigt werden, ohne dass man überhaupt Daten aus der DB lokal holt.

Ist es nichts von dem oben, sondern -sagen wir mal- ein kleiner Datenabgleich und speichern eines Resultats in einer Datei, könnte man den Datenzugriff schlanker gestalten, Stichwort unidirektionale Cursor, Readonly, etc..
Ich weiß allerdings nicht, was die sqlite clients da können.

[himitsu]

Ich hoffe SQLite kann das und ZEROS sollte auch eine Fetch-Funktion bieten,
also wo das DataSet nicht alle Daten runterläd, sondern nur ein Fenster/Teil des ganzen ResultSets.

http://zeoslib.sourceforge.net/viewtopic.php?t=20005
http://forum.lazarus.freepascal.org/...?topic=13192.0

Wenn nicht, dann mußt du das eben selber implementieren, die Abfrage mehrmals mit LIMIT/OFFSET ausführen und die Daten stückchenweise holen.

[jaenicke]

Ich habe zu dem Zweck ein Histogramm zur Werteverteilung vom MS SQL Server gezogen und dann immer eine möglichst gleich große Anzahl von Zeilen abgeholt. Auf diese Weise lassen sich Datenmengen sehr effizient abrufen. (In meinem Fall ging es um threadbasierte Verarbeitung von Teilmengen.)

Zitat:

Zitat von p80286 (Beitrag 1406830)

Edith:
nach dem Abholen der Datensätze muß der .RecordCount nicht zwangsläufig korrekt sein.

Nicht nur das, der Abruf bewirkt auch, dass sofort alle Datensätze von der Datenbank abgeholt werden, auch wenn das an der Stelle eigentlich gar nicht nötig wäre. Dadurch dauert der Start der Verarbeitung unnötig lange.

[freejay]

Leider kenne ich ZEOS nicht und mit SQLite habe ich auch noch nicht viel gemacht.

Wenn es MySQL wäre und Du die MyDac-Komponenten benutzen würdest, müsste man dort nur FetchAll auf false und UniDirectional auf True setzen, dann wird immer nur die unter FetchRows angegebene Anzahl von Datensätzen im Speicher gehalten und man kann mit einer einzigen While-Schleife beliebig große Datenbanktabellen oder Query-Ergebnisse durchlaufen. Keine Gefahr von Out-Of-Memory.

Vielleicht gibt es ja bei ZEOS & SQLite ähnliche Optionen?

[hoika]

Hallo,

Zitat:

FetchAll auf false und UniDirectional auf True

wie ich schon weiter oben geschrieben hatte.
(FetchAll ist standardmäßig ausgeschaltet und sollte nur aus Kompatibilitätsgründen zum Bsp. für den BDE-Ersatz benutzt werden).

[mjustin]

Zitat:

Zitat von hoika (Beitrag 1406828)

Delphi-Quellcode:

ZQuery1.UniDirectional:= True;
while not ZQuery1.Eof do
begin
  // Datensatz verarbeiten

  ZQuery1.Next;
end;

Das Setzen von UniDirectional auf True muss vor dem Öffnen (ZQuery1.Open) der Abfrage erfolgen, danach ist es "zu spät", wenn ich mich nicht irre?

[freejay]

Davon gehe ich aus.

[Bernhard Geyer]

Zitat:

Zitat von freejay (Beitrag 1406919)

Davon gehe ich aus.

Würde ich nicht.
Bei SQLite läuft die DB im eigenen Adressraum (Embedded).
Das was bei einer richtigen SQL-Datenbank mit eigenen Prozess gut/positiv ist, kann für eine Embedded-DB den gegenteiligen Effekt bewirken.

[freejay]

Also ich hatte auf den vorherigen Post

Zitat:

Das Setzen von UniDirectional auf True muss vor dem Öffnen (ZQuery1.Open) der Abfrage erfolgen, danach ist es "zu spät", wenn ich mich nicht irre?

mit

Zitat:

Davon gehe ich aus.

geantwortet.

Bezieht sich darauf Deine Antwort?

[Jasocul]

Wenn wir schon bei solchen Dingen sind, dann noch von mir der Hinweis, dass vor dem

Delphi-Quellcode:

while not ZQuery1.Eof do
begin
   // Datensatz verarbeiten

   ZQuery1.Next;
end;

noch ein

Delphi-Quellcode:

ZQuery1.First;

gemacht werden sollte.
Nach einem Open kann es in einigen Fällen dazu kommen, dass EOF true ist.
Bekannt sind mir folgende Fälle:
- kein FetchAll
- Nur ein Datensatz in der Ergebnismenge
Das passierte mir in den Fällen auch nur sehr selten, aber ich mache immer ein First, seitdem ich mal Probleme damit hatte.

Es ist gut möglich, dass das Problem in neueren Delphi-Versionen nicht mehr existiert. Das letzte mal hatte ich das Problem mit XE2.

[hoika]

Hallo,

Zitat:

Query.First machen wegen Problemen.

Gut zu wissen!
Welche DB und welche Version von FireDAC hattest Du denn damals?

[Schokohase]

Zitat:

Zitat von hoika (Beitrag 1406923)

Welche DB und welche Version von FireDAC hattest Du denn damals?

Eigentlich sprechen wir hier über ZEOS

[Jasocul]

@hoika:
Das Phänomen hatte ich mit verschiedenen DBs und Zugriffskomponenten.
Die genutzten Query-Komponenten waren aber Ableitungen von TDataSet.
btw: Das ist kein BDE-Problem. Das nutze ich schon gefühlte Jahrhunderte nicht mehr.

Es mag durchaus sein, dass das mit Zeos und Embedded-Datenbanken nicht passiert, aber das kann ich bei mir nicht prüfen.

Übrigens hatte ich auch mal Probleme mit

Delphi-Quellcode:

IsEmpty

. Im TDataSet gibt es dafür wohl ein Vergleich auf Recordcount, was durchaus mal fehlschlagen kann, da das auch 0 sein kann, wenn Datensätze gefunden wurden. Meines Wissens hängt das von der DB und der Property FetchAll ab. Daher prüfe ich nur noch, ob eof und bof gleichzeitig true sind.

[p80286]

Zitat:

Zitat von Jasocul (Beitrag 1406922)

Nach einem Open kann es in einigen Fällen dazu kommen, dass EOF true ist.
Bekannt sind mir folgende Fälle:
- kein FetchAll
- Nur ein Datensatz in der Ergebnismenge
Das passierte mir in den Fällen auch nur sehr selten, aber ich mache immer ein First, seitdem ich mal Probleme damit hatte.

Es ist gut möglich, dass das Problem in neueren Delphi-Versionen nicht mehr existiert. Das letzte mal hatte ich das Problem mit XE2.

Interessant. Ich habe unter D7 / 2006 nie ein .First gebraucht und .EOF war false es sei denn es kam eine leere Menge zurück, da hatte ich wohl Glück!
(oder könnte es sein, daß die Qualität der DB-Komponenten nicht so konsistent ist wie wir es gerne hätten)

Gruß
K-H

[Jasocul]

Ich denke, das ist wie mit dem Vergleich einer Boolean Variablen auf True/False. Normalerweise läuft trotzdem alles korrekt.

Mir ist es einfach vor Jahren mal passiert und seitdem gehe ich den sicheren Weg.

[Delphi.Narium]

Mir ist es auch ganz selten mal passiert, das BoF, EoF "falsche" Werte lieferten.

Das liegt wohl weniger an den Komponenten, sondern an dem, was die Datenbank (oder irgendwas aus den Zwischenschichten, Clients, Treibern, wieauchimmer ...) da so an Infos "rausrückt".

Identische Komponenten, aber unterschiedliche Datenbanken können, wenn auch seeeeeehr selten, zu solchen Problemen führen.

Ja nach Datenbank kann es auch passieren, dass RecordCount identisch mit RecNo ist, solange man nur vorwärts scrollt (per Next). Beim Rückwärtsscrollen bleibt RecordCount auf der RecNo des höchsten, bisher erreichten, Datensatze. Scrollt man über den hinaus, steigt RecordCount solange auf RecNo, bis man einmal EoF erreicht hat.

So wie First zu einem korrekten EoF führen kann, kann Last zu einem korrekten RecordCount führen (wobei dann aber die gesamte Datenmenge geladen wird und das kann dauern).

Und wenn man (bei unverändertem Quelltext) den Datenbanktyp wechselt, kann es sein, dass das Problem weg ist.

Aber: Das ist mir in ca. 22 Jahren so 3 oder 4 Mal passiert. Es ist ein sehr selten auftretender Fehler.

Erlebt in Delphiversionen bis D7.

[p80286]

Zitat:

Zitat von Delphi.Narium (Beitrag 1407044)

Identische Komponenten, aber unterschiedliche Datenbanken können, wenn auch seeeeeehr selten, zu solchen Problemen führen.

Wo Du es beschreibst...gleiche DB, gleiche Komponente, zwei unterschiedliche ADO-Treiber

Gruß
K-H

[MichaelT]

Diese Unart alles zu holen kam mit Access auf resp. hat Access den Trend verstärkt * zu nehmen anstatt konkrete Datenmengen zu definieren.

In weiter Folge wird kein Wert mehr darauf gelegt ob ein Join Kriterium ein Integer ist usw...

Peu a peu wird's langsam.

Access ware eine der ersten DBs die den Spaltentyp aufgrund der Analyse von ein paar Datensätzen hat geraten.

Zitat:

Zitat von hoika (Beitrag 1406828)

select id,*
Brauchst Du wirklich alle Felder der Tabelle?

Das könnte schon das [/DELPHI]

[MichaelT]

In Ergänzung: Es bietet sich in solchen Fällen über Arrays im Mittel ganz performant auf Datenmengen zugreift.

Wir haben die Arraysize bis zu einem Limit bei jedem Fetch verdoppelt bis das Limit hinter dem keine Geschwindigkeitszugewinn mehr zu holen war.

Zumindest auf Oracle und SQL Server hat die 'Strategie' sehr gute Ergebnisse geliefert egal wieviele Sätze in den Tabellen gespeichert waren.

Zitat:

Zitat von jaenicke (Beitrag 1406847)

Ich habe zu dem Zweck ein Histogramm zur Werteverteilung vom MS SQL Server gezogen und dann immer eine möglichst gleich große Anzahl von Zeilen abgeholt.

[Delphi.Narium]

Zitat:

Zitat von p80286 (Beitrag 1407097)

Zitat:

Zitat von Delphi.Narium (Beitrag 1407044)

Identische Komponenten, aber unterschiedliche Datenbanken können, wenn auch seeeeeehr selten, zu solchen Problemen führen.

Wo Du es beschreibst...gleiche DB, gleiche Komponente, zwei unterschiedliche ADO-Treiber

Gruß
K-H

Exakt.

Wenn man mal bei DB-Zugriffen "ungewöhnliches" Verhalten seitens seines Delphiprogrammes beobachtet:

Es kann sehr hilfreich sein, sich die Datenbankverbindung und die Treiber anzuschauen / anzupassen, statt im Programmquelltext mehr oder weniger verzweifelt nach Workarounds um festgestellte Probleme zu suchen.

Früher (zu BDE-Zeiten) konnte es durchaus hilfreich sein, beim Zugriff auf Oracle auch deren Treiber zu nehmen und nicht den von Microsoft, der (damals?) auf den Systemen schon vorhanden war.

Keine Ahnung, wie sich das bis heute so entwickelt hat, aber die Fehlermöglichkeiten zwischen eigenem Programm und Datenbank sollte man nie ausklammern.

@MichaelT

select * from ist eine Unsitte, die verboten gehört. Es wird nur das selektiert, was man auch benötigt -> kleinstmögliche Ergebnismenge, sowohl in Bezug auf die ausgewählten Spalten als auch auf die ausgewählten Zeilen.

Alles zu holen und dann im Programm nur das benötigte zu verarbeiten ist einfach nur schlecht.

[jobo]

Zitat:

Zitat von Delphi.Narium (Beitrag 1407230)

select * from ist eine Unsitte,..
..
Alles zu holen und dann im Programm nur das benötigte zu verarbeiten ist einfach nur schlecht.

[/quote]

Unsitte, nicht unbedingt
Alles zu holen ist tatsächlich schlecht.

Wenn hinter dem from eine definierte Datenquelle hängt (view), die Spalten und Zeilen wie gewünscht eingrenzt, finde ich es nicht problematisch.
Spaß macht es, wenn man die Datenquelle ändert und "Select *" in der Anwendung einfach die zusätzlichen (oder weniger) Daten liefert.
Dazu gehört allerdings eine eigene Feldverwaltung.

[Delphi.Narium]

Zitat:

Zitat von jobo (Beitrag 1407248)

Wenn hinter dem from eine definierte Datenquelle hängt (view), die Spalten und Zeilen wie gewünscht eingrenzt, finde ich es nicht problematisch.
Spaß macht es, wenn man die Datenquelle ändert und "Select *" in der Anwendung einfach die zusätzlichen (oder weniger) Daten liefert.
Dazu gehört allerdings eine eigene Feldverwaltung.

Naja, prinzipiell hast Du ja recht, aber, wenn einer eine identische Tabelle, 'ne identische View erstellt, aber "nur" die Spalten im Create in 'ner anderen Reihenfolge angibt, kannst Du bereits die ersten Probleme bekommen.

Delphi-Quellcode:

i := qry.fields[1].AsInteger

könnte schon scheitern, weil die hier ursprunglich erwartete Spalte eventuell eine andere Position bekommen hat und wir hier den Inhalt einer Zeichenkette bekommen und nicht den in Spalte 1 erwarteten Integer.

Und wenn man statt * die benötigten Spalten angibt, schreibt man zwar mehr, aber wenn davon eine Spalte "verlustig" gehen sollte, knallt das SQL, die Query beim Open, und nicht erst ein eventuell viel später erfolgender Zugriff auf die "verschwundene" Spalte.

Mag es halt, wenn Fehler möglichst nah an der Fehlerursache auftreten und wenn eine mögliche Fehlerursache im SQL liegen kann, dann möchte ich, dass das SQL mir "um die Ohren fliegt" und nicht erst die Verarbeitung der erhaltenen, aber nicht so erwarteten, Ergebnismenge.

[Monday]

Erstmal vielen Dank für eure Antworten.

Ich habe gerade gesehen, dass mir bei dem Beitrag ein Fehler unterlaufen ist. Ausgerechnet die wichtigste Zeile fehlt:
temp := temp+100;

Dieser kommt am Schluss. Ich habe unten nochmal den überarbeiteten code angefügt.


"Form1." kann man selbstverständlich rausmachen.
"select id,*" - das * war nur zu anschauung. Ich würde auch nur die Felder wählen, die ich wirklich benötige.

Das mit der Fetch Funktion habe ich versucht. Entweder habe ich nicht verstanden wie es richtig geht, oder es geht nicht. Die Funktion wäre schon nice gewesen. Dann könnte ich mir den unteren code sparen; Weil dieser soll ja die Datensätze abschnittsweise einlesen. Aber ich denke, es lohnt sich, mich damit mal ausführlich auseinanderzusetzen.

Das mit .RecordCount habe ich rausgenommen. Ich dachte mit RecordCount wird die maximaldatensätzeanzahl angezeigt. Mit .RecNo wird die aktuelle Datensatz Nr angezeigt?

Hier der neue Code:

Delphi-Quellcode:

var
temp , stop, stopz, a: integer;

begin
// temp ist der "eigene Datensatz Zähler"
//temp := 0; //Anfang der ID's
stop := 0;

      ZQuery3.Close;
      ZQuery3.SQL.Clear;
      ZQuery3.SQL.Text := 'select min(id) as mm from tabelle1;';
      ZQuery3.Open;
      temp := StrToInt(ZQuery3.FieldByName('mm').AsString);

   ZQuery3.Close;
   ZQuery3.SQL.Clear;
   ZQuery3.SQL.Text := 'select max(id) as mm from tabelle1';
   ZQuery3.Open;
   stopz := ((StrToInt(ZQuery3.FieldByName('mm').AsString)-2)); 



 while stop = 0 do begin //Datensätze nur abschnittsweise laden
   ZQuery1.Close;
   ZQuery1.SQL.Clear;
   ZQuery1.SQL.Text := 'select id,* from tabelle1 where id >= "'+IntToStr(temp)+'" Limit 100;'; // Limit 100: Ggf. verkleinern
   ZQuery1.Open;


   if (stopz <= temp) then begin stop :=1; exit; end; // Wenn Ende erreicht von DB, dann Schleife beenden


  for a := 0 to stopz do begin
  // *** Hier die einzelnen Datensätze verarbeiten ***
  end; //for

temp := temp+100; // muss gleich sein wie bei Limit oben
 end; //while



end;

[p80286]

Delphi-Quellcode:

var
  firstid : integer; // ggf cardinal oder int64
  alldone : boolean;
begin
  alldone:=false;
  firstid:=0; // Annahme die IDs sind alle größer als 0
  ZQuery1.Close;
  ZQuery1.SQL.Text := 'select id chkid,* from tabelle1 where id >= :minid order by id  Limit 100;';
  repeat
    Zquery.Parameters.ParambyName('minid').asinteger:=firstid; 
    ZQuery1.Open;
    zQuery1.First;
    if not zquery1.EOF then 
    begin 
      repeat
        firstid:=Zquery1.Fieldbyname('chkid').asinteger;
        //mach was mit dem Datensatz
        Zquery1.next;
      until Zquery1.EOF;
    end
    else 
    begin
      // alle Datensätze sind verarbeitet
      alldone:=true;
    end;
    ZQuery1.close;
    inc(firstid);
  until alldone;
end;

Die Syntax mußt Du noch prüfen da ich ZEOS nicht so genau kenne.
Aber damit solltest Du das erreichen was Du willst.

Gruß
K-H