Parametrisiertes Insert plötzlich abnormal langsam

[Jasocul]

Hallo mariusbenz,
also die Datei hat schon 21GB und die Vergrößerung ist auf 1MB gesetzt. Das kommt mir im Verhältnis doch etwas wenig vor. Aber das kann auch völlig in Ordnung sein, wenn der Datenzuwachs sehr gering ist. Um das richtig einzustellen, muss man schon einiges analysieren.
Im letzten Unternehmen, bei dem ich war, wurden auch mehrere MS-SQL-Server eingesetzt. Für das Datawarehouse mit täglich, erheblichem Datenzuwachs, wurde die Datenbank mindestens einmal im Monat einem Optimierungslauf unterworfen. Das wurde über Jobs zum passenden Zeitpunkt automatisch angestoßen. Daher habe ich den passenden Befehl leider auch nicht mehr im Kopf. Wie das so ist: Einmal eingericht -> funktioniert und macht alles richtig -> nie mehr drüber nachdenken. Im jetzigen Unternehmen arbeite ich mit anderen Datenbanken und kann das leider nicht nachsehen.
Beim Schreiben ist es mir gerade wieder eingefallen (Glück gehabt ). Suche mal nach Update Statistics. Das war regelmäßig erforderlich und hat im Prinzip das von dir beschriebene Problem behoben. Der Import von Daten lief dort zwar über Dump-Imports, aber ich kann mir gut vorstellen, dass das bei dir auch helfen kann. Allerdings kann das einige Zeit dauern. Und damit meine ich nicht nur ein paar Minuten. Wenn du die Möglichkeit hast, würde ich das zunächst auf einem Testsystem mit einer Kopie der Datenbank machen. Bei der von dir beschriebenen Größe würde ich von ein paar Stunden ausgehen.

Vorher kannst du natürlich auch erstmal die betroffenen Indexe reorganisieren lassen.

[Delphi.Narium]

Wenn sichergestellt ist, dass das Problem tatsächlich aus Deinem Programm kommt (und nicht eine der vielen Möglichkeiten, die Jasocul aufführt, als Ursache in Betracht kommt):

Wie sieht das Transaktionshandling aus?
Eine Transaktion je ExecSQL oder eine Transaktion je while NOT (Que_L_Export.Eof) do , also alles aus einer Tabelle innerhalb einer Transaktion verarbeiten?

Statt je Datensatz immer wieder alle Parameter und Felder zu suchen, diese vorher als entsprechende Variabeln deklarieren und einmalig suchen lassen. Sowas in der Art:

Delphi-Quellcode:

var
  pIDVonQuelle : TParam;
  pParamID : TParam;
  pDelta : TParam;
  pAnzahl : TParam;
  pZeit : TParam;
  fID : TField;
  fParamID : TField;
  fDelta : TField;
  fAnzahl : TField;
  fGeaendert_am : TField;
begin
  ...
  pIDVonQuelle := Que_G_MengenLogbuch_Insert.Params.FindParam('IDVonQuelle');
  pParamID := Que_G_MengenLogbuch_Insert.Params.FindParam('ParamID');
  pDelta := Que_G_MengenLogbuch_Insert.Params.FindParam('Delta');
  pAnzahl := Que_G_MengenLogbuch_Insert.Params.FindParam('Anzahl');
  pZeit := Que_G_MengenLogbuch_Insert.Params.FindParam('Zeit');
  
  fID := Que_L_Export.FindField('ID');
  fParamID := Que_L_Export.FindField('ParamID');
  fDelta := Que_L_Export.FindField('Delta');
  fAnzahl := Que_L_Export.FindField('Anzahl');
  fGeaendert_am := Que_L_Export.FindField('geaendert_am');

  while NOT (Que_L_Export.Eof) do
   begin
     pIDVonQuelle.AsInteger := fID.AsInteger;
     pParamID.AsInteger := fParamID.AsInteger;
     pDelta.AsInteger := fDelta.AsInteger;
     pAnzahl.AsInteger := fAnzahl.AsInteger;
     fGeaendert_am.AsDateTime := fGeaendert_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; 
  ...

Bei großen Datenmengen kann sich das, auch wenn es erstmal nach banalem, vermeidbarem, Mehraufwand im Quelltext aussieht, (aus meiner Erfahrung heraus) durchaus positiv auf die Laufzeit (auf Clientebene) auswirken.

Bei den Parametern hast Du zusätzlich den Vorteil, dass Du den DataType und den ParamType angeben kannst und nicht darauf hoffen musst, dass die Datenbanksschnittstelle schon das Richtige machen wird.

Mit einh bisserl Glück kannst Du so das

Zitat von mariusbenz:

(wenn ich es davor setze, kommen irgendwelche CONVERT variant Exceptions)

vermeiden.

Also zusätzlich noch sowas in der Art:

Delphi-Quellcode:

  pIDVonQuelle.DataType := ftInteger;
  pParamID.DataType := ftInteger;
  pDelta.DataType := ftInteger;
  pAnzahl.DataType := ftInteger;
  pZeit.DataType := ftDateTime;
  pIDVonQuelle.ParamType := ptInput;
  pParamID.ParamType := ptInput;
  pDelta.ParamType := ptInput;
  pAnzahl.ParamType := ptInput;
  pZeit.ParamType := ptInput;

