ADODataset Insert Update

[gro]

Folgendes Problem:

Ich verwende 2 ADOConnections + ADODatasets um von einer Sourcetabelle in eine Targettabelle Daten zu schaufeln.
In der Targettabelle befinden sich aber bereits Daten (gleicher Primarykey) die in der Sourcetabelle bereits vorhanden sind. Wenn nun solch ein Datensatz gefunden wurde sollte auf diesen ein Update gemacht werden.

Meine Lösung:

TargetTable, SourceTable und TargetUpdate sind ADODatasets

Delphi-Quellcode:

TRY
  TargetTable.Insert;
  for iFieldCount := 0 to SourceTable.FieldCount - 1 do begin
    szFieldname := SourceTable.Fields[iFieldCount].FieldName;
    TargetTable.FieldByName(szFieldname).Value := 
           SourceTable.FieldByName(szFieldname).Value;
  end;
  TargetTable.Post; // wenn record vorhanden wird hier eine exception geworfen
EXCEPT
  on e: Exception do begin
    TargetTable.Cancel;

    // alle indexfelder in UPDATESQL befüllen. 
    // Die Stringliste aIndexFields und der SQL-Query wird mit einer eigenen Routine befüllt
    TargetUpdate.Active := FALSE;
    for iCount := 0 to aIndexFields.Count - 1 do begin
      szFieldname := aIndexFields[iCount];
      TargetUpdate.Parameters.ParamByName(szFieldname).Value := SourceTable.FieldByName(szFieldname).Value;
    end;
          
    TargetUpdate.Active := TRUE;
    TargetUpdate.Edit;
    for iFieldCount := 0 to SourceTable.FieldCount - 1 do begin
      szFieldname := SourceTable.Fields[iFieldCount].FieldName;
      TargetUpdate.FieldByName(szFieldname).Value := SourceTable.FieldByName(szFieldname).Value;
    end;
    TargetUpdate.Post;
  end;
END;

Dies funktioniert alles recht schön und fast vernünftig.
ABER die Performanze ist dabei unterm Hund da bei jeden Update ein Select ausgeführt wird.
Vergleich bei 15000 Datensätzen
Nur Insert 95 Secunden
Mit Update 920 Sekunden

Nun meine Frage: Wie kann ich den ADODataset TargetTabel so positionieren, das er nach der Exception des Inserts/Post auf den richtigen Datensatz positioniert ist. BZW. wenn ich das TargetTable.Cancel weglasse, wie kann ich den Datensatz danach trotzdem aktualisieren, denn ohne TargetTable.Cancel ist die TargetTable ja richtig positioniert. Wenn ich jetz mit TargetTable.Edit und anschliesendem TargetTable.Post versuche diesen Record upzudaten bekomme ich eine Fehlermeldung (Duplicat Rows) Was mache ich hier falsch?

Mfg Pit und danke im Voraus

[alzaimar]

Deine Art, die Daten rüber zu schaufeln, ist zwar ADO-Konform, aber eben lahm. Um das schnell(er) zu machen, fallen mir 3 Dinge ein:
1. Transaktionen verwenden
2. SQL-Kommandos selber basteln.
3. Einen Index auf den Primary Key der Target-Tabelle

Zu 2 solltest Du abhängig davon, ob der PK vorhanden ist, oder nicht eine INSERT oder UPDATE Anweisung aufbauen, deren WHERE-Klausel nur die Primary Key Bedingung enthält. Also ungefähr so:

SQL-Code:

UPDATE Target
  SET Field1 = <Value1>, 
      Field2 = <Value2>,
      ....
WHERE IDField = <ID>

bzw.

SQL-Code:

INSERT INTO Target 
  (IDField, Field1, Field2 ....) 
VALUES
  (<ID>, <Value1>, <Value2> ...)

Mit ADO sind die vom Provider generierten SQL-Kommandos alles Andere als optimal. Schau Dir die mal an.

[gro]

Erstmals besten Dank für die rasche Antwort.

