Massenupdate quälend langsam

[Ralph Staudte]

Hallo beisammen,

ich muss ab und zu in meiner Applikation Preisinformationen von Lieferanten (Datanorm) einlesen. Typischerweise sind das Datenmengen von ca. 80.000 Datensätzen pro Lieferung.

Bei meiner derzeitigen Lösung dauert das Lesen und Verarbeiten eines einzigen Satzes etwas über 2 Sekunden - pro Datensatz!!! Das sollte doch ein wenig schneller gehen, oder?

Da ich in Sachen SQL noch ein wenig unbedarft bin, hoffe ich (mal wieder) auf einen hilfreichen Tipp...

Wenn ich testweise nur mal die Quelldatei lese und den Inhalt aufbereite, also nichts mit dem SQL Server mache, dann komme ich auf eine Geschwindigkeit von ca. 160 Sätzen pro Sekunde. Das Problem liegt also eher an meiner SQL-Syntax...

<P>
Derzeit gehe ich so vor:

<UL>
<LI>Öffnen der Quelldatei mit einem File-Stream
<LI>Zuweisung der Felder aus der Quelldatei an Variablen
<LI>SQL: Testen, ob der Datensatz bei uns bereits besteht
<LI>SQL: Je nachdem: INSERT oder UPDATE der Quelldaten
</UL>

Blöderweise arbeite ich beim SQL-Datenzugriff mit den Komponenten von Zeos, die hier wohl nicht so bekannt sein dürften... aber im Prinzip handelt es sich dabei auch nur TDataBase und TQuery.

Meine Fragen:
<UL>
<LI>Gibt es einen besseren Weg, zu prüfen, ob der Datensatz bei uns schon exisitiert?
<LI>Wie sieht es mit TransaktionsLeveln aus? Was wäre der Beste für diesen Fall? (Derzeit: RepeatableRead)
<LI>Stichwort AutoCommit (ist bei mir abgeschaltet...)
</UL>

Wenn jemand eine Idee hätte, wäre ich für einen Hinweis sehr dankbar.

Quellcode folgt...

Grüße aus Stuttgart

Ralph

[Ralph Staudte]

Angaben zur Umgebung:

Delphi4 C/S, PostgreSQL

<P>

Hier noch ein wenig Beispiel-Code:

<PRE>
// ***************************************
// Ist dieser Satz schon vorhanden?
// ***************************************
with qyEpsEps do
begin
Close;
with SQL do
begin
Clear;
Add('SELECT text1 FROM epos');
Add('WHERE lieferantnr = :LieferantNr');
Add('AND bestellnr = :BstNummer');
ParamByName('LieferantNr').AsInteger := Lieferant;
ParamByName('BstNummer').AsString := Trim(ArtNummer);
end;
Open;
end;
// ***************************************
// Also: Neuanlage oder Änderung?
// ***************************************
if qyEpsEps.EOF then
begin
// ***************************************
// Neuanlage
// ***************************************
nEntry := RSGetNumber('EPOS'); // fortlaufende Nr.
with qyEpsEps do
begin
Close;
with SQL do
begin
Clear;
Add('INSERT INTO epos (');
Add('entry, lieferantnr, bestellnr, text1, text2, ');
Add('preiseinheit, rabattgruppe, einspieldatum, ');
case nPreisKennz of
1: Add('preisbrutto)');
2: Add('preisnetto)');
3: Add('preiswerk)');
end;
Add('VALUES (');
Add(':Entry, :LieferantNr, :BstNummer, :Text1, :Text2, ');
Add(':PrsEinh, :RabattGr, :Heute, :Preis);');
ParamByName('Entry').AsInteger := nEntry;
usw...
end;
ExecSQL;
end;
end
else
begin
// ***************************************
// Änderung
// ***************************************
with qyEpsEps do
begin
Close;
with SQL do
begin
Clear;
Add('UPDATE epos');
Add('SET text1 = :Text1,');
Add(' text2 = :Text2,');
Add(' preiseinheit = :PrsEinh,');
case nPreisKennz of
1: Add('preisbrutto = :Preis,');
2: Add('preisnetto = :Preis,');
3: Add('preiswerk = :Preis,');
end;
Add(' rabattgruppe = :RabattGr,');
Add(' einspieldatum = :Heute');
Add('WHERE lieferantnr = :LieferantNr');
Add('AND bestellnr = :BstNummer');
ParamByName('Text1').AsString := Trim(Text1);
usw...
end;
ExecSQL;
end;
</PRE&gt

