Wöchentlich etliche Datensätze updaten

[Dumpfbacke]

Hallo liebe Delphianer,
ich muß wöchentlich Daten in meiner Datenbank aktualisieren / updaten. Ich bekomme eine CSV Datei mit 6,7 Milionen Datensätze. Aus diesen suche ich ca. 650.000 Datensätze heraus und mache ein Update bzw. Insert.
Das ganze mach ich mitteles eines Delphi 7 Programmes und die IBX Komponeten. Es läuft alles in einer Schleife.

Delphi-Quellcode:

1.)  Prüfen, ob Daten benötigt werden oder nicht. Dadurch werden aus den 6,7 Milionen schon mal "nur" noch 650.00.
2.)  Prüfen, ob Daten schon in der Datenbank vorhanden sind anhand von einen Vergleich von drei Feldern mittels               eines Select wenn ja-> 2.1 wenn nein -> 2.2
2.1) Mittes Update die Daten auf den neuesten Stand bringen und aktuelles Datum in ein Feld schreiben. -> 3
2.2) Mittes Insert die Daten in die Datenbank importieren und aktuelles Datum in ein Feld schreiben. -> 3
3.)  Pürfen auf Datensätze ohne aktuelles Datum und diese dann löschen.

Das ganze mache ich mitteles vier IBQuerys. Vor der Schleife habe ich für die drei Querys natürlich ein Prepare gemacht. Der Commit mache ich am Schluß nach der Schleife. Auf jedem den drei Felder welche für den Vergleich benötigt wird liegt ein eigenen Index.

Nun aber zu meinen Problem. Das ganze funktioniert natürlich dauert jedoch > 10 Stunden. Gibt es hier einen andere Möglichkeit für mich dieses schneller zu erledigen ?

:gruebel: :glaskugel:

Danke Dumpfbacke.

[RWarnecke]

Ich würde das über eine Stored Procedure machen und diese mit den Daten füttern. Die Procedure wird direkt auf dem Server ausgeführt.

[mkinzler]

Ich würde auch an ein Update des DBServers denken ( FireBird >= 2.1) und es dann mit eine

SQL-Code:

update or insert

machen

[Oreaden]

Einen schönen Guten Morgen,

mir scheinen da 10 Stunden nicht zu viel zu sein, Frage mich jedoch wo die Zeit bleibt (Mein Orakel weiss da grad auch nichts). Könntest Du mal die Zeiten messen, welche

a) für die Aufbereitung und Auswahl der Datensätze benötigt wird
b) %tualen Anteil der Inserts und %tualen Anteil der Zeit und die Absolut benötigte Zeit
c) wie (b) nur für die Updates
d) für das Prüfen und Löschen der Datensätze

Denke, dann sehen wir schon etwas klarer wo wir hier am besten ansetzen sollten.

Schöne Grüße
OREADEN

[Dumpfbacke]

Zitat:

Zitat von mkinzler

Ich würde auch an ein Update des DBServers denken ( FireBird >= 2.1) und es dann mit eine

SQL-Code:

update or insert

machen

Ja das hab ich schon mehrfach mir vorgenommen. Leider greifen ca. 30 verschiedenen Programme aud die DB zu und die Left Outer Joins sind nicht alle "sauber". Kann ich eigentlich mit einem Backup und Restore immer zurück zu Interbase 6, wenn es einfach nicht käuft ?

Mit den Stored Procedures werde ich mal testen ob es etwas bringt.

Dumpfbacke

[mkinzler]

Zitat:

Kann ich eigentlich mit einem Backup und Restore immer zurück zu Interbase 6, wenn es einfach nicht käuft ?

Nein, in diesem Fall müsstest du die Datenbank mitsamt Metadaten in ein Skript auslagern

[Dumpfbacke]

Zitat:

Zitat von RWarnecke

Ich würde das über eine Stored Procedure machen und diese mit den Daten füttern. Die Procedure wird direkt auf dem Server ausgeführt.

