SQL "Update or Insert" langsam

[BlueStarHH]

Hallo,

ich führe ca. 2000 "Update or Insert"-Statements mit einem TIBCQuery aus. Das dauert ca. 2,5 Sekunden und ist zu langsam. Ist das normal, dass das so lange dauert oder kann man das beschleuningen? (Zum Vergleich: Wenn ich die selben Daten ohne Datenbank in eine Ini-Datei schreibe ist das in 0,5 Sekunden erledigt.)

Code:

Transaction.StartTransaction;

for i := 0 to 2000 do
begin
  Query.SQL.Text := 'Update or insert into foo (A, B, C) values (:a, :b, :c)';
  Query.ParamByName('a').AsString := 'v1';
  Query.ParamByName('b').AsString := 'v2';
  Query.ParamByName('c').AsString := 'v3';
  Query.Execute;
end;

Transaction.Commit;

[DeddyH]

Wieso setzt Du innerhalb der Schleife jedesmal die SQL-Eigenschaft neu? Und fehlt da nicht ein MATCHING in Deinem Statement?

[SProske]

Zitat:

Zitat von DeddyH (Beitrag 1357737)

Und fehlt da nicht ein MATCHING in Deinem Statement?

Nicht zwangsläufig: In the absence of a MATCHING clause, matching is done against the primary key.

Siehe https://firebirdsql.org/refdocs/lang...or-insert.html

[BlueStarHH]

Zitat:

Zitat von DeddyH (Beitrag 1357737)

Wieso setzt Du innerhalb der Schleife jedesmal die SQL-Eigenschaft neu? Und fehlt da nicht ein MATCHING in Deinem Statement?

Pseudocode ;-) Die Schleife und der Inhalt sind im echten Code ganz weit von einander weg und entkoppelt, dass es nur so geht. Ich kann nur den Inhalt in der Schleife kontrollieren. Ob da nun eine String-Eigenschaft (SQL.Text) mehrfach gesetzt wird oder nicht sollte von der Geschwindgkeit unter den Tisch fallen...

[dataspider]

Durch die dauernde Zuweisung des Statemants wird die Komponente jedes mal ein Unprepare schicken.
Dadurch geht der Vorteil der Parameter natürlich flöten...

Frank

[jobo]

Ggf im SQL das Matching oder andere "Hilfsmittel" ergänzen. Auch wenn es dann doppeltgemoppelt ist, könnte es dem Optimizer eine Hilfestellung sein.
Ohne weiteres wirkt ja ein Update erstmal auf den gesamten Datenbestand, ist also eine fette Nummer.
Wirklich flott ist wahrscheinlich eine komplette Umstellung auf:
Step1:
Insert into <table> (<fields>)
select <values> from table
where not exists .. (bzw. in richtig mit self join und is null Prüfung)
Step2:
Updates mit ähnlichem Vorgehen.

Wäre die Frage, ob es ständig geschieht und nervt oder die bequeme Formulierung oben eine einmalige Sache ist und mit ein paar Tränchen verdrücken zu bewältigen ist.

[mkinzler]

Zitat:

Zitat von BlueStarHH (Beitrag 1357739)

Zitat:

Zitat von DeddyH (Beitrag 1357737)

Wieso setzt Du innerhalb der Schleife jedesmal die SQL-Eigenschaft neu? Und fehlt da nicht ein MATCHING in Deinem Statement?

Pseudocode ;-) Die Schleife und der Inhalt sind im echten Code ganz weit von einander weg und entkoppelt, dass es nur so geht. Ich kann nur den Inhalt in der Schleife kontrollieren. Ob da nun eine String-Eigenschaft (SQL.Text) mehrfach gesetzt wird oder nicht sollte von der Geschwindgkeit unter den Tisch fallen...

Nein. Absolut nicht, weil man dann den Vorteil der Parameter nicht hat

Delphi-Quellcode:

Transaction.StartTransaction;

