Update extrem langsam

[Uwe Merckens]

Hallo Gemeinde,

eine Tabelle beinhaltet ~ 1.8 Mio Datensätze. Die werden in ein List-Objekt eingelesen. Ein Feld dieses Objektes berechnet einen Wert aus drei Feldern der Abfrage (bis hierhin alles sehr performant). Dieser neue Wert soll nun in ein leeres Feld der Tabelle eingetragen werden.
Dazu habe ich eine SP geschrieben in der Form:

UPDATE Wetterdaten SET Wetterdaten.DRUCK_MEERESHOEHE = :Druck WHERE Wetterdaten.ID = :Ident

Weiter im Programm:

  if not taDWD.InTransaction then
    taDWD.StartTransaction;
  for i := 0 to PDruckliste.Count - 1 do
    begin
      spUpDate.Params[0].AsFloat:= PDruckListe^[i].Druck;
      spUpdate.Params[1].AsInteger:= PDruckListe^[i].Index;
      spUpDate.ExecProc;
      if id > 999 then
        begin
          taDWD.Commit;
          id:= 0;
          taDWD.StartTransaction;
        end;
      inc(id);
    end;

Das ganze geht unendlich langsam. Es vergehen 2, 3 Sekunden für ein 'UPDATE' eines einzelnen Datensatzes.

Umgebung:
D2007 mit IBX
FB Classic
Win 2008 64 (Intel i7, 16 GB RAM, WD-Raptor, 100 MBit Netz)

Jemand ne Idee, wie man das schneller gestalten könnte?

Danke fürs mitbrainen.

Uwe

[defo]

Das Spannende wäre das Statement zu sehen, das in der SPUpdate läuft.
Und dazu "for i := 0 to PDruckliste.Count"
Ist PDruckliste die Liste mit 1,8 Millionen Einträgen?
Laufen also hier 1,8 Millionen Aufrufe der SPUpdate durch?
Wenn ein SPAufruf 3 Sekunden dauert: Sind die Where Kriterien des Update auf indizierte Felder gesetzt?
Ein einzelnes Update sollte dann schneller laufen, zumindest wenn der Index auch verwendet wird und einer bis eine Handvoll DS betroffen sind.
Grundsätzlich sehe ich keinen großen Sinn darin, 1,8 Mio Datensätze auf den Client zu ziehen und dort durchzuackern, um wiederum auf dem Server 1,8 Mio Updates zu fahren.
Außerdem ist fraglich, ob das Datenmodell ok ist, falls dieser Vorgang zum Tagesgeschäft gehört. Worum geht's da? DWH?

[Uwe Merckens]

Das Statement steht doch ganz oben :-)
Die 'WHERE'-Clause betrifft den PK.

Zwei Lösungsmöglichkeiten habe ich jetzt:
1. eine UDF. Diese berechnet den gewünschten Wert. Problem sind hier wiederum zweierlei: es stehen nicht in jedem DS die benötigten Parameter, das müsste man in der dll abfangen und vllt. '0' ausgeben. Ist aber nicht optimal. Das zweite ist, dass die UDF nicht funktioniert. Lokal auf einer 32 bit Maschine bricht die Connection ab. Hat sicher was mit der Speicherzuweisung zu tun. Da muß ich noch mal bei. Auf dem Server läuft die 32 bit dll natürlich nicht.

2. neue Tabelle angelegt. Felder ID und DRUCK. Aus der Liste habe ich die beiden Werte in die neue Tabelle gepumpt (ging rucki zucki) und frage die Daten per View ab. Das geht gut.

Dennoch bleibt die grundsätzliche Frage, warum ein Bulk Update so quälend langsam läuft. Stimmt es, dass beim UPDATE der DS gelöscht wird und ein neuer mit den aktualisierten Werten als INSERT eingefügt wird.?

Uwe

[defo]

Das Statement steht doch ganz oben :-)
Die 'WHERE'-Clause betrifft den PK.

Zwei Lösungsmöglichkeiten habe ich jetzt:
1. eine UDF. Diese berechnet den gewünschten Wert. Problem sind hier wiederum zweierlei: es stehen nicht in jedem DS die benötigten Parameter, das müsste man in der dll abfangen und vllt. '0' ausgeben. Ist aber nicht optimal. Das zweite ist, dass die UDF nicht funktioniert. Lokal auf einer 32 bit Maschine bricht die Connection ab. Hat sicher was mit der Speicherzuweisung zu tun. Da muß ich noch mal bei. Auf dem Server läuft die 32 bit dll natürlich nicht.

2. neue Tabelle angelegt. Felder ID und DRUCK. Aus der Liste habe ich die beiden Werte in die neue Tabelle gepumpt (ging rucki zucki) und frage die Daten per View ab. Das geht gut.

Dennoch bleibt die grundsätzliche Frage, warum ein Bulk Update so quälend langsam läuft. Stimmt es, dass beim UPDATE der DS gelöscht wird und ein neuer mit den aktualisierten Werten als INSERT eingefügt wird.?

Uwe

Sorry, das hab ich falsch verstanden.
Zu der UDF kann ich nichts sagen.
Die neue Tabelle soll dann ein Puffer für die Updates sein oder dauerhaft?
Beim nächsten Update dann wieder ne neue Tabelle?

Ja, angeblich macht FB ein Insert statt ein Update. Kenne ich aber nicht sehr gut.

