Firebird wird beim Append von Datensätze immer langsamer

[Chemiker]

Hallo,

zum Testen meiner Anwendung generiere ich Testdaten und schreibe sie in eine Firebird-Datenbank.
Am Anfang ist das Anfügen der Datensätze auch noch recht schnell gegangen, aber Mitlehrweise ist es doch sehr zähflüssig.

Delphi-Quellcode:

procedure dbEinfuegen;
var
  strText: String;
  dbZahl: Double;
  strSende: String;
  z: Integer;
begin
  For z:=0 to high (Form3.MAArr) do
  begin
    strSende:= ZufallMSmitWert(Form3.MAArr[z].FMAID,
                             Form3.MAArr[z].FMWMinBereich,
                             Form3.MAArr[z].FMWMaxBereich);

    StrText:= copy (strSende,2,5); // Nur die ersten 5 Zeichen übernehmen.StrSende[1,3];
    dbZahl:= strToFloat(copy(strSende,7,Length(strSende)-7));

    if pos('M1',StrText)=0 then
    begin
      Form3.pFIBTransaction2.StartTransaction;
      try
        Form3.PFIBDataSet2.Append;
        Form3.PFIBDataSet2['MW_MSID']:= strText;
        Form3.PFIBDataSet2['MW_MW']:= dbZahl;
        Form3.pFIBDataSet2['MW_DATUMUHRZEIT']:= Form3.DTDatumUhrZeit;
        Form3.pFIBDataSet2.Post;
      except
        Form3.pFIBTransaction2.RollbackRetaining;
      end;
      end else
      begin
      if Form3.i = 70 then
      begin
        Form3.pFIBTransaction2.StartTransaction;
        try
          Form3.PFIBDataSet2.Append;
          Form3.PFIBDataSet2['MW_MSID']:= strText;
          Form3.PFIBDataSet2['MW_MW']:= dbZahl;
          Form3.pFIBDataSet2['MW_DATUMUHRZEIT']:= Form3.DTDatumUhrZeit;
          Form3.pFIBDataSet2.Post;
          Form3.bME:= TRUE;
        except
          Form3.pFIBTransaction2.RollbackRetaining;
        end;
     end;
   end;
 end;
end;

Mit dieser Procedure werden die Datensätze angehängt.

Vielleicht hat einer von euch eine Idee, wie man das Anhängen der Datensätze beschleunigen kann.

Bis bald Chemiker

[Bernhard Geyer]

Definier Zählflüssig und deine Testumgebung? Hast du auch den Virenscanner für deine Firebird-DB-Dateien ausgeschaltet? Wie schaut es mit der Tabellendefinition (Indize, ...) aus?

Und dein Quellcode ist ja schrecklich was die Kapslung betrifft. Du greifst von einer unabhängigen Funktion fest auf eine globale Variable (Forms3) zu. Wenn schon definiere als mindesten die Funktion als Methode von Form3 und lass in der Methode alle Form3-Angaben weg.

[mkinzler]

Nicht ständig neue Transaktionen anstossem bzw. diese auch abschliessen ( nicht nur bei Fehlern)

[Chemiker]

Hallo Bernhard Geyer,

Zitat:

Zitat von Bernhard Geyer

Und dein Quellcode ist ja schrecklich was die Kapslung betrifft. Du greifst von einer unabhängigen Funktion fest auf eine globale Variable (Forms3) zu. Wenn schon definiere als mindesten die Funktion als Methode von Form3 und lass in der Methode alle Form3-Angaben weg.

Da gebe ich Dir vollkommen Recht!!! Habe Teile der Procedure aus der Anwendung schnell zusammen kopiert.

Zitat:

Zitat von Bernhard Geyer

Definier Zählflüssig und deine Testumgebung?

Am Anfang hat das einfügen von ca. 500 Datensätze ca. 1sek, gedauert, jetzt nachdem ca. 0,5 Mio. Datensätze vorhanden sind dauert es ca. 25 sek.

Zitat:

Zitat von Bernhard Geyer

