Massendaten ständig aktualisieren

[Piro]

Moin zusammen,

in einem anderen Thread, den ich eröffnet habe, habe ich gelernt, dass ein INSERT besser und vorallem schneller als ein Append ist.

Jetzt habe ich aber ca. 100.000 Datensätze schon in der Datenbank, welche jeden Tag aktualisiert werden(hinzufügen oder ändern).

Für ein reines Hinzufügen verwende ich ein TADOCommand mit Prepared und Parameter. Geht verdammt schnell. Will ich nicht drauf verzichten.

Meine Situation:
1. Lese Daten aus einer Textdatei (ca. 100.000)
2. Abgleich gegen die bestehenden Daten -> wenn verhanden aktualisieren, wenn nicht hinzufügen

Folgenden Code habe ich bis jetzt.

Delphi-Quellcode:

...
if FileExists(filename) = True then
begin
  AssignFile(F, filename);
  Reset(F);

  SQLCommand_INSERT.CommandText := 'INSERT Into hostliste_data (ktr, znr, motor, snr, quelle, importdatum) ' +
                                                       'VALUES (:p0, :p1, :p2, :p3, :p4, :p5);';
  SQLCommand_INSERT.Prepared;

  while not EoF(F) do
  begin
    Readln(F, zeile);
    sl_zeile := Split(zeile,';', True);

    DSData.Close;
    DSData.CommandText := 'select * from hostliste_data where ktr = '+QuotedStr(sl_zeile[0])+' and ' +
                                                             'znr = '+QuotedStr(sl_zeile[1])+' and ' +
                                                             'snr = '+QuotedStr(sl_zeile[3])+' and ' +
                                                             'quelle = '+ QuotedStr(quelle);
    DSData.Open;
    id  := DSData.FieldByName('id').AsString;

    if id <> '' then
    begin
      SQLCommand_UPDATE.CommandText := 'UPDATE hostliste_data ' +
                                         'SET ktr = '+QuotedStr(sl_zeile[0])+','+
                                             'znr = '+QuotedStr(sl_zeile[1])+', '+
                                         ...
                                       'WHERE id = ' + QuotedStr(id);
      SQLCommand_UPDATE.Prepared; 
      SQLCommand_UPDATE.Execute;   
    end
    else
    begin
      SQLCommand_INSERT.Parameters[0].Value := sl_zeile[0];
      SQLCommand_INSERT.Parameters[1].Value := sl_zeile[1];
      ...
      SQLCommand_INSERT.Execute;
    end;
end;
    ...

Das Update Befehl wird in diesem Fall ja ständig neu geparst im Gegenteil zum Insert. Ich bekomme die Daten für den Abgleich erst aus der Schleife, um die Datei zeilenweise zu lesen.
Kann ich mir eventuell das Query sparen und mit dem DSData (TADODataSet) weiter arbeiten im EditModus?

Für Idee, Vorschläge oder Verbesserungen wäre ich dankbar.
Vielen Dank im Voraus.
Sven

[bepe]

Ich kenne mich weder mit ADO noch mit dem MS SQL besonders gut aus aber...

1. Gibt es keinen Grund das Select und das Update Statement ständig neu zuzuweisen. Sprich: Mach es wie mit dem Insert Statement. Vor der Schleife preparen und in der Schleife nur noch Parameter zuweisen.

2. Kannst du dir das Select und das Update sparen. Kenne mich nicht wirklich aus mit dem MS SQL deshalb kann ich dir nur das Stichwort MERGE anbieten. Mit der Anweisung wird ein vorhandener Datensatz aktualisiert, existiert er nicht wird er angelegt.


Ups.... Sehe gerade "Version 2005"... Merge kam erst mit MS SQL 2008. Aber Punkt eins steht und Punkt zwei kann ich austauschen:

2. Schau mal ob es nicht effektiver wäre eine Stored Procedure zu schreiben. Diese funktioniert im Grunde wie eine Prozedur in Delphi. Der Vorteil wäre das dein Select und die IF Verzweigung innerhalb der DB bzw. auf dem DB-Server ausgeführt wird. Dadurch kannst du einiges an Performance gewinnen weil weniger Daten über das Netz zu dem Client (deinem Programm) übertragen werden muss.

[Bummi]

