Parametrisiertes Insert plötzlich abnormal langsam

[mariusbenz]

Guten Morgen,

wir haben eine Datenschnittstelle, die Daten von lokalen SQL-Express Servern auf einen globalen SQL Server einliest. Diesen Einlesevorgang haben wir über die Jahre durch Indizes entsprechend optimiert, sodass wir 1000 Datensätze in ca. 1-3s einlesen können.
Vor ein paar Tagen hat sich dieser Prozess um den Faktor 1000 verlangsamt, so dass wir plötzlich bei 5s pro Datensatz lagen. Zugegebenermaßen hat die Tabelle 140.000.000 Datensätze und 4 Indizes, die ein Einfügen nicht schneller machen, aber ich hätte jetzt eher damit gerechnet, dass so etwas schleichend langsamer wird.
Hier mal der Quellcode dazu:

Delphi-Quellcode:

Que_G_MengenLogbuch_Insert.Close;
Que_G_MengenLogbuch_Insert.SQL.Clear;
Que_G_MengenLogbuch_Insert.SQL.Add(   
   'IF EXISTS(SELECT IDVonQuelle ' +
            'FROM MDE_Mengen ' +
            'WHERE ParamID = :ParamID ' +
            'AND IDVonQuelle = :IDVonQuelle) ' +
     'BEGIN ' +
      'UPDATE MDE_Mengen ' +
      'SET ParamID = :ParamID, ' +
           'Delta = :Delta, ' +
           'Anzahl = :Anzahl, ' +
           'Zeit = :Zeit, ' +
           'IDVonQuelle = :IDVonQuelle ' +
      'WHERE ParamID = :ParamID ' +
            'AND IDVonQuelle = :IDVonQuelle ' +
     'END ' +
   'ELSE ' +
     'BEGIN ' +
       'INSERT INTO MDE_Mengen (ParamID, Delta, Anzahl, Zeit, IDVonQuelle) ' +
       'VALUES ( :ParamID, ' +
              ':Delta, ' +
              ':Anzahl, ' +
              ':Zeit, ' +
              ':IDVonQuelle) ' +
     'END;');

  while NOT (Que_L_Export.Eof) do
   begin
     Que_G_MengenLogbuch_Insert.Params.ParamByName('IDVonQuelle').AsInteger := Que_L_Export.FieldByName('ID').AsInteger;
     Que_G_MengenLogbuch_Insert.Params.ParamByName('ParamID').AsInteger := Que_L_Export.FieldByName('ParamID').AsInteger;
     Que_G_MengenLogbuch_Insert.Params.ParamByName('Delta').AsInteger := Que_L_Export.FieldByName('Delta').AsInteger;
     Que_G_MengenLogbuch_Insert.Params.ParamByName('Anzahl').AsInteger := Que_L_Export.FieldByName('Anzahl').AsInteger;
     Que_G_MengenLogbuch_Insert.Params.ParamByName('Zeit').AsDateTime := Que_L_Export.FieldByName('geaendert_am').AsDateTime;

     if NOT(Que_G_MengenLogbuch_Insert.Prepared) then Que_G_MengenLogbuch_Insert.Prepare();
     Que_G_MengenLogbuch_Insert.Execute();
     
     Que_L_Export.Next;
   end;

Das Que_G_MengenLogbuch_Insert.Execute() hier dauert ca. 5s.

Wenn ich das auf die unsichere und vermeintlich langsamere Methode umstelle, funktioniert alles wieder gewohnt schnell:

Delphi-Quellcode:

while NOT (Que_L_Export.Eof) do
   begin
     Que_G_MFA_MDE.Close;
     Que_G_MFA_MDE.SQL.Clear;
     Que_G_MFA_MDE.SQL.Add(
         'IF EXISTS(SELECT IDVonQuelle ' +
                  'FROM MDE_Mengen ' +
                  'WHERE ParamID = ' + Que_L_Export.FieldByName('ParamID').AsString + ' ' +
                  'AND IDVonQuelle = ' + Que_L_Export.FieldByName('ID').AsString + ') ' +
            'BEGIN ' +
              'UPDATE MDE_Mengen ' +
              'SET ParamID = ' + Que_L_Export.FieldByName('ParamID').AsString + ', ' +
                  'Delta = ' + IntToStr(Que_L_Export.FieldByName('Delta').AsInteger) + ', ' +
                  'Anzahl = ' + IntToStr(Que_L_Export.FieldByName('Anzahl').AsInteger) + ', ' +
                  'Zeit = ' + DateTimeToQuotedISO8601(Que_L_Export.FieldByName('geaendert_am').AsDateTime, true) + ', ' +
                  'IDVonQuelle = ' + Que_L_Export.FieldByName('ID').AsString + ' ' +
              'WHERE ParamID = ' + Que_L_Export.FieldByName('ParamID').AsString + ' ' +
                    'AND IDVonQuelle = ' + Que_L_Export.FieldByName('ID').AsString + ' ' +
            'END ' +
           'ELSE ' +
            'BEGIN ' +
               'INSERT INTO MDE_Mengen (ParamID, Delta, Anzahl, Zeit, IDVonQuelle) ' +
               'VALUES ( ' + Que_L_Export.FieldByName('ParamID').AsString + ', ' +
                     IntToStr(Que_L_Export.FieldByName('Delta').AsInteger) + ', ' +
                     IntToStr(Que_L_Export.FieldByName('Anzahl').AsInteger) + ', ' +
                     DateTimeToQuotedISO8601(Que_L_Export.FieldByName('geaendert_am').AsDateTime, true) + ', ' +
                     Que_L_Export.FieldByName('ID').AsString + ') ' +
            'END;');
      Que_G_MFA_MDE.ExecSQL;
      
      Que_L_Export.Next;
   end;

Kann sich jemand erklären, warum das parametrisierte plötzlich Statement so viel langsamer geworden ist?

[Uwe Raabe]

Ist das wirklich der reale Code im zweiten Abschnitt? Dort wird innerhalb der WHILE-Schleife ein SQL.Add gemacht ohne den vorigen Inhalt zu löschen.

Passiert zwar auch im ersten, aber da sieht man nicht, ob es mehrfach ausgeführt wird.

[mariusbenz]

Hallo Uwe,

ich hatte hier etwas vereinfachten Quellcode eingefügt und es jetzt entsprechend angepasst. Es wird natürlich vorher immer geleert.

[exon]

Hallo, unabhängig von dem Problem,

Wäre es evtl. sinnvoll, auf Batch-Operation umzustellen?

https://docs.devart.com/unidac/batchops.htm

[dataspider]

Ist es auch richtig, dass du in der neuen Version Execute anstatt ExecSQL benutzt?
Möglicherweise gibt es da Unterschiede.

Die Zeile mit dem Prepare gehört IMO über das while unmittelbar nach dem Setzen des Statements.

Und Options.AutoPrepare der Query sollte False sein.

Frank

[mariusbenz]

Zitat von exon:

Hallo, unabhängig von dem Problem,

Wäre es evtl. sinnvoll, auf Batch-Operation umzustellen?

https://docs.devart.com/unidac/batchops.htm

Nach jedem Insert wird noch ein Update in einer anderen Tabelle gemacht, um im Fehlerfall genau den fehlerhaften Datensatz identifizieren zu können.

Zitat von Frickler:

Wenn sowas "ganz plötzlich" passiert: gab es eventuell ein Update der DB-Server?

Habt ihr ansonsten mal probiert, das Statement umzuschreiben auf MERGE?

Es gab rund um den Zeitraum keine Updates auf betroffenen Systemen. Es war laut Log-Dateien von der 1 zur nächsten Minute plötzlich langsamer.

Zitat von dataspider:

Ist es auch richtig, dass du in der neuen Version Execute anstatt ExecSQL benutzt?
Möglicherweise gibt es da Unterschiede.

Die Zeile mit dem Prepare gehört IMO über das while unmittelbar nach dem Setzen des Statements.

Und Options.AutoPrepare der Query sollte False sein.

Frank

AutoPrepare ist false. Prepare ist vermutlich an der Stelle, weil dann die Datentypen durch die Zuweisungen bekannt sind (wenn ich es davor setze, kommen irgendwelche CONVERT variant Exceptions).
Einen Unterschied zwischen Execute und ExecSQL wollte ich gerade mal testen, nur um festzustellen, dass wieder alles funktioniert??? Das lief seit dem 06.01. 05:01 bis zum Verfassen des Themas durchgehend langsam.

Ich beobachte das jetzt mal weiter.

[Frickler]

Wenn sowas "ganz plötzlich" passiert: gab es eventuell ein Update der DB-Server?

Habt ihr ansonsten mal probiert, das Statement umzuschreiben auf MERGE?