Hast du auch den Virenscanner für deine Firebird-DB-Dateien ausgeschaltet?

Nein, hat der Virenscanner denn mit zunehmenden Anzahl an Datensätze so einen großen Einfluss?

Zitat:

Zitat von Bernhard Geyer

Wie schaut es mit der Tabellendefinition (Indize, ...) aus?

SQL-Code:

ET SQL DIALECT 3;

SET NAMES NONE;

SET CLIENTLIB 'C:\Programme\Firebird\Firebird_2_0\bin\fbclient.dll';



/******************************************************************************/
/****                               Domains                                ****/
/******************************************************************************/

CREATE DOMAIN DATUMZEIT AS
TIMESTAMP;

CREATE DOMAIN MSID AS
VARCHAR(5) CHARACTER SET WIN1252
NOT NULL;

CREATE DOMAIN MSINCID AS
INTEGER
CHECK (VALUE>0);

CREATE DOMAIN MW AS
DOUBLE PRECISION;



/******************************************************************************/
/****                              Generators                              ****/
/******************************************************************************/

CREATE GENERATOR MW_ID_GEN;


/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE MW_TABELLE (
    MW_ID            MSINCID NOT NULL,
    MW_MSID          MSID NOT NULL,
    MW_DATUMUHRZEIT  DATUMZEIT DEFAULT 'NOW' NOT NULL,
    MW_MW            MW NOT NULL
);



/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE MW_TABELLE ADD CONSTRAINT PK_MW_TABELLE PRIMARY KEY (MW_ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX MW_TABELLE_DATUM ON MW_TABELLE (MW_DATUMUHRZEIT);
CREATE INDEX MW_TABELLE_MSID ON MW_TABELLE (MW_MSID);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/


SET TERM ^ ;


/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: SET_MW_ID */
CREATE TRIGGER SET_MW_ID FOR MW_TABELLE
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.mw_id is null) then
  new.mw_id = gen_id(mw_id_gen, 1);
end
^

SET TERM ; ^



/******************************************************************************/
/****                             Descriptions                             ****/
/******************************************************************************/

DESCRIBE DOMAIN DATUMZEIT
'Aktuelles Datum und Uhrzeit';

DESCRIBE DOMAIN MSID
'Messstellen ID besteht aus der 3
 Stelligen MANr und aus der 2 Stelligen 
MSNr.';

DESCRIBE DOMAIN MSINCID
'CHECK (VALUE>0)';

DESCRIBE DOMAIN MW
'Gemessener Messwert';



/* Fields descriptions */

DESCRIBE FIELD MW_ID TABLE MW_TABELLE
'CHECK (VALUE>0)';

Bis bald Chemiker

[Chemiker]

Hallo mkinzler,

Zitat:

Zitat von mkinzler

Nicht ständig neue Transaktionen anstossem bzw. diese auch abschliessen ( nicht nur bei Fehlern)

Und wie müsste das Aussehen?

Bis bald Chemiker

[haentschman]

Gibts bei FibPlus kein "Commit" :gruebel: oder wie läuft das da mit dem Abschluß der Transaktion...

sowas wie Form3.pFIBTransaction2.Commit ... :roll:

prinzipiell:

Delphi-Quellcode:

StartTransaktion;
try
  // alles was datentechnisch zusammen ausgeführt werden muß
  Commit;  // Daten werden "bestätigt" / eingetragen
except
  Rollback;  // Änderungen werden zurückgenommen
end;

[Chemiker]

Hallo Andreas,

ja gibt es. Werde es mal ausprobieren.


Bis bald Chemiker

[haentschman]

Nachtrag:

wie schon oben erwähnt hast du ohne COMMIT mit jedem Datensatz eine offene Transaktion mehr. Das prinzipielle Beispiel kannst du so immer als Block betrachten welcher zusammengehört. Start --> Commit --> bei Fehler Rollback

hoffe geholfen zu haben... :hi:

[Chemiker]

Hallo,

ich habe jetzt folgende Maßnahmen durchgeführt.