Query.SQL.Text := 'Update or insert into foo (A, B, C) values (:a, :b, :c)';
for i := 0 to 2000 do
begin
  Query.ParamByName('a').AsString := 'v1';
  Query.ParamByName('b').AsString := 'v2';
  Query.ParamByName('c').AsString := 'v3';
  Query.Execute;
end;

Transaction.Commit;

[Mikkey]

Zitat:

Zitat von mkinzler (Beitrag 13577499)

Delphi-Quellcode:

Transaction.StartTransaction;

Query.SQL.Text := 'Update or insert into foo (A, B, C) values (:a, :b, :c)';
for i := 0 to 2000 do
begin
  Query.ParamByName('a').AsString := 'v1';
  Query.ParamByName('b').AsString := 'v2';
  Query.ParamByName('c').AsString := 'v3';
  Query.Execute;
end;

Transaction.Commit;

Noch ein Vorschlag zur Beschleunigung:

Delphi-Quellcode:

Transaction.StartTransaction;

Query.SQL.Text := 'Update or insert into foo (A, B, C) values (:a, :b, :c)';
ParamA = Query.ParamByName('a');
ParamB = Query.ParamByName('b');
ParamC = Query.ParamByName('c');
for i := 0 to 2000 do
begin
  ParamA.AsString := 'v1';
  ParamB.AsString := 'v2';
  ParamC.AsString := 'v3';
  Query.Execute;
end;

Transaction.Commit;

Erspart auch noch dreimal pro Runde das Auswerten von ParamByName.

[user0815]

Delphi-Quellcode:

Transaction.StartTransaction;

Query.SQL.Text := 'Update or insert into foo (A, B, C) values (:a, :b, :c)';

Query.Params[0].DataType := ftString;
Query.Params[1].DataType := ftString;
Query.Params[2].DataType := ftString;

for i := 0 to 2000 do
begin
  Query.Params[0].Value := 'v1';
  Query.Params[1].Value := 'v2';
  Query.Params[2].Value := 'v3';
  Query.Execute;
end;

Transaction.Commit;

geht es nicht auch so?
Erspart das Auswerten & Überprüfen des Typs sowie das Suchen der Parameter Position.

[hoika]

Hallo,
bei IBDAC muss man explizit preparen, um Parameter ausnutzen zu können

Delphi-Quellcode:

Transaction.StartTransaction;

Query.SQL.Text := 'Update or insert into foo (A, B, C) values (:a, :b, :c)';

// das fehlte
Query.Prepare;

for i := 0 to 2000 do
begin
  Query.ParamByName('a').AsString := 'v1';
  Query.ParamByName('b').AsString := 'v2';
  Query.ParamByName('c').AsString := 'v3';
  Query.Execute;
end;

Transaction.Commit;

Und dem Link von SProske zufolge spielt auch der Primary Key eine Rolle,
wie sieht denn die Tabellenstruktur aus?

[BlueStarHH]

Zitat:

Zitat von hoika (Beitrag 1357820)

und dem link von sproske zufolge spielt auch der primary key eine rolle,
wie sieht denn die tabellenstruktur aus?

A VarChar(50) PK
B VarChar(50) PK
C Blob Text 80

Muss ich das SQL also anpassen?

[hoika]

Hallo,
das heisst also, dass der PK aus 2 Feldern (A,B) besteht,
dann ist das ja OK.

Wies sieht es mit "meinem" Prepare aus?

[Slipstream]

Kann man nicht einfach ein paar hundert oder sogar tausend Update-Insert-Anweisungen in einem Rutsch sammeln und die dann auf einmal executen? Wenn ich so grosse Mengen auf einmal aktualisieren und/oder einfügen muss, geht das immer viel schneller. Man kann doch in die SQL-Stringlisten einen Haufen Zeugs reinpacken, eigentlich bis der Speicher voll ist. Was da so aufhält, ist bestimmt das Execute 2000 Mal ausführen. Eigentlich sollte es auch reichen, das nur einmal auszuführen, 2000 Datensätze ist jetzt nicht die Welt.

