Verarbeiten von großer Ergebnismenge wird immer langsamer

[Bbommel]

Hallo zusammen,

ich habe ein ähnliches Problem, wie es jemand auch schon mal hier beschrieben hat - leider gabs damals keine weitere Rückmeldung des Benutzers und somit auch keine Lösung.

Also, das ganze mal von mir: Ich habe eine Datenbank-Abfrage, bei der es sei kann, dass die Ergebnismenge recht groß wird. In einem Beispiel habe ich hier >500.000 Datensätze, die vom Server zurückkommen. Das Problem ist dann, dass es recht schnell geht, bis die Antwort vom Server zurückkommt, also Query.Open kehrt nach ca. 17 Sekunden mit seinen 500.000 Datensätzen zurück. Danach allerdings sollen diese Datensätze in einer Schleife verarbeitet werden und eben diese Schleife wird immer langsamer.

Ein bisschen Code kann das Problem sicher veranschaulichen und ihr müsst weniger raten, wo der Fehler liegen könnte.

Zunächst mal das Initialisieren und Aufbauen der Verbindung zum Server - spannend ist ja hierbei vielleicht der ConnectionString und der CursorType und LockType:

Delphi-Quellcode:

procedure TmyThread.InitDB;
var
  tempStr: string;
begin
  currConnection.Provider:='SQLOLEDB.1';
  currConnection.LoginPrompt:=configAuthType=vatAlwaysAsk;
  currConnection.DefaultDatabase:=StringReplace(configDatabase,'.','_',[rfReplaceAll]);

  // den Connection-String basteln
  tempStr:='Provider=SQLOLEDB.1;';
  if configAuthType=vatWindows then
    tempStr:=tempStr+'Integrated Security=SSPI;';
  tempStr:=tempStr+'Persist Security Info=False;';
  tempStr:=tempStr+'Initial Catalog='+configDatabase+';';
  tempStr:=tempStr+'Data Source='+configServername+';';
  tempStr:=tempStr+'Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;';
  currConnection.ConnectionString:=tempStr;

  // Verbindung mit der Datenbank initialisieren
  currDBstate:=vdbConnected;
  currErrorText:='';
  try
    if (configAuthType=vatAlwaysAsk) or (configAuthType=vatSavePw) then
      currConnection.Open(configUser,configPassword)
    else
      currConnection.Open;
  except
    on e: Exception do begin
      currDBstate:=vdbError;
      currErrorText:=e.Message;
    end;
  end;

  if currDBstate=vdbConnected then begin
    currQuery.Connection:=currConnection;
    currQuery.CursorType:=ctOpenForwardOnly;
    currQuery.LockType:=ltReadOnly;
  end;
end;

Was den ConnectionString angeht, so habe ich mir da mal was vom Delphi-Assistenten basteln lassen und für mich angepasst (also vor allem für die Konfigurierbarkeit).

Nun zum kritischen Teil, der mir Kopfzerbrechen bereitet:

Delphi-Quellcode:

function TmyThread.QueryDB: boolean;

// Die Funktion wird true, falls neue Daten in die Liste eingetragen wurden

var tempDate: TDateTime;
    tempStrDate: string;
    oldId, oldFlag: string;
    oldCount: integer;

begin
  currQuery.SQL.Text:=configSQLstatement.Text;

  currDBstate:=vdbConnected;
  currErrorText:='';
  lastQueryTime:=now;
  try
    currQuery.Open;
  except
    on e: Exception do begin
      currDBstate:=vdbError;
      currErrorText:=e.Message;
    end;
  end;

  // wenn wir noch connected sind, war die Abfrage wohl erfolgreich und wir können sie
  // verarbeiten
  if currDBstate=vdbConnected then begin
    oldid:='';
    oldFlag:='';
    // das Füllen der Ergebnis-Struktur darf nur im kritischen Abschnitt stattfinden, da diese
    // auch für den Hauptthread veröffentlicht wird
    CriticalSec.Acquire;

    while (not currQuery.Eof) and (not Terminated) do begin
      // wir erzeugen für jeden Datensatz einen neuen Eintrag in die Ergebnisliste, es sei denn:
      // 1. Es gibt keine ID, dann können wir damit nix anfangen
      // 2. Der aktuelle und der letzte Datensatz haben das Kennzeichen B und die selbe ID - das wiederholt sich gerne mal, brauchen wir aber nicht jedes Mal zu übernehmen
      if (not currQuery.Fields[1].IsNull) and ((currQuery.Fields[1].AsString<>oldid) or (currQuery.Fields[10].AsString<>'B') or (currQuery.Fields[10].AsString<>oldFlag)) then begin

        // hier würde dann eigentlich das Ergebnis in eine interne Datenstruktur übernommen,
        // aber dazu kommt es meist gar nicht - siehe Erklärung unten.

      end;

      currQuery.Next;
    end;
    currQuery.Close;

    Result:=messageList.Count<>oldCount;

    CriticalSec.Release;
  end else
    Result:=false;