Wenn Du DTS nicht nutzen kannst oder möchstes zerlege es doch in 5 Schritte.
1. Alles wie gewohnt per Insert einfügen, ABER in eine Hilfstabelle
2. Die Echttabelle aus der Hilftabelle per SQL updaten
3. Daten aus der Hilftabelle per SQL löschen wenn schon in der Echttabelle vorhanden
4. Den Rest per SQL reinschießen
5. Truncate Hilfstabelle

[Piro]

Das mit der Hilfstabelle wäre ein Idee. Muss ich mal drauf rumdenken.

Der Select Befehl ist derzeit erforderlich, da er mir Aufschluss gibt, ob der DS vorhanden ist.

Danke erstmal.

Ich probiere gerade meinen geposteten Code. Mal sehen, ob die Zeit angemessen ist

Gruß, Sven

[Bummi]

Noch ein Vorschlag:
Du schießt alles in eine Stored Prozedure rein
die fährt dann ein "Pseudocode"

Code:

If Exists(Select * from Tabelle where id=@ID)
  Update Tabelle set a=@,b=@b ... where id=@ID
else
  Insert into Tabelle (id,a,b,..) Values (@id,@a,@b..)

[Piro]

@Bummi: Verliere ich dann aber nicht an Geschwindigkeit. Er führt dann jedesmal ein INSERT oder UPDATE aus.

In meinem Code habe ich nur einmal ein INSERT welches dann nur noch mit Parametern arbeitet und das UPDATE wird immer wieder neu erstellt und verwendet.

Wo ist der Vorteil einer Stored Prozedure? Sorry für die Frage.

Mal was anderes kann ich die UPDATE Routine nicht wie die INSERT vor die Schleife mit einer Variablen für die ID legen? Weißm leider nicht wie das gehen soll.

[Bummi]

Er führt ein Insert aus für das was noch nicht drin ist, das soll eh so sein.
Ob das Update schneller ist oder die Überprüfung ob ein Update nötig ist und dann gegf. ein Update durchzuführen müsste man evaluieren, ich denke der Unterschied wird marginal ausfallen.
Der Vorteil liegt bei von mir beschreibenen Möglichkeiten darin daß Du mit dem Minimum an Dattentransfer auskommst, die SQL's innerhalb des Servers, vor allem bei Massenoperationen sind ja um Welten schneller.
Ich persönlich würde die erste Variante vorziehen, hier vielleicht sogar aus der Echttabelle löschen wo die ID in der Hilfstabelle vorhanden ist und dann die komplette Hilfstabelle reinschießen.

Zitat:

Wo ist der Vorteil einer Stored Prozedure?

Das die komplette Datenmanipulation da stattfindet wo die Daten im benötigten Format vorliegen, keine Transfer übers Netz, keine hin und her Wandlung durch Treiberschichten, Delphiklassen etc.

[jobo]

bei solchen Datenmengen (und wahrscheinlich wird es nicht weniger) würde ich es per Linked Server CSV versuchen.

Also Datei auf den Server kopieren, verdrahten (linken) und dann reines SQL oder auch per Stored Proc falls notwendig. Schneller wirst Du es mit keinem Clientprogramm hinbekommen.

hier ist ein link mit handlichen Anweisungen:
http://forums.databasejournal.com/sh...ad.php?t=38583

Weiß aber nicht, ob das lizenztechnisch in jedem ms sql server drin ist.

Gruß, jo

[Piro]

Danke an alle für die großartigen Antworten. Sie haben mir sehr geholfen.

Ich habe derzeit 3.500 Datensätze die ich mit INSERT und UPDATE (mit Parameterübergabe) in DB schiebe.
Das Ganze dauert 20 Sekunden und ist völlig ausreichend.

Vielen Dank nochmal. Super Forum.
Gruß, Sven

[RWarnecke]

Hallo zusammen,

eine reine Verständnisfrage. Wäre für den Abgleich und das Einfügen der Daten in die Datenbank eine Stored Procedure besser geeignet ? Bei der Variante in Beitrag #1 erzeugt doch zu viel Traffic, da immer die Anfragen und das Ergebnis zwischen Datenbankserver und der Anwendung hin und her transpüortiert werden müssen.

Soweit ich es weiß kann der MS SQL-Server doch Stored Procedure.

Edit:
Ich würde für die Update-Anweisung und für die Select-Anweisung kein QuotedStr benutzen sondern Parameter verwenden. Ist aus meiner Sicht etwas übersichtlicher.