1. Die Datenbank wird vom Virenscanner nicht mehr gescannt.
2. Hinter Post ein Commit gesetzt um die Transaktionen abzuschließen.

Ich habe den PC jetzt übernacht(5h) laufen lassen, aber er hat nur ca. 180.000 Datensätze anfügt. Die Geschwindigkeit hat sich nicht signifikant erhöht.

Gibt es noch andere Stellschrauben, um die Geschwindigkeit zu erhöhen?


Bis bald Chemiker

[haentschman]

Guten Morgen..

Zitat:

Ich habe den PC jetzt übernacht(5h) laufen lassen, aber er hat nur ca. 180.000 Datensätze anfügt.

...eine frische Datenbank oder die alte mit den offenen Transaktionen ?

wie wäre es, wenn du das ganze nicht mit Append/Post sondern mit SQL Insert machen würdest ?

:wink:

[Chemiker]

Hallo Andreas,

die alte Datenbank.


Bis bald Chemiker

[haentschman]

Zitat:

die alte Datenbank.

...zum einen würde ich mal mit einer neuen probieren
...zum anderen mal mit SQL Insert arbeiten (dürfte dem Append/Post überlegen sein 8) )

:P

[WoGe]

Hallo
ich habe vor einiger Zeit mal ein ähnliches Problem untersucht
und habe für 1 MIO Datensätze ca 2h gebraucht.

Verwendet habe ich die IBX-Komponenten und die Werte mit einer Query ala Insert into eingetragen.
Die Datenbank war dabei jungfräulich

Grüsse
wo

[haentschman]

...ich bin zwar kein FIB Experte :wink: aber deine SQL Anweisung könnte in etwa so aussehen.

Delphi-Quellcode:

Form3.PFIBQuery.SQL.Text:= 'insert into MW_TABELLE (MW_MSID,MW_MW,MW_DATUMUHRZEIT) values (:ID,:MW,:DATUM)';
Form3.PFIBQuery.ParamByName('ID'):= strText;
Form3.PFIBQuery.ParamByName('MW'):= dbZahl;
Form3.PFIBQuery.ParamByName('DATUM'):= Form3.DTDatumUhrZeit; 
Form3.PFIBQuery.ExecSQL;

[Chemiker]

Hallo,

@ Andreas:

Zitat:

Zitat von Andreas

wie wäre es, wenn du das ganze nicht mit Append/Post sondern mit SQL Insert machen würdest ?

Ein Versuch kann nicht schaden.

Aber mal eine andere Frage, ich lassen noch ein DBGrid mitlaufen, kann das mit zunehmender Anzahl an Datensätze die Datenbank ausbremsen?
Ich benutze es eigentlich nur, um zu sehen ob noch Datensätze angefügt werden.

Zitat:

Zitat von Andreas

...zum einen würde ich mal mit einer neuen probieren

Zum Anfang war das einfügen ja noch schnell, wird aber mit zunehmender Größe der Datenbank, aber immer langsamer.
Wie kann man die offenen Tranksaktionen schließen? Vielleicht reicht das aus.

@WoGe:

Zitat:

Zitat von WoGe

Verwendet habe ich die IBX-Komponenten und die Werte mit einer Query ala Insert into eingetragen.

ich benutze FibPlus die sollten für Firebird eigentlich besser geeignet sein.


Bis bald Chemiker

[mkinzler]

Zudem würde ich überprüfen, ob wirklich jeder Insert in eine eigene Transaktion gekapselt werden muss.

[haentschman]

Zitat:

Zudem würde ich überprüfen, ob wirklich jeder Insert in eine eigene Transaktion gekapselt werden muss.

...stimmt. Wahrscheinlich nicht. :P

...zu spät gelesen :roll:

Zitat:

Aber mal eine andere Frage, ich lassen noch ein DBGrid mitlaufen, kann das mit zunehmender Anzahl an Datensätze die Datenbank ausbremsen?