end;

Das eigentliche Erstellen der internen Datenobjekte habe ich mal weggelassen, weil in meinem Testfall die ersten 100.000 Datensätze die if-Abfrage schon nicht erfüllen und daher übersprungen werden, dennoch tritt mein Problem auf. Will heißen: Am Erstellen der internen Objekte kann es definitiv nicht liegen.

So, nun also noch mal das eigentlich Problem: Die while-Schleife wird immer langsamer durchlaufen. Wie gesagt, das currQuery.Open kehrt nach ca. 17 Sekunden zurück. Wenn ich dann nach etwa 5 Sekunden in der while-Schleife einen Breakpoint setze, hat sie schon 25.000 Datensätze durch. Setze ich nach weiteren 5 Sekunden einen, hat sie es gerade mal bis zum Datensatz 40.000 geschafft und nach einer Minute ist man dann beim 80.000ten Datensatz angekommen. Sind keine ganz exakten Werte, sollten aber das Problem beschreiben.

Ich verstehe das nicht. Zum Zeitpunkt dieser Verarbeitung liegen die Ergebnisse doch schon längst im Speicher und werden nicht mehr live von der Datenbank gezogen, oder? Jedenfalls kann ich die Datenbank auch stoppen und die Schleife läuft munter weiter.

Ach so, was euch vielleicht auch noch interessieren könnte: Das SQL-Statement ist ein ganz einfaches SELECT * FROM view - ich frage in diesem Testfall also einfach alle Datensätze eines Views ab. Der View selbst wiederum ist ein bisschen komplizierter, ein SELECT mit JOINS über mehrere Tabellen, aber letztlich auch nicht weiter wild. Zumal, so wie ich das verstanden habe, da ja das Problem nicht liegen kann, denn der Server hat die Abfrage ja nach den besagten 17 Sekunden ausgeführt.

Irgendwelche Ideen, was hier falsch läuft?

Dankbar für jeden Tipp
Bommel

PS: Bitte keine Hinweise, dass ich die IDs, die NULL sind, doch direkt im SQL-Befehl wegfiltern könnte - das geht an meinem Problem vorbei.

[sirius]

Hast du dir mal angesehen, wie groß dein Speicherverbrauch wird? Evtl. arbeitest du schon direkt in der Auslagerungsdatei.

[Bbommel]

Vor der Abfrage belegt das Programm ca. 170 MB an Speicher. Wenn es zum ersten Mal an der while-Schleife ankommt, sind es ca. 310 MB. Damit sollte es eigentlich noch locker in den Hauptspeicher (2 GB) passen und nicht ausgelagert werden.

Bis denn
Bommel

[hoika]

Hallo,

currQuery.CursorLocation = clUseServer ?
currQery.Unidirectional = True ?

Ausserdem solltest du das Is Not Null in die Query reinpacken,
warum sollen diese Datensätze übers Netz gehen,
wenn du sie eh nicht brauchst?


Heiko

[nahpets]

Hallo,

meine Erfahrung mit SQL-Server ist (mit großen Datenmengen) nicht so groß, aber bei Oracle habe ich schon ähnliche Erfahrungen gemacht.

Wenn man ein SQL absetzt, legt die Datenbank los. Du bekommst nach 17 Sekunden die ersten Ergebnisse, d. h. aber nicht, dass die Datenbank mit ihrer Abfrage schon fertig ist. Nach 17 Sekunden liegen nur die ersten Daten vor (das mag jedoch auch datenbankabhängig sein).

Wenn Du Dein SQL mal in einem DB-Grid anzeigen lässt, solltest Du auch nach ca. 17 Sekunden die ersten Ergebnisse sehen, gehst Du nun mit 'nem DB-Navigator zum letzten Datensatz, kann das schon eine Weile dauern. Habe hier schonmal bei 'ner Oracle-Datenbank und sehr vielen Daten (mehrere Mio. Sätze), Stunden gewartet, bis der letzte Satz zu sehen war.

Es wäre also denkbar, dass Du die Daten schneller abholst, als die Datenbank sie liefern kann.

Mess' mal spasseshalber die Zeit, die vergeht, wenn Du nach dem Open direkt ein Last ausführst. Wann bist Du dann am Ende der Datenmenge? Weitere Sekunden oder Stunden. Bei Sekunden hast Du das Problem im Programm, andernfalls liefert die Datenbank die Daten nicht schneller und Du musst im Programm warten.

