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

[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?

[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.

[Jasocul]

Zitat von mariusbenz:

aber ich hätte jetzt eher damit gerechnet, dass so etwas schleichend langsamer wird.

Nein, das kann auch "plötzlich" passieren.
Wenn sich an den Statements nichts geändert hat, würde ich dort auch nicht mit der Problemsuche anfangen.
Zuerst würde ich prüfen, ob die Selects oder die Inserts langsamer geworden sind. Ich tippe erstmal auf die Inserts.
Du schreibst etwas von einem globalen Server, wo die Daten landen. Ist die Verbindung dorthin stabil und schnell genug? Wir hatten mal einen defekten Hub, der nur manchmal Fehler verursacht hatte. Wurde vielleicht ein Netzwerkkabel umgesteckt oder ausgetauscht? Falls ja, wo werden die Statements ausgeführt? Hat sich daran vielleicht etwas geändert? Ich meine mich erinnern zu können, dass es unter Hyper-V mal Performance-Probleme gab.
Wie voll sind die Datenbank-Dateien? Stehen die auf automatischer Vergrößerung? Sind die Vergrößerungsschritte groß genug?
Wann wurden die Indexe der betroffenen Tabellen das letzte Mal reorganisiert?
MS-SQL neigt dazu, den gesamten Hauptspeicher in Anspruch zu nehmen. Darum sollten man auf einem MS-SQL-Server möglichst auch keine anderen Anwendungen nutzen.
Hast du auf dem Server die Log-Files schon kontrolliert?
Greift ein AV-Programm evtl. auf die DB-Dateien zu? Kann nach einem Update oder Konfigurationsanpassung passiert sein. Eigentlich sollte in dem Bereich möglichst kein AV-Programm etwas machen.
Ganz banal einen Server-Neustart schon versucht?
Es gibt noch andere Möglichkeiten, aber das wäre der Bereich, den ich erstmal gründlich untersuchen würde.

[mariusbenz]

Zitat von Jasocul:

Zitat von mariusbenz:

aber ich hätte jetzt eher damit gerechnet, dass so etwas schleichend langsamer wird.

Nein, das kann auch "plötzlich" passieren.
Wenn sich an den Statements nichts geändert hat, würde ich dort auch nicht mit der Problemsuche anfangen.
Zuerst würde ich prüfen, ob die Selects oder die Inserts langsamer geworden sind. Ich tippe erstmal auf die Inserts.
Du schreibst etwas von einem globalen Server, wo die Daten landen. Ist die Verbindung dorthin stabil und schnell genug? Wir hatten mal einen defekten Hub, der nur manchmal Fehler verursacht hatte. Wurde vielleicht ein Netzwerkkabel umgesteckt oder ausgetauscht? Falls ja, wo werden die Statements ausgeführt? Hat sich daran vielleicht etwas geändert? Ich meine mich erinnern zu können, dass es unter Hyper-V mal Performance-Probleme gab.
Wie voll sind die Datenbank-Dateien? Stehen die auf automatischer Vergrößerung? Sind die Vergrößerungsschritte groß genug?
Wann wurden die Indexe der betroffenen Tabellen das letzte Mal reorganisiert?
MS-SQL neigt dazu, den gesamten Hauptspeicher in Anspruch zu nehmen. Darum sollten man auf einem MS-SQL-Server möglichst auch keine anderen Anwendungen nutzen.
Hast du auf dem Server die Log-Files schon kontrolliert?
Greift ein AV-Programm evtl. auf die DB-Dateien zu? Kann nach einem Update oder Konfigurationsanpassung passiert sein. Eigentlich sollte in dem Bereich möglichst kein AV-Programm etwas machen.
Ganz banal einen Server-Neustart schon versucht?
Es gibt noch andere Möglichkeiten, aber das wäre der Bereich, den ich erstmal gründlich untersuchen würde.

Hallo Jasocul, da sind natürlich einige Sachen dabei, die wir uns sicherlich mal anschauen sollten.
- Die Schnittstelle läuft auf einem Hintergrundrechner mit Serverbetriebssystem, bei dem wurden auch Ping Downs vom PRTG gemeldet, den habe ich natürlich mit als erstes neugestartet. Der Datenbankserver an sich lief auch stabil, zumindest habe ich von keinen anderen Fehlverhalten etwas mitbekommen. Von meinem Arbeitsrechner aus hatte ich aber dieselben Probleme bei eben dieser einen Abfrage. Daher schließe ich Hardware/Netzwerk erst mal aus.
- Mit Dateispeichergrößen kenne ich mich nicht gut aus, anbei mal die aktuellen Einstellungen der DB. Das ist auch die mit Abstand größte DB auf dem Server und die Einstellungen wirken falsch auf mich. Hat dafür jemand Empfehlungen?
- Ich weiß leider auch nicht, was man wie bei Indizes genau einstellen muss. Damit habe ich mich noch nicht gut genug beschäftigt.
- Auf dem Datenbankserver (bzw. auf der Instanz) läuft meines Wissens nach nur der SQL Server
- In den SQL-Server Protokollen habe ich auf die schnelle nichts gefunden, habe aber auch nicht intensiv durchforstet
- Unseren blöden Antivirus habe ich mittlerweile grundsätzlich als Hauptverdächtigen, allerdings passt es irgendwie nicht, dass plötzlich eine einzelne Abfrage deswegen 1000x langsamer wird
- SQL-Server-Neustart wäre spätestens dann gekommen, wenn ich keine andere Lösung gefunden hätte. Dafür hat man im Produktivsystem natürlich immer nur ein kleines Zeitfenster

[Bernhard Geyer]

Zitat von Jasocul:

Zitat von mariusbenz:

aber ich hätte jetzt eher damit gerechnet, dass so etwas schleichend langsamer wird.

Nein, das kann auch "plötzlich" passieren.

Yepp. Hatten wir mal bei einem Kunden mit einer größeren Datenbank.
Auf einmal meinte der Query-Analyzer das man für eine relativ einfache Abfrage die sehr wenige (1-10) Datensätze liefert 1GB RAM benötigen würde.
AFAIK hat sich das mit einem Update des SQL-Servers irgendwann gelöst.

Abfrage im SQL Mangement Studio mal analysieren lassen, was da rauskommt bezüglich Abfragepfad und Kosten.