Das habe ich nun mal veruscht und die Ticks mittels GetTickcont gezählt. Meine alte Routine benötigt zwischen 62 und 78 Ticks. Bei der Stored Procedure benötige ich jedoch 1953 Tick.
Habe ich hier villeicht ein Fehler bei der Stored Produre gemacht oder warum ist die denn so langsam ?


Hier mal die Procedure

Delphi-Quellcode:

CREATE PROCEDURE DatenUpdate (
    Feld1 varchar(20) character set iso8859_1,
    Feld2 varchar(4) character set iso8859_1,
    Feld3 varchar(35) character set iso8859_1,
    Feld4 varchar(20) character set iso8859_1,
    Feld5 varchar(10) character set iso8859_1,
    Feld6 varchar(50) character set iso8859_1,
    Feld7 varchar(30) character set iso8859_1)
as
declare variable maxZaehler integer;
declare variable zaehler integer;
declare variable dsgefunden integer;
begin
  /* Procedure Text */
   for Select Max(Tabelle1Zaehler)
   From Tabelle1
   into :maxZaehler
   do
   DSGefunden = 0;
   Zaehler = 0;
   for Select Tabelle1Zaehler
   From Tabelle1
   Where (Feld1 = :Feld1) and (Feld2 = :Feld2) and (Feld5 = :Feld5)
   into :Zaehler
   do
    begin
     DSGefunden = 1;
    end
   if (DSGefunden = 1) then
    begin
     update Tabelle1
      set
       Tabelle1ZAEHLER = :Zaehler,
       Feld1 = :Feld1,
       Feld2 = :Feld2,
       Feld3 = :Feld3,
       Feld4 = :Feld4,
       Feld5 = :Feld5,
       Feld6 = :Feld6,
       Feld7 = :Feld7,
       AKTUELLESDATUM = current_date
       where Tabelle1ZAEHLER = :Zaehler;
       /*Test = 'update';*/
       Suspend;
    end
   else
    begin
     maxZaehler = maxZaehler +1;
      insert into Tabelle1
       (Tabelle1ZAEHLER, Feld1, Feld2, Feld3, Feld4, Feld5, Feld6,
        Feld7, AKTUELLESDATUM)
      values
       (:maxZaehler, :Feld1, :Feld2, :Feld3, :Feld4, :Feld5,
        :Feld6, :Feld7, current_date);
      /*Test = 'insert';*/
      Suspend;
    end
end^

SET TERM ; ^

[mkinzler]

Ich würde eine SP erzeugen, welche alle Datensätze updatet nicht nur einen

[TBx]

Ist das tatsächlich die SP, mit der Du inserten/updaten wolltest?

Ich hab mal ein wenig formatiert und ein paar Anmerkungen dazugeschrieben.

SQL-Code:

CREATE PROCEDURE DatenUpdate (
  Feld1 varchar(20) character set iso8859_1,
  Feld2 varchar(4)  character set iso8859_1,
  Feld3 varchar(35) character set iso8859_1,
  Feld4 varchar(20) character set iso8859_1,
  Feld5 varchar(10) character set iso8859_1,
  Feld6 varchar(50) character set iso8859_1,
  Feld7 varchar(30) character set iso8859_1)
as
  declare variable maxZaehler integer;
  declare variable zaehler integer;
  declare variable dsgefunden integer;
