Viele Datensätze nacheinander erstellen

[Reinhardtinho]

Hi,

ich habe mir ein kleines Musik-Verwaltungstool gebastelt, dass die Dateien in einem Ordner sucht und die Informationen in einem neuen Datensatz in einer Datenbank speichert (Name, Pfad, einige ID3-Tags, ggf. Cover).

Wenn ich die Suche starte, läuft das Programm noch sehr schnell, es werden so ca. 20-40 Datensätze pro Sekunde eingefügt.

Doch ab 5000 etwa wird es sehr langsam und am Ende braucht das Tool 1-2 Sekunden pro Datensatz. Das Taskmanager zeigt an, dass der Prozess sqlservr.exe einen virtuellen Speicher von etwa 500.000k hat.

Ich hab es auf meinem Laptop (WinXP - SP2, 2 GHz, 512 MB Ram) laufen.

Hier nochmal kurz der Code, wie ich die Datensätze erstelle, der immer aufgerufen wird, wenn das Programm eine mp3-Datei gefunden hat.

Delphi-Quellcode:

  try
    if MSTable_Wrk.Locate('Pfad', FileName, [loCaseInsensitive]) then
    begin
      MSTable_Wrk.Edit;
      Inc(ChangedFiles);
    end
    else
    begin
      MSTable_Wrk.Append;
      Inc(NewFiles);
    end;

    if Assigned(OnTTDProgress) then
      OnTTDProgress(self, NewFiles, ChangedFiles);

    with MSTable_Wrk do
    begin
      FieldValues['Pfad']   := FileName;
      FieldValues['Name']   := StringReplace(ExtractFileName(FileName), ExtractFileExt(FileName), '', []);
      FieldValues['Ordner'] := ExtractFilePath(FileName);

      FTyp := LowerCase(ExtractFileExt(FileName));

      if (Pos('.', FTyp) > 0) then
        FTyp := Copy(FTyp, 2, Length(FTyp) - 1);

      FieldValues['Typ'] := FTyp;

      FGroesse := FormatFloat('0 B', Dateigroesse);

      if (Dateigroesse DIV 1024 > 0) then
        FGroesse := FormatFloat('#,##0.00 KB', Dateigroesse / 1024);

      if (Dateigroesse DIV 1048576 > 0) then
        FGroesse := FormatFloat('#,##0.00 MB', Dateigroesse / 1048576);

      if (Dateigroesse DIV 1073741824 > 0) then
        FGroesse := FormatFloat('#,##0.00 GB', Dateigroesse / 1073741824);

      if ((Dauer MOD 60) > 9) then
        FDauer := IntToStr(Dauer DIV 60) + ':' + IntToStr(Dauer MOD 60)
      else FDauer := IntToStr(Dauer DIV 60) + ':0' + IntToStr(Dauer MOD 60);

      FieldValues['Groesse']           := FGroesse;
      FieldValues['Interpret']         := Artist;
      FieldValues['Titel']             := Titel;
      FieldValues['Album']             := Album;
      FieldValues['Jahr']              := Year;
      FieldValues['Genre']             := Genre;
      FieldValues['Lyrics']            := Lyrics;
      FieldValues['Dauer']             := FDauer;
      FieldValues['Bitrate']           := Bitrate;
      FieldValues['Vbr']               := Vbr;
      FieldValues['ChannelMode']       := ChannelMode;
      FieldValues['Samplerate']        := Samplerate;
      FieldValues['Schreibgeschuetzt'] := FReadOnly;
      FieldValues['Versteckt']         := FHidden;
      FieldValues['Erstelldatum']      := Created;
      JvDBImage1.Picture.Assign(JvID3v21.Images.Pictures.CoverFront);

      Post;
    end; (* of with *)

    JvID3v21.Active := False;
  except
    //
  end;

Gibt es eine Möglichkeit die Suche + Erstellung der Datensätze dauerhaft mit der Geschwindigkeit wie zu Beginn auszuführen?


P.S.: Die MSTable_Wrk-Komponente ist nicht mit einer visuellen Komponente verknüpft.


Vielen Dank und freundliche Grüße
Lorenz

[Union]

Du solltest einen Index auf Deinen Suchbegriff aufbauen ( in dem Fall auf FileName)

[RavenIV]

Das Einfügen in die DB dauert wohl am längsten bei dem Programm, bzw. frisst die meiste Zeit.

Warum benutzt Du nicht eine Liste als Zwischenspeicher und fügst alle Datensätze in einem Rutsch in die DB ein?
Es würde sich ein Record und eine Liste von Records anbieten.

[mschaefer]

Moin, moin,

Du rufst Deine Routine mehrmals auf. Nimm das Post aus der Routine selbst und setzte es hinter Deine aufrufende Schleife.
Damit werden alle Datensätze aufeinmal in die DB geschrieben.

Grüße // Martin

[Bernhard Geyer]