Was ist denn mit der Indizierung? Ein Update, selbst wenn es durch Delete/insert ersetzt wird, braucht keine 2-3 Sekunden.
Wenn Du eh eine SP für das einzelne Update verwendest, wieso nicht gleich den gesamten Updatevorgang in der SP?

[Uwe Merckens]

Die neue Tabelle soll dann ein Puffer für die Updates sein oder dauerhaft?
Beim nächsten Update dann wieder ne neue Tabelle?

Die neue Tabelle soll bis eine bessere Lösung gefunden ist, erst mal dauerhaft bleiben und wird entsprechend erweitert.

Ja, angeblich macht FB ein Insert statt ein Update. Kenne ich aber nicht sehr gut.

Dennoch verstehe ich nicht, warum das so langsam geht. Das INSERT in die neue Tabelle hat bei 1,6 Mio DS irgendwas mit 5 Minuten gedauert. Meine irgendwo gelesen zu haben, dass das Problem im Erstellen der Log-datei liegt. Habe es aber noch nicht prüfen können. Vllt. kann man das ja unterbinden.

Was ist denn mit der Indizierung? Ein Update, selbst wenn es durch Delete/insert ersetzt wird, braucht keine 2-3 Sekunden.

Nochmal: die WHERE-Clause bezieht sich auf den PK (ein Integer-Wert). 2-3 Sekunden sind vllt. etwas übertrieben. Aber wenn man den UPDATE-Vorgang im Debugger verfolgt, vergeht spürbar Zeit. Kann sein, dass es nur eine Sekunde ist - muss ich mal messen.

Wenn Du eh eine SP für das einzelne Update verwendest, wieso nicht gleich den gesamten Updatevorgang in der SP?

Weil es unterschiedliche Werte sind. Es handelt sich um langjährige Klimadaten aus ganz Europa. Alleine die von D beinhalten die 1.8 Mio DS. Es geht um den Luftdruck, der immer für die Stationshöhe angegeben wird. Das kommt daher, dass bis zur Einführung elektronischer Messstationen keine ad hoc-Umrechnung auf Meereshöhe durchgeführt werden konnte. Um flächigen Bodendruck zu ermitteln, brauche ich aber den Druck, wie er auf Meereshöhe sein würde (Stichwort barmetrische Höhenformel).

Dabei handelt es bei diesen Daten um den 24-stündigen Mittelwert. Das gleiche steht mir noch für die stündlichen Werte bevor, die z.T. bis 1950 zurückreichen. Und dann habe ich erst die für D. Danach sollen die für Europa drankommen.

Ist vllt. die Einrichtung einer RAM-Disk auf dem Server und die Auslagerung der DB darauf eine Option?

Uwe

[Ralf Jansen]

Stimmt es, dass beim UPDATE der DS gelöscht wird und ein neuer mit den aktualisierten Werten als INSERT eingefügt wird.?

Insert, Update sind logische SQL Befehle Datenbank intern haben die aber kaum eine genaue Entsprechung.
Ein Snapshot/Multiversions- oder wie auch immer genanntes System einer Datenbank hält potentiel mehrere Versionen eines Datensatzes vor. Denn solange du obigen Update nicht commited hast wollen andere Leser ja die Originaldaten sehen(keine Dirty Reads!). Oder je nach Isolation Level sogar genau den Zustand zu Begin der Lesetransaktion auch wenn der Datensatz zwischenzeitlich schon anders commited wurde. Das geht am einfachsten (und vermutlich für Leser am schnellsten) mit multiplen Versionen einer Row.

So wie dann Transaktionen abgeschlossen werden löscht die DB irgendwann mal alte Versionen der Rows wenn garantiert keine laufende Transaktion die noch interessant finden könnte.

[defo]

Nochmal: die WHERE-Clause bezieht sich auf den PK (ein Integer-Wert). 2-3 Sekunden sind vllt. etwas übertrieben. Aber wenn man den UPDATE-Vorgang im Debugger verfolgt, vergeht spürbar Zeit. Kann sein, dass es nur eine Sekunde ist - muss ich mal messen.

Ok, ich bin glaub ich etwas begriffsstutzig heute. Oben hast Du von "neuem Wert" in "leeres Feld " geschrieben. Ergebnis langsam.
Später hast Du von neuer Tabelle mit 2Feldern geschrieben. Ergebnis schnell.

Wurde das "leere Feld" nachträglich angelegt (alter table add mynewcolumn..)?
Wenn ja, könnte das die Ursache sein. Die Interna von Firebird sind mir nicht bekannt-ich kenne sowas ähnliches von Oracle-, aber es ist einfach gesagt denkbar, das die neue Spalte nie dort liegt, wo sie per PK zunächst gesucht wird. Da steht dann nur der Verweis auf eine neue Location für die neue Spalte. Die Festplatte fährt also Achterbahn.
Dieses Problem kann man mit Reorganisation der Tabellenstruktur beheben. Weiß aber nicht, was FB da alles kann.
Zu Fuß ungefähr so :
Create Table NewWetterdaten <mit entgültiger Struktur>
insert into NewWetterdaten ()
Select * from Wetterdaten

[Thomas Steinmaurer]

Hallo,

solltest du Firebird 2.5 verwenden, dann würde ich an deiner Stelle mal die Trace API anwerfen und für einen Bruchteil, z.b. 1000 Datensätze (damit das erste Commit mal greift), das Ganze verfolgen.

Btw, ist auf Wetterdaten.DRUCK_MEERESHOEHE auch ein Index?

LG