begin
   for Select Max(Tabelle1Zaehler)
         From Tabelle1
         into :maxZaehler
   do DSGefunden = 0;
   /* for select ist hier unsinnig, es wird immer wenn die Tabelle mindestens
      einen Datensatz enthät der höchste Wert von Tabelle1Zaehler zurückgegeben
      und DSGefunden auf 0 gesetzt. Andernfalls sind beide Werte undefiniert. */

   Zaehler = 0;

   for Select Tabelle1Zaehler
         From Tabelle1
         Where (Feld1 = :Feld1) and (Feld2 = :Feld2) and (Feld5 = :Feld5)
         into :Zaehler
   do
   begin
     DSGefunden = 1;  /* hier wird für jeden gefundenen Datensatz DSGefunden auf 1 gesetzt*/
   end

   if (DSGefunden = 1) then
   begin
     update Tabelle1
       set Tabelle1ZAEHLER = :Zaehler, Feld1 = :Feld1, Feld2 = :Feld2, Feld3 = :Feld3, Feld4 = :Feld4,
           Feld5 = :Feld5, Feld6 = :Feld6, Feld7 = :Feld7, AKTUELLESDATUM = current_date
       where Tabelle1ZAEHLER = :Zaehler;
     /* hier wird nun der letzte gefundene Datensatz aktualisiert */
/*     Suspend  --> raus damit, hat in einer SP ohne Rückgebewert nichts verloren */
   end
   else
   begin /* Wenn DSGefunden != 1 und not null */
     maxZaehler = maxZaehler +1;

     insert into Tabelle1 (Tabelle1ZAEHLER, Feld1, Feld2, Feld3, Feld4, Feld5, Feld6, Feld7, AKTUELLESDATUM)
                   values (:maxZaehler, :Feld1, :Feld2, :Feld3, :Feld4, :Feld5, :Feld6, :Feld7, current_date);  /* neuen Datensatz einfügen */
/*     Suspend  --> raus damit, hat in einer SP ohne Rückgebewert nichts verloren */
   end
end

Hier eine SP, die alle Datensätze, bei denen die Felder 1,2 und 5 übereinstimmen updated und einen neuen Datensatz einfügt, wenn keine Übereinstimmung gefunden wurde.

SQL-Code:

CREATE PROCEDURE DatenUpdate (
  Feld1 varchar(20) character set iso8859_1,
  Feld2 varchar(4)  character set iso8859_1,
  Feld3 varchar(35) character set iso8859_1,
  Feld4 varchar(20) character set iso8859_1,
  Feld5 varchar(10) character set iso8859_1,
  Feld6 varchar(50) character set iso8859_1,
  Feld7 varchar(30) character set iso8859_1)
as
  declare variable anz integer;
  declare variable zaehler integer;
begin
  anz = 0;
  select count (*)
    from Tabelle1
    where (Feld1 = :Feld1) and (Feld2 = :Feld2) and (Feld5 = :Feld5)
    into :anz;
  if (:anz > 0) then
  begin
    update Tabelle1
      set Feld3 = :Feld3, Feld4 = :Feld4, Feld6 = :Feld6, Feld7 = :Feld7, AKTUELLESDATUM = current_date
      where (Feld1 = :Feld1) and (Feld2 = :Feld2) and (Feld5 = :Feld5);
  end
  else
  begin
    Select Max(Tabelle1Zaehler)
      From Tabelle1
      into :Zaehler;
    if (:zaehler is null) then zaehler = 1; else zaehler = :zaehler + 1;
    insert into Tabelle1 (Tabelle1ZAEHLER, Feld1, Feld2, Feld3, Feld4, Feld5,
                          Feld6, Feld7, AKTUELLESDATUM)
                  values (:maxZaehler, :Feld1, :Feld2, :Feld3, :Feld4, :Feld5,
                          :Feld6, :Feld7, current_date);

  end
end

SP ist ungetestet, nur ins Unreine geschrieben.
Anmerkung zur Performance:

• es sollte ein absteigender eindeutiger Index auf Tabelle1Zaehler liegen, eleganter wäre es sicherlich, sich den neuen Zaehler über einen Generator zu ermitteln
• es sollte einen kombinierten Index aus Feld 1, 2 und 5 geben.

Hoffe, das hilft Dir weiter.

[mjustin]

Zitat:

Zitat von Dumpfbacke