...ja logisch und bei der Menge erst recht. Das Grid aktualisiert sich quasi kpl. mit jedem neuen Datensatz.
Entweder Grid raus oder die Aktualisierung unterbinden. Da gibt es für Deaktivieren und Aktivieren Befehle.
...ohne Gewähr DisableControls und EnableControls des Grid (kann ich mir nie merken :? )

[TBx]

• ich würde auf gar keinen Fall ein Grid verwenden, ggf. ein DBText oder DBEdit, da kannst Du an der Änderung ja sehen, dass nochwas passiert.
• Was willst Du hier testen? Nur Testdatensätze erstellen für weitere Tests oder das Einfügen direkt testen? Wenn Du nur Testdatensätze generieren möchtest, solltest Du mal schauen, ob Du die Generierung nicht in eine Stored Procedure kapseln kannst. Schneller als direkt auf dem DB-Server wirst Du keine Testdatenerstellung bekommen.
• bloß nicht jeden INSERT commiten. Bau Dir was ein, dass Du z.B. alle 500 INSERTS ein COMMIT machst, dann solltest Du eine annehmbare Geschwindigkeit haben

Hoffe, diese stichwortartigen Bemerkungen helfen Dir weiter

[haentschman]

...und wie ist der Stand ? :roll:

[Chemiker]

Hallo

@Andreas:

Zitat:

Zitat von Andreas