Prepared parametrisierte Abfragen ist das Zauberwort um den Import zu beschleunigen.

[Reinhardtinho]

Hi,

danke euch schon mal für die schnellen Antworten, ich kann es leider erst heute abend testen.

Einen Index werde ich noch auf FileName setzen.

Ist es für den Arbeitsspeicher besser, wenn ich die Daten alle erst am Ende in die DB speichere oder soll ich ca. alle 500 Dateien ein Post absetzen?


MFG
Lorenz

[hoika]

Hallo,

viell. liegt es auch an der Komponente.
Du benutzt eine Table, beim SQL-Server wäre aber eien Query
mit prepared statements besser.

Ausserdem erteugst du mit deinem aktuellen Code
bei jedem Post ein Transaktion.Commit (oder wie Connection.Commit).

Ich würde vor dem ersten Schleife ein StartTransaction
und danach ein Commit absetzen.


Heiko

[tomsson74]

Zitat:

Zitat von Reinhardtinho

Delphi-Quellcode:

    with MSTable_Wrk do
    begin
      FieldValues['Pfad']   := FileName;
      FieldValues['Name']   := StringReplace(ExtractFileName(FileName), ExtractFileExt(FileName), '', []);
      FieldValues['Ordner'] := ExtractFilePath(FileName);
      FieldValues['Groesse']           := FGroesse;
      FieldValues['Interpret']         := Artist;
      FieldValues['Titel']             := Titel;
      FieldValues['Album']             := Album;
      FieldValues['Jahr']              := Year;
      FieldValues['Genre']             := Genre;
      FieldValues['Lyrics']            := Lyrics;
      FieldValues['Dauer']             := FDauer;
      FieldValues['Bitrate']           := Bitrate;
      FieldValues['Vbr']               := Vbr;
      FieldValues['ChannelMode']       := ChannelMode;
      FieldValues['Samplerate']        := Samplerate;
      FieldValues['Schreibgeschuetzt'] := FReadOnly;
      FieldValues['Versteckt']         := FHidden;
      FieldValues['Erstelldatum']      := Created;
      JvDBImage1.Picture.Assign(JvID3v21.Images.Pictures.CoverFront);

Diese Zuweisungen wirken sich auch auf die Performance des Programms aus - obgleich das wahrscheinlin nicht die Hauptursache für Dein Problem ist. Die Eigenschaft FieldValues arbeitet mit dem Datentyp 'Variant' so dass der Datenzugriff im Vergleich zu nativen Datentypen langsamer erfolgt.

Persönlich würde ich daher die andere Schreibweise empfehlen, z.B so ...

Delphi-Quellcode:

      (...)
      FieldByName('Groesse').AsInteger   := FGroesse;
      FieldByName('Interpret').AsString  := Artist;
      FieldByName('Titel').AsString      := Titel;
      (...)
      FieldByName('Jahr').AsInteger      := Year;

usw.

/Thomas

[grenzgaenger]

das mag zwar sein, aber der aufwand ist linear. wenn die zeit der operationen nach oben geschraubt wird, gibt es hierfür zwei möglichkeiten

• dein SQL statement und tabellendefinition ist nicht dafür vorgesehen und somit unperformant
• du hast noch irgendwelche algos und programmcode, der mit der anzahl der datensetze und/oder der laufzeit ein ziemlich krankes verhalten zeigt

im zweiten fall, poste doch mal deine prozeduren/funktionen die beim einlesen mit durchlaufen werden...

[Reinhardtinho]

Hi,

vielen Dank für eure Antworten.

Neben dem oben im ersten Post genannten Code, der immer aufgerufen wird, wenn ich eine Datei gefunden habe, mache ich noch folgendes:

[Edit]Aber die DataSource-Komponente ist mit der MStable1 und nicht mit der Working-Table verknüpft, die ich ja fülle.[/Edit]

Delphi-Quellcode:

procedure TForm_Main.DataSource1DataChange(Sender: TObject; Field: TField);
begin
  Label1.Caption := 'Datensatz: ' + IntToStr(MSTable1.RecNo) + '/' + IntToStr(MSTable1.RecordCount);

  if MSTable1.Active then
  begin
    if (MSTable1.RecordCount > 0) then
    begin
      BitBtn_Datei.Enabled := True;
      Datei2.Enabled       := True;
    end
    else
    begin
      BitBtn_Datei.Enabled := False;
      Datei2.Enabled       := False;
    end;

    BitBtn_Ordner.Enabled := True;
    BitBtn_Suche.Enabled  := True;
    BitBtn_Backup.Enabled := True;
    BitBtn_Import.Enabled := True;
    BitBtn_Export.Enabled := True;

    Ordner1.Enabled        := True;
    Suche1.Enabled         := True;
    Import1.Enabled        := True;
    Export1.Enabled        := True;
    BackupRestore1.Enabled := True;

    ComboBox1.Enabled      := True;
  end
  else
  begin
    BitBtn_Ordner.Enabled := False;
    BitBtn_Suche.Enabled  := False;
    BitBtn_Backup.Enabled := False;
    BitBtn_Import.Enabled := False;
    BitBtn_Export.Enabled := False;

    Ordner1.Enabled        := False;
    Suche1.Enabled         := False;
    Import1.Enabled        := False;
    Export1.Enabled        := False;
    BackupRestore1.Enabled := False;

    ComboBox1.Enabled    := False;
  end;