Nun aber zu meinen Problem. Das ganze funktioniert natürlich dauert jedoch > 10 Stunden. Gibt es hier einen andere Möglichkeit für mich dieses schneller zu erledigen ?

Noch eine Idee: vorher alle nicht benötigten Indexe deaktivieren, und danach neu aufbauen.

Und den Query Plan mal prüfen (diesen kann man bei den IBX-Query-Komponenten netterweise gleich über eiine Funktion abfragen), ob dort auffällige Joins auftauchen.

[Dumpfbacke]

Zitat:

Zitat von mkinzler

Ich würde eine SP erzeugen, welche alle Datensätze updatet nicht nur einen

Hallo mkinzler,

wie soll ich denn so etwas machen ? Alle Daten liegen in einer CVS Datei. Ich könnte eventuell einen neu CSV Datei erzeugen in der nun noch die benötigten Daten dann drin stehen. Somit habe ich die nicht benötigten Daten schon mal aussortieren. Das sollte vermutlich funktionieren. Nur wie bekomme ich die Daten 650.000 Stück dann in eine SP rein ?
:gruebel:

Dumpfbacke.

[mkinzler]

Man könnte diese direkt vom DB-Server als Dateitabelle (external file) öffnen

[Dumpfbacke]

Zitat:

Zitat von mjustin

Zitat:

Zitat von Dumpfbacke

Nun aber zu meinen Problem. Das ganze funktioniert natürlich dauert jedoch > 10 Stunden. Gibt es hier einen andere Möglichkeit für mich dieses schneller zu erledigen ?

Noch eine Idee: vorher alle nicht benötigten Indexe deaktivieren, und danach neu aufbauen.

Und den Query Plan mal prüfen (diesen kann man bei den IBX-Query-Komponenten netterweise gleich über eiine Funktion abfragen), ob dort auffällige Joins auftauchen.

Hallo mjustin,
es sind keine Joins vorhanden. Es ist einfach nur ein Update oder Insert. Es sind die beiden aus der SP von oben.

Dumpfbacke

[Dumpfbacke]

Zitat:

Zitat von mkinzler

Man könnte diese direkt vom DB-Server als Dateitabelle (external file) öffnen

Dann würde ich ein Firebird > 2.1 benötigen um ein update or insert zu machen oder ?
Ich muß mich endlich mal an die Umstellung auf Firebird 2.x machen.

Dumpfbacke.

[mkinzler]

Das eine hat mit dem anderen nichts zu tun. Aber ein Update würde sich auf jeden Fall lohnen

[Dumpfbacke]

Zitat:

Zitat von mkinzler

Das eine hat mit dem anderen nichts zu tun. Aber ein Update würde sich auf jeden Fall lohnen

Kanst du mir beitte mal beschreiben, wie ich es mit Interbase 6 hinbekommen würde ?

Externe Datei einbinden komme ich ich villeicht noch hin habe hierzu mal ein Beispiel irgendwo gesehen. Nur wie gehts dann weiter bzw. wie kann ich ein update oder insert machen ?

Dumpfbacke

[mkinzler]

Du öffnest diese direkt in der SP und geht in ihr durch die Einträge durch und testest dort auf die Existenz der Zeile

[Dumpfbacke]

Zitat:

Zitat von mkinzler

Du öffnest diese direkt in der SP und geht in ihr durch die Einträge durch und testest dort auf die Existenz der Zeile

Hallo lieber mkinzler,
bitte nicht lachen, da ich blond bin und noch nicht so viel mit SP gemacht habe muß ich noch mal nachfragen. Wie gehe ich die Einträge in einer SP durch ? Mitteles einer for next Schleife ?

Dumpfbacke

[RWarnecke]

Ja, genau so wie Du es auch in Delphi machen würdest.

[mkinzler]

Eine external file ist wie eine Datenbanktabelle

http://fbexport.sourceforge.net/fbexport.html