Delphi-Quellcode:

Transaction.StartTransaction;

for i := 0 to 2000 do
begin
  Query.SQL[i] := 'Update or insert into foo (A, B, C) values (:a, :b, :c)';
  Query.ParamByName('a').AsString := 'v1';
  Query.ParamByName('b').AsString := 'v2';
  Query.ParamByName('c').AsString := 'v3';
end;

Query.Execute;

Transaction.Commit;

[hoika]

Hallo,
FB kann kein bulk insert.
Hier könnte man sich mit external tables behelfen.
Aber ich wäre ja noch auf eine Antwort zu meinem prepare.

[p80286]

Zitat:

Zitat von BlueStarHH (Beitrag 1357828)

A VarChar(50) PK
B VarChar(50) PK
C Blob Text 80

Muss ich das SQL also anpassen?

Zwei Primary Keys? und dann augenscheinlich Text?
Da bin ich auf den ersten Ärger mit Umlauten und/oder Codepages gespannt.

Gruß
K-H

[DeddyH]

2 PKs geht nicht, aber ein zusammengesetzter über 2 Felder schon ;)

[jobo]

Zitat:

Zitat von p80286 (Beitrag 1357872)

Zitat:

Zitat von BlueStarHH (Beitrag 1357828)

A VarChar(50) PK
B VarChar(50) PK
C Blob Text 80

Zwei Primary Keys? und dann augenscheinlich Text?
Da bin ich auf den ersten Ärger mit Umlauten und/oder Codepages gespannt.

Zitat:

Zitat von DeddyH (Beitrag 1357873)

2 PKs geht nicht, aber ein zusammengesetzter über 2 Felder schon ;)

Ja, ein Schlüssel kann aus mehreren Feldern bestehen.
Am interessantesten finde ich aber ein BLOB Text(80), der in das "Upsert" involviert ist.
Wie sieht es da mit Indizierung aus? Und wenn vorhanden, greift sie?

Wenn nicht, was ich mir ganz gut vorstellen kann, bedeutet jedes einzelne "Upsert" ein Fullscan. Das dann in einer Schleife und die Performance ist im A..

[user0815]

Zugriff erfolgt über: IBDAC
kurz mal geguckt: https://www.devart.com/ibdac/docs/?batchops.htm

Batch INSERT operation sample
Let’s try to insert 1000 rows to the BATCH_TEST table using a Batch Insert operation:

Delphi-Quellcode:

var
  i: Integer;
begin
  // describe the SQL query
  IBCQuery1.SQL.Text := 'INSERT INTO BATCH_TEST VALUES (:ID, :F_INTEGER, :F_FLOAT, :F_STRING, :F_DATE)';

  // define the parameter types passed to the query :
  IBCQuery1.Params[0].DataType := ftInteger;
  IBCQuery1.Params[1].DataType := ftInteger;
  IBCQuery1.Params[2].DataType := ftFloat;
  IBCQuery1.Params[3].DataType := ftString;
  IBCQuery1.Params[4].DataType := ftDateTime;

  // specify the array dimension:
  IBCQuery1.Params.ValueCount := 1000;

  // populate the array with parameter values:
  for i := 0 to IBCQuery1.Params.ValueCount - 1 do 
  begin
    IBCQuery1.Params[0][i].AsInteger := i + 1;
    IBCQuery1.Params[1][i].AsInteger := i + 2000 + 1;
    IBCQuery1.Params[2][i].AsFloat := (i + 1) / 12;
    IBCQuery1.Params[3][i].AsString := 'Values ' + IntToStr(i + 1);
    IBCQuery1.Params[4][i].AsDateTime := Now;
  end;

  // insert 1000 rows into the BATCH_TEST table
  IBCQuery1.Execute(1000);
end;