...ohne Gewähr DisableControls und EnableControls des Grid (kann ich mir nie merken

mit Gewähr.

Es ist eine Verbesserung eingetreten, aber noch nicht gut genug. Habe jetzt das ganze DBGrid rausgenommen.

@Tbx:

Zitat:

Zitat von Tbx

ich würde auf gar keinen Fall ein Grid verwenden, ggf. ein DBText oder DBEdit, da kannst Du an der Änderung ja sehen, dass nochwas passiert.

DBGrid ist raus.

Zitat:

Zitat von Tbx

Was willst Du hier testen? Nur Testdatensätze erstellen für weitere Tests oder das Einfügen direkt testen? Wenn Du nur Testdatensätze generieren möchtest, solltest Du mal schauen, ob Du die Generierung nicht in eine Stored Procedure kapseln kannst. Schneller als direkt auf dem DB-Server wirst Du keine Testdatenerstellung bekommen.

Es sind Testdatensätze um die Datenbank mit einer entsprechenden Anzahl Datensätze auszustatten für spätere Abfragen und einfügen von Datensätze.
Mit Stored Procedure weis ich nicht ob das geht. Als Messwerte werten zufällige Zahlen generiert, die sich aber zwischen 2 Grenzen bewegen sollen die usw.

Zitat:

Zitat von Tbx

bloß nicht jeden INSERT commiten. Bau Dir was ein, dass Du z.B. alle 500 INSERTS ein COMMIT machst, dann solltest Du eine annehmbare Geschwindigkeit haben

Ich habe das einfügen jetzt auf Insert umgestellt, die Transaktionskontrolle geht wohl automatisch, bin aber noch dabei das umzustellen. Habe den Timer herausgenommen und durch eine simple For-Schleife ersetzt.
Hier mal jetzt der Quellcode nach der Umstellung:

Delphi-Quellcode:

////////////////////////////////////////////////////////////////////////////////
{
********************************************************************************
Datensätze in die Datenbank einfügen
********************************************************************************
}
procedure dbEinfügenSQL(const aMAArr: TArrMSRec;
                        var apFIBTransaction: TpFIBTransaction;
                        var aPFIBDataSet: TpFIBDataSet;
                        const aDatumUhrzeit: TDateTime;
                        const ai: Integer;
                        var abME: Boolean);
var
  strText: String;
  dbZahl: Double;
  strSende: String;
  z: Integer;
begin
  for z := 0 to high(aMAArr) do
  begin
   strSende:= ZufallMSmitWert(aMAArr[z].FMAID,
                              aMAArr[z].FMWMinBereich,
                              aMAArr[z].FMWMaxBereich);
  StrText:= copy (strSende,2,5); // Nur die ersten 5 Zeichen übernehmen.StrSende[1,3];
  dbZahl:= strToFloat(copy(strSende,7,Length(strSende)-7));

  if pos('M1',StrText)=0 then
  begin
    aPFIBDataSet.Insert;
    aPFIBDataSet['MW_MSID']:= strText;
    aPFIBDataSet['MW_MW']:= dbZahl;
    apFIBDataSet['MW_DATUMUHRZEIT']:= aDatumUhrzeit;
  end else
  begin
    if ai = 70 then
    begin
      aPFIBDataSet.insert;
      aPFIBDataSet['MW_MSID']:= strText;
      aPFIBDataSet['MW_MW']:= dbZahl;
      apFIBDataSet['MW_DATUMUHRZEIT']:= aDatumUhrzeit;
      abME:= TRUE;
    end;
   end;
 end;
end;
////////////////////////////////////////////////////////////////////////////////

Insgesamt haben die Maßnahmen dafür gesorgt, dass ein Datensatz in ca. 1 sek angelegt wird. Ich werde aber versuchen das noch weiter zu verbessern. Das Testen ist nur sehr langwierig, weil man erst nach ca. 30.000 – 40.000 Datensätze feststellt, ob die Änderung was gebracht hat. Was auf jeden Fall stört ist die BDS 2006, weil mit zunehmender Zeit immer mehr virtueller Speicher verbraucht wird, ohne dass man irgendetwas mit ihr macht.

Ich wollte noch ein Backup von der Datenbank-Datei machen, aber bisher ohne Erfolg.
Habe mir eine bat. – Datei geschrieben um ein Backup (mit gbak.exe) von der Datenbank zu bekommen, leider kommt immer eine Fehler-Meldung. Mit IBExpert ist mir auch noch kein Backup gelungen, weil immer eine DLL-Datei, oder der Server-Name fehlt.

Zitat:

Zitat von Andreas

...und wie ist der Stand ?

Soweit ein Zwischenbericht, wie geschrieben ich werde noch weitere Optimierungen ausprobieren.
Bis bald Chemiker

[mkinzler]

Zitat:

Insgesamt haben die Maßnahmen dafür gesorgt, dass ein Datensatz in ca. 1 sek angelegt wird.

Was ja noch nicht berauschend ist.

Zitat:

Was auf jeden Fall stört ist die BDS 2006, weil mit zunehmender Zeit immer mehr virtueller Speicher verbraucht wird, ohne dass man irgendetwas mit ihr macht.

Ist auch nicht normal
Ich würde aber auf jeden Fall ein parametrisiertes Insert-Statement verwenden

[haentschman]

Zitat:

Was ja noch nicht berauschend ist.

...ist eine Katastrophe :roll:

Zitat:

Ich würde aber auf jeden Fall ein parametrisiertes Insert-Statement verwenden

...ist zwingend notwendig ! :roll:

:hi:

[Chemiker]

Hallo,

ich habe mich wohl etwas verrechnet, bei der Ausgabe von Gettickcount, man sollte msec in sek schon richtig umrechnen.
Zurzeit legt das Programm ca. 100 Datensätze in der Sekunde an. Und die CPU-Zeit liegt bei ca. 40-60% nur, sie lag vorher bei 90-100%.

Bis bald Chemiker

[Chemiker]

Hallo,

der Rechner ist in der Nacht durchgelaufen und es sind ca. 730.000 Datensätze eingefügt worden, zu Anfang sind 180 Datensätze in der Sek. eingefügt worden. Diese Zahl ging aber kontinuierlich runter, um schließlich bei 730.000 Datensätzen im Durchschnitt bei ca. 23 Datensätzen pro sek. zu landen.

Bis bald Chemiker

[mkinzler]

Wie gesagt, verwende explizite Transaktionskontrolle, fasse einige Inserts zusammen und verwende ein parametrisiertes Insert-Statement.

[globetrotter77]

Der DBGrid ist ein gnadenloser Bremser!
Habe ich selbst schon erlebt.
Ist ja auch klar, wenn man sich vorstellt, dass nach jedem neu eingefügten oder angehängten Datensatz die komplette Darstellung neu erfolgen muss.
Seitdem stelle ich bei solchen Aktionen immer um auf

Delphi-Quellcode:

DBGrid.Visible:=true;

und nach Ende der Schleife wieder auf

Delphi-Quellcode:

DBGrid.Visible:=false;

Um beobachten zu können,ob sich noch was tut, kannst Du beispielsweise im StatusBar die aktuelle Anzahl der Datensätze anzeigen.

Delphi-Quellcode:

StatusBar.Panels[0].Text:=IntToStr(DataSetXY.RecordCount);

die Anzeige evtl. auch nur alle 100 oder 1000 Datensätze aktualisieren:

Delphi-Quellcode:

if RecordCount mod 100 = 0 then
  ...

Probier's mal aus!

PS: die Sache mit den noch nicht abgeschlossenen Transaktionen stimmt natürlich trotzdem!

[edit=mkinzler]Delphi-Tags eingefügt Mfg, mkinzler[/edit]

[globetrotter77]

Zitat:

Zitat von Chemiker

Delphi-Quellcode:

  StrText:= copy (strSende,2,5); // Nur die ersten 5 Zeichen übernehmen.StrSende[1,3];
  dbZahl:= strToFloat(copy(strSende,7,Length(strSende)-7));

Bevor du dir Schrottdaten generierst, solltest du die beiden Zeilen auch noch mal überarbeiten!
Im Augenblick enthält StrText die Stellen 2-6 und bei dbZahl fehlt die letzte Stelle von StrSende

[Chemiker]

Hallo globetrotter77,

das DBGrid ist raus. Und ob es ausreicht DBGrid.Visible auf FALSE zu stellen bezweifle ich. Dann schon besser wie haentschman geschrieben hat, die Controls mit pFIBDataSet.DisableControls auf FALSE stellen.

Um die Datensätze zu Zählen habe ich sowieso einen Zähler der bei jedem Anfügen um 1 erhöht wird.

Zitat:

Zitat von globetrotter77

Bevor du dir Schrottdaten generierst, solltest du die beiden Zeilen auch noch mal überarbeiten!
Im Augenblick enthält StrText die Stellen 2-6 und bei dbZahl fehlt die letzte Stelle von StrSende

Das sind keine Schrottdaten, weil der String von einem SPS geliefert wird und folgende Form hat.

#2 K01T1 Zahl als String #3 , wobei #2 STX ist und #3 ETX ist, diese beiden Zeichen die den Anfang und das Ende des Übertragungsstrings bilden werden für das Speichern in die Datenbank nicht gebraucht.


Bis bald Chemiker

[globetrotter77]

Zitat:

Das sind keine Schrottdaten

Ich meinte das auch nicht negativ, sondern mir ist aufgefallen, dass du was anderes rausholst als du im Kommentar erwähnst. Und dann werden aus den schönen Daten ganz leicht Schrottdaten.
Aber ich dachte mir schon, dass es auch was mit Steuerzeichen zu tun haben könnte.

Bei mir hat das Umstellen auf Visible=false enorm was gebracht.
Bei 100000 Datensätzen kann man noch ganz gut zuschauen.
Noch besser ist es natürlich für so ne Sonderaktion das Grid gleich gar nicht erst einzubauen.

[globetrotter77]

habe nochmal nachgeschaut und siehe da, ich habe mich getäuscht:
ich stelle nicht Visible um, sondern DataSource, damit ist es überhaupt nicht mehr betroffen.

DBGrid1.DataSource:=NIL;

und danach wieder

DBGrid1.DataSource:=DataSource1;

und zur Kontrolle, was passiert:

procedure TForm1.DataSource1DataChange(Sender: TObject; Field: TField);
begin
with Table1 do
StatusBar1.Panels[0].Text:=
IntToStr(RecNo)+'/'+IntToStr(RecordCount);
Application.ProcessMessages;
end;