end;

Ich habe das "Post" nach dem kompletten Suchvorgang erst gesetzt, aber das Phänomen bleibt das selbe. Auch der Arbeitsspeicher wird wieder hoch ausgelastest (Prozess: sqlservr.exe).


Ich habe auch das mit dem .AsString probiert, aber ich erhalte da folgende Fehlermeldung:
"Variante referenziert kein Automatisierungsobjekt. (EOleError).


Es kann ja eigentlich nicht sein, dass bei einer Anzahl von 5000 Datensätzen die Auslagerungsdatei des Sql-Servers bei 300 MB liegt, oder?
Ich denke, daran liegt es auch, dass die Performance so drastisch nachlässt.



MFG
Lorenz

[Union]

Noch etwas zur Performance und Sicherheit: Der Zugriff über persistente (oder zur Laufzeit erstellze) Feldobjekte ist ca. 3x so schnell wie der Zugriff über FieldByName. Und bei persistenten Feldern kann man sich auch nicht mehr vertippen.

[Jelly]

Also der SQL Server ist dafür bekannt, sehr schnell so viel wie möglich Speicher für sich in Anspruch zu nehmen. Das mit den 300MB kann also durchaus sein.

Es muss aber gewusst sein, dass ein Insert in eine Tabelle wo bereits viiiiiile Datensätze vorhanden sind, langsamer ist als in eine leere Tabelle einzufügen. Hier mal einige Tipps, an welchen Ecken man schrauben kann:

• Liegen einige Indizes auf der Tabelle, so kanns z.B. von grossem Vorteil sein, dies erst mal wieder zu entfernen, und nach dem Einfügen der DS wie neu zu erstellen.
• Auch bietet der SQL Server einen BulkInsert Mechanismus an. Such mal in der T-SQL Hilfe nach Bulk Insert.
• Es wird auch schon helfen, anstatt über eine Table Komponente zu gehen direkt den SQL Insert Befehl in einer Query Komponente zu formulieren, und diesen dann mit ExecSQL auszuführen. Das erspart den Overhead lokal im Dataset.
• Du könntest auch ein insert into SQL Befehl nutzen. Warum musst du lokal iterieren, oder ist eine der beiden Tabellen nicht aus einem MSSQL Server

[*]Eventuell ist eine Stored Procedure hilfreich. Du kannst auch intern im SQL Server mit temporären Tabellen arbeiten, die dann nur im Speicher vorliegen. Ein Insert into aus dieser temporären Tabelle sollte wesentlich schneller sein, als im Delphi als zu durchlaufen, und jeden Insert einzeln aufzurufen.[/list]

[stifflersmom]

Wieso editierst Du eigentlich jeden Datensatz immer wieder,
statt nur den Counter für Veränderungen hochzuzählen?

Moin

[Reinhardtinho]

Hi,

@Jelly:
Danke für die zahlreichen Tipps, ich werde sie heute abend oder morgen ausprobieren.

@stifflersmom:
Den Datensatz editiere ich immer, weil sich an den ID3Tags oder an der Größe etwas geändert haben könnte.
Ich beginne mit einer leeren Datenbank und da tritt das Problem ja schon auf, es wird also immer nur Append aufgerufen.


MFG
Lorenz

[RWarnecke]

Mein Vorschlag zu dem Thema wäre, es über ein Query zu machen. Ich erstelle meinetwegen in einer StringList oder in einem Memo alle INSERT-Befehle oder UPDATE-Befehle und gebe dann das Memo oder die StringList in die Query und führe die aus. Sende dann noch ein COMMIT und die Datensätze sind in der Tabelle. Bei meiner Firebird - Datenbank habe ich ca. 7000 Datensätze in 120 Sekunden mit der Methode eingefügt.

Edit:
Ich denke beim SQL Server 2000 funktioniert das genau so schnell.

[mquadrat]

Ich würde das prüfen, ob es den Datensatz schon gibt und das Einfügen bzw. ändern über eine Stored Procedure machen. Dazu sämtliche Aufrufe in einer Transaction. Ein Beispiel findest du hier.

Das muss allerdings dein Geschwindigkeitsproblem nicht lösen. Ich sehe ein weiteres Problem beim RAM. Ist das voll muss der Rechner die ganze Zeit hin und her pagen. Also ggf. einfach mal auf nem Rechner mit mehr RAM ausprobieren.