Damit müsstest Du das Prepare vor die While-Schleife setzen können.
Gibt es eine Garantie, dass das dann im realen (Datenbank)Leben wirklich was bringt? Nein, aber eventuell ist's 'nen Versuch wert.

Zitat von mariusbenz:

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

Doch, die können bei 'nem Update durchaus auch mal "Bockmist" gebaut haben und nun z. B. Datenbankdateien mit scannen, was sie vor dem Update nicht gemacht haben, irgendwelche konfigurierten Ausnahmeregeln nicht mehr beachten, ... Da kann schon ziemlich viel passieren, auf das Du erstmal keinen Einfluss hast, weil solches "Fehlverhalten" nicht zwingend offensichtlich ist.

Die DB ist laut Screenshot ca. 21 GB groß. Und die wird in Schritten von 1 Megabyte vergrößert?

Welche Datenmengen habt Ihr denn da? Könnte es sein, dass der Datenbankserver "fast ausschließlich" mit dem Vergrößern der Datenbankdatei beschäftigt ist? Und wenn die Platte dann stark fragmentiert ist oder sehr voll, wird sowas durchaus auch schonmal "etwas langsamer".

Das kann auch das "Plötzlich von jetzt auf gleich" für's Langsamwerden begründen. Bis 20 GB Ursprungsgröße war die Datenbankdatei ausreichend dimensioniert und ab 20 GB + 1 MB wird "alle Nase lang" vergrößert.

Berechne bitte mal grob die Datenmenge, die Du pro Aufruf Deiner Routine in etwa in die Datenbank schreiben musst, also nicht nur hier die langsame Routine, sondern auch die Protokollstatements, die in eine andere Tabelle geschrieben werden ... (ggfls. auch andere Prozesse, die in die Datenbank schreiben, beachten). Für wieviele Programmaufrufe reicht dann 1 Megabyte aus? Die 1 Megabyteschritte kämen für mich nur bei einer Datenbank mit marginalem Zuwachs in frage. (Flapsig formuliert: Der Zuwachs sollte schon für die nächsten ein oder zwei Wochen reichen )

Und: Alles was Jasocul schreibt, sollte vorab überprüft bzw. durchgeführt werden.

[masc-online]

Die Schrittweise Erweiterung der Datenbank-Dateien um 1 MB ist einer der hässlichsten Standardwerte, die Microsoft sich ausgedacht hat. Wenn hier ein Schwung Daten eingefügt werden, muss der SQL Server ständig die Anforderung zur Erweiterung ans OS senden. Wenn zudem das Ganze noch in einer virtualisierten Umgebung passiert und die VM-Disks sich ebenfalls aus Platzspargründen so eingestellt sind, dass sie Speicher erst bei Wachstum auf der virtuellen Platte allokieren, hat man weiter hinten auch noch einen zusätzlichen Request - und wenn man viel Glück hat, gilt gleiches auch noch im Storage-System.

--> Wenn das Autoextend aktiv bleiben soll, würde ich etwas größere Blöcke nehmen (irgendwas zwischen 64 MB und 512 MB). In der Praxis würde ich zudem ein "wir erwarten im nächsten Quartal das Wachstum von xx GB" bevorzugen - und dann die Größe direkt zuweisen.


Ansonsten würde ich bei dem Problem empfehlen, einen Blick auf den Activity Monitor des SQL Servers zu werfen. Mit dem kriegt man einen relativ schnellen Einblick, womit sich die Datenbank gerade beschäftigt und kann dann gezielt das Problem angehen.

[TigerLilly]

Yep. Activity Monitor bzw Profiler - dann weißt du was passiert und was wie lange dauert. Für so ein Verhalten kann es - haben ja schon einige gesagt - viele Gründe geben. Aber ohne strukturiertes Vorgehen (=messen!) ist das ein Stochern im Heuhaufen.

Ich würde mit den Ausführungsplänen der SQL Statemnets anfangen. Ich würde auch am Server Performanceprotokolle mitlaufen lassen, da gibt es gute Kennzahlen für den SQL Server, die weiterhelfen.

https://learn.microsoft.com/en-us/sq...l-server-ver16
https://learn.microsoft.com/en-us/sq...l-server-ver16
https://learn.microsoft.com/en-us/sq...l-server-ver16

[mariusbenz]

Vielen Dank für alle eure Antworten.

Also die DB ist seit gestern um gut 20MB gewachsen, hier werde ich definitiv mal einen höheren Wert einstellen. Ebenso die Index-Reorganisierung.

Da sich das Problem gestern ja "von selbst" wieder gelöst hat, als ich einige vorherige Vorschläge am testen war, kann ich jetzt nicht mehr viel analysieren.
Wenn der Antivirus die Ursache war, würde es auch Sinn machen, dass es plötzlich wieder geht. Der wird ja auch ständig im Hintergrund aktualisiert, ohne dass man was direkt davon mitbekommt.
Über den Ausführungsplan haben wir die kritischen Abfragen zumindest schon soweit analysiert und Anpassungen vorgenommen, dass keine fehlenden Indizes mehr angemeckert werden. Was man darüber hinaus aus diesem Diagramm ableiten kann ist mir aber ein Rätsel.