Beobachte mal im Programmverlauf im Taskmanager die Speichernutzung, steigt der Speicherverbrauch, wird eventuell im Laufe der Zeit soviel Speicher verbraucht, dass Windows anfängt auszulagern? Wie ist die Speicherentwicklung während der Abarbeitung der While-Schleife, wächst der Verbrauch weiter an?

Hast Du irgendwelche Komponenten, die während der Abarbeitung der Daten, diese in der Oberfläche anzeigen (DB-Grid, DBEdits...)? Dann deaktiviere die mal beim Abarbeiten der Daten.

Auslagerung: Je nach Windowskonfiguration wird auch ausgelagert, wenn der Arbeitsspeicher noch ausreichend groß ist. Windows ist in der Lage, den gesamten Arbeitsspeicher immer auch in die Auslagerungsdatei zu schreiben, um beim Absturz aus der Auslagerungsdatei das Speicherabbild in eine Datei zu ziehen. Ist nun auch noch die Auslagerungsdatei mit einer dynamischen Größe konfiguriert, muss Windows immer wieder ausreichend Platz reservieren. Bei einer stark fragmantierten Platte hat diese dann ziemlich was zu tuen.

[p80286]

Hallo Bommel,

die Idee von Sirius (#2)ist garnicht so abwegig, ich bin auch mal in die Speicherfalle getappt.
"Damit sollte.." heißt nicht das es das auch tut!

Zitat:

Ausserdem solltest du das Is Not Null in die Query reinpacken,
warum sollen diese Datensätze übers Netz gehen,
wenn du sie eh nicht brauchst?

!!!

Gruß K-H

[Bbommel]

Zitat von hoika:

currQuery.CursorLocation = clUseServer

Jo, du bist mein Held. Damit ist die gesamte Abfrage inklusive Verarbeiten der Daten in 6 Sekunden durch. Bin begeistert.

Jetzt muss ich nur noch verstehen, warum das so viel schneller geht. Okay, dass das Open schneller geht, ist mir völlig klar, aber ich hätte jetzt ganz naiv angenommen, dass es eigentlich schneller sein müsste, wenn man Daten verarbeitet, die die Anwendung schon lokal in ihrem Speicher liegen hat. Kann ja dann fast nur noch daran liegen, dass da doch schon irgendwas ausgelagert wird, wir hier einige meinten.

Zitat von hoika:

currQery.Unidirectional = True ?

Gibbet nicht. Ich kann nur currQuery.isUnidirectional abfragen, aber nicht setzen...

Zitat von hoika:

Ausserdem solltest du das Is Not Null in die Query reinpacken,
warum sollen diese Datensätze übers Netz gehen,
wenn du sie eh nicht brauchst?

Weil ich dann keine 500.000 Datensätze mehr bekommen hätte, mit denen ich dieses Problem reproduzieren konnte. Ganz einfach. Ich wollte das Problem aber jetzt lösen, da es im späteren Echt-Einsatz auch vorkommen kann, dass tatsächlich 500.000 "echte" Datensätze abgefragt werden müssen, bei denen nichts mehr NULL ist.

Danke für die Hilfe und bis denn
Bommel

[hoika]

Hallo,

mit clUseServer wird der Cursor, also eine Struktur zum Navigieren,
auf dem Server ezeugt.
Mit clUseClient auf dem Client.

Das Dumme am SQL-Server ist aber, dass auf dem Server immer ein
Cursor erzeugt wird.

Es wurden also 2 Cursor angelegt, einer auf dem Client,
einer auf dem Server.

Faust-Regel war wohl:
clUseClient für lokale DB's (Access)
clUseServer für SQL-Server


Heiko

[Bernhard Geyer]

Zitat von hoika:

Faust-Regel war wohl:
...
clUseServer für SQL-Server

Wobei man bedenken sollte das ein Serverseitiger Curser für den MS SQL Server eine ziemliche belastung ist, da dieser Server erst AFAIK mit der neuesten Version das Multiversionskonzept unterstützt. D.h. der Server erstellt erstmal für die 500k Ergebnisdatensätze ein Temporäre Tabelle in der das Ergebnis kopiert wird. Besser ist es:

- Die Ergebnismenge per WHERE/HAVING auf minmimal Größe zu bringen
- Sollte nur die Daten in einer Schleife ausgelesen werden so ist ein Forward-Only Curser Ressourcentechnisch das beste. Jedoch weiß nicht ob man die übe den dbGO-Wrapper erstellen kann. Direkt mit den ADO-Interfaces ist das kein Problem.

[hoika]

Hallo Bernhard,

  currQuery.CursorType:= ctOpenForwardOnly;
Der Cursor war schon so gesetzt.


Heiko