[bnreimer42]

Da kapselt dann aber IBDAC die Batch-Operation.
Das gibt es bei Firebird keine direkte Unterstützung in der API.

Gruß

Björn

[jobo]

Zitat:

Zitat von bnreimer42 (Beitrag 1357944)

Da kapselt dann aber IBDAC die Batch-Operation.
Das gibt es bei Firebird keine direkte Unterstützung in der API.

Meinst Du eine Batchkomponente?
Mittels Transaktionshandling sollte es eigentlich wurscht sein, was an Spezialitäten da ist.
Wenn man eine Reihe von Befehlen absetzt, werden sie erst mit "Commit" commited.
Viele Commits, nach jedem insert bspw, bringen auch eine Verzögerung. Blockweise commit sind schneller, ist aber glaub ich marginal.

[bnreimer42]

Zitat:

Zitat von jobo (Beitrag 1357991)

Meinst Du eine Batchkomponente?
Mittels Transaktionshandling sollte es eigentlich wurscht sein, was an Spezialitäten da ist.
Wenn man eine Reihe von Befehlen absetzt, werden sie erst mit "Commit" commited.
Viele Commits, nach jedem insert bspw, bringen auch eine Verzögerung. Blockweise commit sind schneller, ist aber glaub ich marginal.

Ja, die Batchkomponenten kann ja nicht zaubern.
Firebird kann zum Beispiel nicht die "Indexpflege" erst beim Commit machen und damit nur einmal für alle Commits.

Es kann auch sein, dass viele Statements im Batch langsamer sind. Wenn eine weitere ältere Transaktion alte Versionen der Sätze noch im Fokus hat, muss der Server neue Pages allozieren um die neuen Versionen der Sätze zu speichern. Beim Insert ist es klar, dass er das immer machen muss, aber bei mehreren Updates kann er die alten Speicherbereiche recyceln.

Das wird bei größeren Satzlängen problematischer, also insbesondere beim Updaten von größeren BLOBs, die ja dann ggf. auf eigene Pages pro BLOB ausgelagert werden zusätzlich zu den Pages, die er beschreiben muss, um den Satz zu speichern.

Diese Phänomene können obigen Geschwindigkeitsvorteil wieder wett machen.

Natürlich gibt es aber Gründe für eine derartige Implementierung und das ist die Datensicherheit und den Erhalt der Konsistenz der Datenbankdatei zu jedem Zeitpunkt. Bei Datenbanken sollte immer die Datensicherheit höher bewertet werden, als die Schnelligkeit.



Gruß

Björn

[jobo]

Zitat:

Zitat von bnreimer42 (Beitrag 1358000)

Zitat:

Zitat von jobo (Beitrag 1357991)

Meinst Du eine Batchkomponente?
Mittels Transaktionshandling sollte es eigentlich wurscht sein, was an Spezialitäten da ist.
Wenn man eine Reihe von Befehlen absetzt, werden sie erst mit "Commit" commited.
Viele Commits, nach jedem insert bspw, bringen auch eine Verzögerung. Blockweise commit sind schneller, ist aber glaub ich marginal.

Ja, die Batchkomponenten kann ja nicht zaubern.
Firebird kann zum Beispiel nicht die "Indexpflege" erst beim Commit machen und damit nur einmal für alle Commits.

Es kann auch sein, dass viele Statements im Batch langsamer sind. Wenn eine weitere ältere Transaktion alte Versionen der Sätze noch im Fokus hat, muss der Server neue Pages allozieren um die neuen Versionen der Sätze zu speichern. Beim Insert ist es klar, dass er das immer machen muss, aber bei mehreren Updates kann er die alten Speicherbereiche recyceln.

Das wird bei größeren Satzlängen problematischer, also insbesondere beim Updaten von größeren BLOBs, die ja dann ggf. auf eigene Pages pro BLOB ausgelagert werden zusätzlich zu den Pages, die er beschreiben muss, um den Satz zu speichern.