[Ralph Staudte]

Nachtrag: Nachdem ich den Index auf die Tabelle wieder aktiviert habe, dauert die Suche nach einem evtl. vorhandenen Eintrag natürlich wesentlich weniger lange ;-)

Jetzt bin ich bei einer Verarbeitungsgeschwindigkeit von ca. 20 Sätzen pro Sekunde...

Man sollte eben zuerst denken und dann handeln...

Wenn trotzdem jemand noch einen Tuning-Tip für mich hat, würde ich mich natürlich sehr freuen!

Ralp

[Andreas Kosch]

Hallo,

es gibt gleich mehrere problematische Teile bei dem gezeigten Beispiel. Zum einen werden die SQL-Anweisungen jedesmal dynamisch zugewiesen, der SQL-Server muss daher jede Anweisung erneut jedesmal neu vorbereiten (Analysieren, Optimieren, compilieren). Zum anderen würde ich in diesem Fall eine Stored Procedure in der Datenbank einrichten, die mit allen Parametern aufgerufen wird und dort entscheidet, ob die Daten zu aktualisieren oder neu einzufügen sind. In diesem Fall spart man sich die ständigen Ping-Pong-Aufrufe zwischen SQL Server und Client.

Alternativ dazu gibt es noch die Option, alle Daten zuerst in eine Hilfstabelle zu schieben und von dort aus über 2 SQL-Anweisungen die Daten zu aktualisieren:
<pre>
INSERT INTO Tabelle......
SELECT .....FROM Hilfstabelle
WHERE Tabelle.Key NOT IN (SELECT Key FROM Hilfstabelle)

UPDATE Tabelle......
FROM Hilfstabelle
WHERE Hilfstabelle.Key = Tabelle.Key
</pre>
Am Ende wird die Hilfstabelle wieder geleert.
&#10

[Ralph Staudte]

Hallo Herr Kosch,

danke für die Antwort!

Die Idee mit der Stored Procedure werde ich mal im Hinterkopf behalten - da ich mit der derzeitigen Geschwindigkeit im Moment leben kann, ist es nicht ganz so dringend.

Viele Grüße
Ralp

[Ralph Staudte]

Hallo Herr Kosch,

(mal sehen, ob sie diese Nachricht in einem so alten Thread überhaupt bekommen...)

Ich habe mir mittlerweile eine Funktion auf dem Server geschrieben, die mit allen Parametern aufgerufen wird und selbst entscheidet, ob sie den Satz neu anlegen oder eine Änderung bestehender Daten durchführen muss.

Sie hatten aber auch meine dynamische Zuweisung der SQL-Anweisungen bemängelt. Wie kann ich dieses Problem umgehen? Ich kenne keine andere Lösung als Query.SQL.Add()...

Mit freundlichen Grüßen

Ralph Staudt

[Andreas Kosch]

Hallo,

&gt;Wie kann ich dieses Problem umgehen?

indem je SQL eine eigene Komponente vorgesehen und fest über den Objektinspektor konfiguriert wird. Da wir zur Laufzeit beliebig viele Instanzen von Datenmodulen erzeugen/zerstören dürfen, ist das in der Praxis auch handhabbar

[Ralph Staudte]

Hallo Herr Kosch,

danke für die schnelle Antwort!

Meine Stored Procedure (ist eine mit CREATE FUNCTION angelegte Funktion überhaupt eine Stored Procedure?) kann ich nicht über ein TQuery ansprechen - mein Programm stürzt bei der Übergabe der (dynamischen) SQL-Anweisung ab.

Eine FUNCTION mit weinger Parametern konnte ich problemlos über ein TQuery ansprechen, die zu diesem Zweck benutzte Funktion besitzt aber 16 Übergabeparameter... evtl. ist dies das Problem.

Daher versuche ich es jetzt mal mit einer TStoredProc - und dann hat sich das mit dem dynamischen SQL eh erledigt, oder?

Ich melde mich wieder, falls ich nicht weiterkommen sollte.

Mit freundlichen Grüßen

Ralp