Zu 1: Der obige Codeteil ist reduziert auf ein absolutes Minimum. Die einzelnen Inserts/Updates befinden sich innerhalb einer Transaktion. (ADOTargetConnection.BeginTrans und alle 1000 Records ein ADOTargetConnection.CommitTrans) Alle 1000 Records ein Commit ist/war eine Anforderung des Kunden.


Zu 2: Mit dem Dataset "TargetUpdate" wird genau das gemacht. Im Commandtext steht bereits ein SQL wobei sich die WHERE Bedingung aus den Infdexfeldern zusammensetzt.

Dazu kommt noch ein kleines (für mich grosses) Problem: Wenn ich statt eins Datasets einen ADOQuery verwende kann ich in den Blobfelder keine Werte setzen. Sobald ich dieses versuche bekomme ich die Fehlermeldung "Invalid conversation" ???

Dies funktioniert mit BLOB/CLOB Feldern nicht!

Delphi-Quellcode:

  ADODataSet1.First;
  ADOQuery1.SQL.Clear;
  szSql := 'UPDATE schema.table SET ' +
           'FILEDATA = :FILEDATA, DESCRIPTION = :DESCRIPTION ' +
           'WHERE OBJECID = :OBJECTID';

  ADOQuery1.SQL.Add (szSql);
  ADOQuery1.Prepared := TRUE;

  while (ADODataSet1.Eof = FALSE) do begin
    for iCount := 0 to ADODataSet1.FieldCount - 1 do begin
      szField := ADODataSet1.Fields[iCount].FieldName;

      aField := ADOQuery1.Parameters.FindParam(szField);
      if (aField <> nil) then begin
        if (ADODataSet1.Fields[iCount].IsNull = FALSE) then begin
          ADOQuery1.Parameters.ParamByName(szField).Value :=
              ADODataSet1.Fields[iCount].Value;
        end;
      end;
    end;
    ADOQuery1.ExecSQL;
    ADODataSet1.Next;
  end;

Zu 3: Auf allen Tagettabellen sind Indizes vorhanden

-----------------------------------------------------------------------------

Was mir nicht eingeht, ist dass nach einem .INSERT und anschl. .POST die Targettabelle den Cursor auf der richtigen Position stehen hat (und dies auch nach der EXCEPTION DUPLICATE ROWS -803) aber kein Update auf diesen Record danach mehr möglich ist. Wenn ich dieses irgendwie umgehen könnte wäre mein Problem gelöst.

Folgendes könnte ich mir vorstellen. Aber wie?

Delphi-Quellcode:

    // insert versuchen
    aDataSet.Insert;

    // werte eintragen
    // ...

    aDataSet.Post;
  EXCEPT
    on e: exception do begin
      // ??? Was muss man hier machen das das funktioniert???
      aDataSet.Edit;

      // ev. werte nochmals eintragen
      // ...

      aDataSet.Post;
  END;

MfG Pit

[alzaimar]

Hmm....
BLOBS: Mach doch einfach eine Fallunterscheidung: Bei einem Blob verwendest du am besten einen Stream zum kopieren und sonst eben die Variants.

EDIT: Normalerweise sollte es kein Problem sein, bei einem Post-Fehler die Daten nochmals zu editieren, ohne das man irgendwelche Anstrengungen unternehmen muss. Das Dataset ist noch im Edit/Append-Modus, insofern ist der Aufruf vom 2.Edit falsch.
myDataset.Edit;

Delphi-Quellcode:

Try
  myDataSet['Foo'] := Bar;
...
  myDataSet.Post; // <-- peng
Except
  myDataSet.Edit; // <-- Falsch, weil das Dataset noch im Editmodus ist.
  myDataSet['Bar'] := Foo;
  myDataSet.Post;
End;

Versuche doch mal ein myDataSet.Cancel, damit wird der Editmodus verlassen, ohne Änderungen vorzunehmen. Danach kannst Du ja andere Daten reinschreiben...

Was genau steht im TargetUpdate.CommandText drin?