Diese Phänomene können obigen Geschwindigkeitsvorteil wieder wett machen.

Natürlich gibt es aber Gründe für eine derartige Implementierung und das ist die Datensicherheit und den Erhalt der Konsistenz der Datenbankdatei zu jedem Zeitpunkt. Bei Datenbanken sollte immer die Datensicherheit höher bewertet werden, als die Schnelligkeit.

Ja, ich sprach nur davon, dass hochfrequnete Commits einen Geschwindigkeitsnachteil haben gegenüber blockweisen, also dem reinen Commit Overhead.
Indizierungseffekte bspw. sind wieder eine andere Geschichte, defferable Indices sind auch interessant, aber nicht in jedem RDBMS verfügbar.

Interessant an deinen Ausführungen auch die BLOB Thematik, die ja vielleicht das Problem des TE berührt. Aber der ist ja wohl abgetaucht.

[mkinzler]

Bei Firebird kann man Indizes temporär deaktivieren:

SQL-Code:

ALTER INDEX <Indexname> INACTIVE;

Allerdings nicht bei aktiven Constraints.

[jobo]

Ah, wie wirkt sich das aus?
Nur auf die Aktualisierung oder auch auf die Nutzung bei Abfragen?

Die Einschränkung auf "ohne aktive Constraints" ist natürlich hart, wäre dann aber immerhin für reine Suchindices möglich.

Die Einschränkung deutet auch darauf hin, dass der Index nicht nur für Aktualisierungen deaktiviert ist, sondern auch für Abfragen.

[bnreimer42]

Hallo,

das stimmt. Einen reinen Such-Index kann man mit INACTIVE von Nutzung und Aktualisierung abschalten.
Funktioniert aber nicht für Indizes, die für Fremdschlüssel erzeugt wurden oder für Indizes, die für Constraints erzeugt wurden oder für UNIQUE-Indizes, was ja auch ein Constraint ist. Somit ist die Funktion bei einer "ordentlichen" Datenbankstruktur mit Fremdschlüsseln in der Praxis eher irrelevant.

Es kann aber auch nichts passieren, wenn man versucht, einen Index auf INACTIVE zu setzen, der in einem Constraint benötigt wird. Man bekommt einen Hinweis.

Eigentlich nutze ich das umsetzen nur für die Erneuerung der Statistken der Indizes beim "Aufräumen" der Datenbank.

[hstreicher]

da hier aber ein Update or Insert durchgeführt wird ,
ist das eher kontraproduktiv,
da ohne Index die Prüfung auf das Update eine Full Table Scan auslösen wird


mfg Hannes

[bnreimer42]

Zitat:

Zitat von hstreicher (Beitrag 1358030)

da hier aber ein Update or Insert durchgeführt wird ,
ist das eher kontraproduktiv,
da ohne Index die Prüfung auf das Update eine Full Table Scan auslösen wird


mfg Hannes

Hallo,

ja, wenn man den Index deaktivieren könnte. Der Index kann nicht auf INACTIVE gesetzt werden. Primary-Key ist ein Constraint.

Gruß

Björn

[mkinzler]

Es kommt auch darauf an, ob die Daten einer gewissen Überprüfung bedürfen. Falls Daten 1:1 eingespielt werden können kann man Indizes/Contraints problemlos Deaktivieren.

[jobo]

Mmh, also in dem Fall hier wo PK betroffen sind, würde ich das Deaktivieren von Constraints mal ausschließen.

Generell ist beim Deaktivieren von Indizes oder Constraints ja auch zu berücksichtigen, wer/was sonst noch grad im System geschieht.

Ebenfalls können aber neben den PK Keys und sonstigen Constraints auch reine Suchindices vorhanden sein. Die wären dann m.E. geeignet, sie zu deaktivieren. Dann muss im Zweifel mal jemand etwas länger auf einen Report oder eine Antwort der Suchmaske warten.