Laden...

SQL-Query-Timeout - aber nicht im Management Studio

Erstellt von ChrisProg vor 3 Jahren Letzter Beitrag vor 3 Jahren 2.487 Views
ChrisProg Themenstarter:in
174 Beiträge seit 2009
vor 3 Jahren
SQL-Query-Timeout - aber nicht im Management Studio

Verwendetes Datenbanksystem: SQL-Server 13.0.5882.1

Hallo zusammen,

folgendes Problem habe ich auf einem Kunden-System (und nur dort!) :

Ich habe ein 400-zeiliges SQL-Script mit 30 Parametern, welches Daten aus einer WaWi sammelt.
Dieses Script habe ich als Text hinterlegt und lese ihn in ein entsprechenden SQL-Command ein.

Die Parameter werden alle mit ihrem korrekten Datentyp und IsNullable = true erstellt.

Rufe ich nun einen entsprechenden DataAdapter auf


 using (SqlDataAdapter da_su = new SqlDataAdapter(x_ermittle_verpackungen_kunde))
{
    da_su.Fill(cBestaende);
}

dann laufe ich jedesmal auf ein Timeout

"Das Ausführungstimeout ist abgelaufen. Der Timeoutzeitraum wurde überschritten, bevor der Vorgang beendet wurde, oder der Server antwortet nicht. ..."

Der Fehler verweist auf die Zeile "da_su.Fill(cBestaende)" ...

Den (das?) Timeout habe ich dabei schon auf 180 sek hoch gesetzt ...

Nun zu dem eigentlichen Problem:
Rufe ich das Script im Management Studio auf (mit über declare erstellte u. mit set entsprechend gefüllte Parameter) so funktioniert es einwandfrei und braucht nicht einmal zwei Sekunden ...

Auf meinem Entwicklungssystem habe ich keine Probleme ...

Kennt jemand ein solches Phänomen oder kann mir Tips nennen, wie man noch an das Problem heran kommen kann ?

MfG Christian

463 Beiträge seit 2009
vor 3 Jahren

Hast du mal geprüft welcher SQL Befehl beim SQL-Server ankommt? Ich vermute hier einen/mehrere Parameter welche hier Probleme verursachen.

16.864 Beiträge seit 2008
vor 3 Jahren

Du vergleichst einfach Äpfel mit Birnen.

SqlDataAdapter funktioniert völlig anders als das Management Studio selbst.
Das MS ist im Endeffekt nur ein Executor; SqlDataAdapter ist eine Runtime Funktion, die extrem viel allokiert.
SqlDataAdapter ist sogar meines Wissens die aller langsamste Variante, wie man über ADO.NET mit einer Datenbank kommunizieren kann.

Warum willst Du überhaupt SqlDataAdapter nutzen, wenn Du schon ein fertiges Script hast und das auch direkt ausführen kannst?

3.825 Beiträge seit 2006
vor 3 Jahren

Was passiert wenn Du exakt den gleichen SQL Befehl im Code ausführst wie der aus dem Management Studio ?

Was passiert wenn Du einen DataReader benutzt ?

Ich denke dass man das Management Studio eher mit einem DataReader vergleichen kann weil es während der Ausführung responsiv bleibt und im Hintergrund lädt.

Wenn es eine Tabelle anzeigt ist es oft noch nicht fertig mit laden.

Grüße Bernd

Workshop : Datenbanken mit ADO.NET
Xamarin Mobile App : Finderwille Einsatz App
Unternehmenssoftware : Quasar-3

ChrisProg Themenstarter:in
174 Beiträge seit 2009
vor 3 Jahren

Hast du mal geprüft welcher SQL Befehl beim SQL-Server ankommt? Ich vermute hier einen/mehrere Parameter welche hier Probleme verursachen.

Blöde Frage: wie kann man das denn ?

(...) Warum willst Du überhaupt SqlDataAdapter nutzen, wenn Du schon ein fertiges Script hast und das auch direkt ausführen kannst?

Das ist doch DER Weg (so lese ich das jedenfalls im Netz), um ein Ergebnis direkt in eine DataTable zu bekommen; ok. man könnte einen DataReader nehmen u. durch alle Rows iterieren und "von Hand" in eine DataTable eintragen, aber das wäre sicherlich auch nicht schneller, oder ?

Was passiert wenn Du exakt den gleichen SQL Befehl im Code ausführst wie der aus dem Management Studio ?

Der im Management Studio ausgeführte Code ist der aus dem SQL-Command herauskopierte Code ...

Was passiert wenn Du einen DataReader benutzt ?

werde ich mal probieren (u. berichten) ...

MfG Christian

16.864 Beiträge seit 2008
vor 3 Jahren

Das ist doch DER Weg (so lese ich das jedenfalls im Netz), um ein Ergebnis direkt in eine DataTable zu bekommen; ok. man könnte einen DataReader nehmen u. durch alle Rows iterieren und "von Hand" in eine DataTable eintragen, aber das wäre sicherlich auch nicht schneller, oder ?

Das ist aber was völlig anderes als die Execution im MS.

Im MS führst Du etwas aus und bekommst das native Ergebnis.
In Deinem Adapter führst Du etwas aus, holst das Ergebnis, serialisierst das Ergebnis, mappst das Ergebnis in tausende von Objekten und wirfst das dann in ne DataTable.
Drunter ist ein DataSet, das im Gegensatz zum DataTable zigfach mehr Speicher benötigt (Table is forward only, Set ist auch fürs Manipulieren gedacht).

Du schaufelst Dir also im Gegensatz zu MS einen riesen Berg an Objekten und Speicherallokationen an; und dann wunderst Dich, dass das langsamer ist? 🙂
Im Endeffekt passiert genau das, was Du programmiert hast. Die Frage ist ob das, was Du programmiert hast, das ist, was Du eigentlich willst 🙂

Das mit dem Reader könnte schon ne Besserung bringen, wenn Du nur anzeigen willst.

ChrisProg Themenstarter:in
174 Beiträge seit 2009
vor 3 Jahren

So, habe es jetzt mal auf


using (SqlDataReader x_reader = x_ermittle_verpackungen_kunde.ExecuteReader())
{
     cBestaende.Load(x_reader);
}

umgestellt, leider mit dem gleichen Ergebnis 🤢

Also werde ich mich jetzt wohl der Frage von Stefan.Haegele näher befassen ...

MfG Christian

3.825 Beiträge seit 2006
vor 3 Jahren

Und was passiert bei


while (x_reader.Read())
{}

Was soll mit den Daten geschehen ?

DataSet und DataTable ist wie Abt schon gesagt sehr speicher- und zeitintensiv.

Ich benutze bei großen Datenmengen nur DataReader. Beim DataTable weiß man nie ob das beim Kunden funktioniert oder nicht.

Und statt select * die Spalten angeben die man braucht.

Grüße Bernd

Workshop : Datenbanken mit ADO.NET
Xamarin Mobile App : Finderwille Einsatz App
Unternehmenssoftware : Quasar-3

ChrisProg Themenstarter:in
174 Beiträge seit 2009
vor 3 Jahren

Hi Bernd,

ich brauche leider schon die DataTable ...

Die Daten, die ich bekomme muss ich teilweise editieren (Daten die so nicht im SQL vorhanden sind...), anschließend gehen Sie in einen Report ...

MfG Christian

16.864 Beiträge seit 2008
vor 3 Jahren

Wenn Du das Speicher- und Zeitintensive Zeug willst / brauchst, dann musst eben damit leben.
Das ist an der Stelle nicht änderbar - und eine bekannte Situation mit diesen Bausteinen.

Daher verwendet man entsprechende in neueren Konstrukten auch nicht mehr.

ChrisProg Themenstarter:in
174 Beiträge seit 2009
vor 3 Jahren

Was wird denn nun genommen / gemacht ? Kannst Du mir da ein paar Links / Stichworte nennen ?
(Irgendwie scheine ich wohl mit meinem Wissen stehen geblieben zu sein ...)

Ich vermute mal ganz stark, das es dabei auch auf den verwendeten Berichtsdesigner ankommt, richtig?

MfG Christian

T
2.228 Beiträge seit 2008
vor 3 Jahren

Auch wenn du ein DataTable brauchst, wäre es sinnvoller mit dem DataReader die Daten auszulesen.
Hier würde ich dann für die DB Rückmeldung eine entsprechende Klasse anlegen und pro Datensatz eine Instanz anlegen und als Liste liefern.
Wenn diese ohne große Wartezeit geliefert werden, dann liegt das Problem am SqlDataAdapter.
Falls es auch hier zu einem Timeout kommt, dann wird die Abfrage ggf. auch langsam sein.
Kann man aber auch recht stumpf gegenprüfen.

Nachtrag:
I.d.R. werden die Daten aus der DB entweder über OR Mapper wie Entity Framework oder Depper materialisiert.
Man kann dies auch noch klassisch über ADO .NET via DataReader selbst machen, Anleitungen dazu gibt es wie Sand am Meer.
Über den DataAdapter würde ich nie gehen, da dieser einfach unperformant und zu Speicherlastig ist.

T-Virus

Developer, Developer, Developer, Developer....

99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.

ChrisProg Themenstarter:in
174 Beiträge seit 2009
vor 3 Jahren

Hallo,

um das ganze aufzulösen: es lag(warum auch immer) am SQL-Server beim Kunden ...

Nach einem Neustart funktionierte alles wieder.

Ich danke allen für die Inputs (ich werde zukünftig auf die DataAdapter verzichten...) 😃

Zum Verständnis meinerseits:

Was passiert eigentlich genau (physikalisch) wenn ich einen SqlCommand ausführe?
Übergibt er den Command wirklich als Batch-File an den SQL-Server, so das dieser die eigentliche Verarbeitung durchführt u. (in meinem Fall) die Ergebnistabelle zurück liefert oder macht das der Command alles selber ?

MfG Christian

3.825 Beiträge seit 2006
vor 3 Jahren

Ja, Neustart ist immer gut !

Der SQL Server bekommt den gesamten SQL Befehl und übersetzt ihn und führt ihn aus. Das ist ja Sinn der Sache.

Wenn Du eienn SQL Befehl mit Parametern übergibst dann setzt der SQL Server die Parameter ein, Du musst Dich nicht um länderspezifische Darstellung oder Sonderzeichen kümmern (SQL Injection).

Die Parameter haben noch den Vorteil dass der SQL Server jedes Kommando nur einmal übersetzen muss wenn sich nur die Parameter ändern.

Ich verzichte bei großen Datenmangen auf Tables und DataSet weil man nie weiß was beim Kunden passiert.

Lieber DataReader und selber durchgehen.

Grüße Bernd

Workshop : Datenbanken mit ADO.NET
Xamarin Mobile App : Finderwille Einsatz App
Unternehmenssoftware : Quasar-3

ChrisProg Themenstarter:in
174 Beiträge seit 2009
vor 3 Jahren

(...) Die Parameter haben noch den Vorteil dass der SQL Server jedes Kommando nur einmal übersetzen muss wenn sich nur die Parameter ändern.

Das war auch meine bisherige Info und Intention ...

Lieber DataReader und selber durchgehen.

Was meinst Du genau,


 using (SqlDataReader x_reader = x_ermittle_verpackungen_kunde.ExecuteReader())
{
      cBestaende.Load(x_reader);
}

ist doch auch ok oder ist


using (SqlDataReader x_reader = x_ermittle_verpackungen_kunde.ExecuteReader())
{
     while (x_reader.Read())
     {
            /// werte ermitteln und in DataTable einfügen 
     }
}

trotz der Schleife noch schneller ???

MfG Christian

T
433 Beiträge seit 2006
vor 3 Jahren

Das hört sich eher nach einem Problem mit Parameter Sniffing an.
Probier doch mal OPTION (RECOMPILE) - Query Hints (Transact-SQL) - SQL Server

Wirst ja hoffentlich nicht ständig den SQL Server neustarten wollen ...

Schönen Gruß
Tom

F
10.010 Beiträge seit 2004
vor 3 Jahren
  
 using (SqlDataReader x_reader = x_ermittle_verpackungen_kunde.ExecuteReader())  
{  
      cBestaende.Load(x_reader);  
}  
  

ist doch auch ok oder ist

Nein, das befüllen der DataTable ist das Problem, die DT ist ein Speichermonster das ziemlich langsam ist.

Jeder Reportgenerator seit Eonen kann auch mit Klassen umgehen, also ist das arbeiten mit Klassen und deren Listen die deutlich schnellere und einfachere alternative.
Ausserdem ist es typsicher.

3.825 Beiträge seit 2006
vor 3 Jahren

using (SqlDataReader x_reader = x_ermittle_verpackungen_kunde.ExecuteReader())
{
     while (x_reader.Read())
     {
            cBestaende.Rows.Add(...);
     }
}

Ob das schneller ist weiß ich nicht. Aber man kann dem Anwender eine Progressbar anzeigen und einen Abbruch Button zur Verfügung stellen.

Bei mir hat alles was länger als 2 Sekunden dauert eine Progressbar.

Außerdem prüfe ich den Hauptspeicherbedarf. Wenn ein Anwender versucht eine Liste mit 100 Millionen Datensätzen anzuzeigen kommt eine entsprechende Fehlermeldung.

Grüße Bernd

Workshop : Datenbanken mit ADO.NET
Xamarin Mobile App : Finderwille Einsatz App
Unternehmenssoftware : Quasar-3

ChrisProg Themenstarter:in
174 Beiträge seit 2009
vor 3 Jahren

Das hört sich eher nach einem Problem mit Parameter Sniffing an.
Probier doch mal OPTION (RECOMPILE) -
>

Wirst ja hoffentlich nicht ständig den SQL Server neustarten wollen ...

Schönen Gruß
Tom

Hi,

tatsächlich will ich das natürlich nicht ...

Danke für den Tip /Link 👍 - wieder was dazu gelernt ...

Wenn ich das richtig verstanden habe, dann löscht "RECOMPILE" den Batch aus den SQL-Server-Speichern, so das beim nächsten Aufruf es wieder frisch eingelesen und verarbeitet werden kann ...
Das ist natürlich für eine dauerhafte Programmierung kontraproduktiv (damit wäre ja der Geschwindigkeitsvorteil dahin...)

Weißt Du in welcher sys-Tabelle solche Batches gespeichert werden ? (in der sys.objects augenscheinlich nicht) - dann könnte man ja dort gezielt ein "sp_refreshview" durchführen 7 oder den Eintrag löschen ...

MfG Christian

ChrisProg Themenstarter:in
174 Beiträge seit 2009
vor 3 Jahren

(...)
Ob das schneller ist weiß ich nicht. Aber man kann dem Anwender eine Progressbar anzeigen und einen Abbruch Button zur Verfügung stellen.

Bei mir hat alles was länger als 2 Sekunden dauert eine Progressbar.

Auch ein guter Einwand ... 👍

(...)
Außerdem prüfe ich den Hauptspeicherbedarf. Wenn ein Anwender versucht eine Liste mit 100 Millionen Datensätzen anzuzeigen kommt eine entsprechende Fehlermeldung.

Wie kannst Du das denn, wenn doch der Reader strikt vorwarding ist, du also noch gar nicht weißt, wie viele Daten kommen werden?

MfG Christian

T
2.228 Beiträge seit 2008
vor 3 Jahren

@ChrisProg
Der DataReader ist schneller, da man die Daten einfach selbst in einem Objekt materialisieren kann.
Der DataAdapter führt hier zusätzliche Operationen aus, die viel aufwändiger sind als stumpfes durchlaufen des DataReaders.
Beispiele dafür und auch Vergleiche mit entsprechenden Benchmarks gibt es auch unmengen.

Ansonsten kann man einfach per Count in der DB prüfen wieviele Daten eingeladen werden sollen.
Wenn die Datenmenge dann zu groß ist, kann man die Operation zum einlesen der Daten einfach überspringen.

Nachtrag:
Mir ist nicht ganz klar was du mit Batch meinst.
Die Commands werden in einer eigenen Transaktion ausgeführt, wenn du nicht explizit eine eigene Transaktion auf machst.
Wenn du mit Batching, das sammeln in einer Transaktion meinst, dann musst du dir über die SqlConnection eine eigene Transaktion öffnen und den entsprechenden Commands diese zuweisen.
Kann man auch via Konstruktor von SqlCommand direkt machen.
Ansonsten wird jede Abfrage als einzelne Transaktion ausgeführt.
In deinem Fall wird vermutlich das gesamte Skript in einem Command gesendet, entsprechend wird hier der Befehl in einer Transaktion durch das Command ausgeführt.

T-Virus

Developer, Developer, Developer, Developer....

99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.

T
433 Beiträge seit 2006
vor 3 Jahren

Hi,

vereinfacht erklärt, erstellt der SQL Server für jedes Query einen Plan (Compiled Execution Plan). Dieser beinhaltet z.B. welche Objekte (Tabellen, Spalten etc) benötigt werden und wie diese miteinander agieren, damit du dein Ergebnis bekommst. Dieser Plan beinhaltet auch ob und welche Indizes genutzt werden sollen und wieviele Datensätze pro Tabelle vorraussichtlich geholt werden müssen.
Das letztere ist nur eine Schätzung auf Grund der Statistiken, welcher der SQL Server bei jeder Tabelle hinterlegt hat. (Die Kardinalität ist hier der entscheidende Faktor)
Auf Grund dieses Plans reserviert der SQL Server auch eine gewisse Menge an RAM, welches explizit dem Query zur Verfügung steht.

Wenn du jetzt aber sagst du hast 30 Parameter, dann vermute ich, dass diese wohl einen großen Einfluss auf den eigentlichen Ablauf des Querys haben.
Z.B. könnte ich mir vorstellen das aus einer TabelleA nur 1 Datensatz benötigt wird und bei einem neuen Lauf mit geänderten Parameter benötigt man vielleicht 10.000 Datensätze.

Mein Tipp ist, wenn das Query schnell genug ist und es wird mit OPTION (RECOMPILE) behoben, dann lass es so.
Wenn nicht, dann solltest du dir Gedanken machen wie du das große Query in kleinere unterteilen kannst.
Oder vielleicht mal die ganze Architektur überdenken, weil vernünftig hört sich das mit 30 Parameter nicht an 😉

Wegen Skripte für Performance Fragen empfehle ich meinen Kunden immer die Skriptsammlung von Brent Ozar.
Für dich wäre z.B. https://www.brentozar.com/blitzcache/multiple-plans/ interessant.

Schönen Gruß
Tom

3.825 Beiträge seit 2006
vor 3 Jahren

Außerdem prüfe ich den Hauptspeicherbedarf. Wenn ein Anwender versucht eine Liste mit 100 Millionen Datensätzen anzuzeigen kommt eine entsprechende Fehlermeldung.
Wie kannst Du das denn, wenn doch der Reader strikt vorwarding ist, du also noch gar nicht weißt, wie viele Daten kommen werden?

Meine Applikation ist 32 Bittig und ich erlaube 768 MB Hauptspeichernutzung beim Erstellen des Reports. Bis zu 2048 MB sind möglich.

Wenn man eigene Arrays / Listen benutzt spart man viel Speicher gegenüber einer DataTable, da diese die Zeilen mehrfach speichert.

So auf die Schnelle :


using (SqlDataReader x_reader = x_ermittle_verpackungen_kunde.ExecuteReader())
{
     while (x_reader.Read())
     {
            cBestaende.Rows.Add(...);
            if (HauptspeichernutzungInMegabyte() > 768) { Message("Der Bericht ist zu groß um ihn vollständig auszugeben !"); break; }
            progresslabel.Text = "  Bericht wird erstellt ... " + (index++ * 100 / length).ToString() + " %  ";
            progresslabel.BringToFront();
            Application.DoEvents();
     }
}

"Application.DoEvents();" ist böse, besser einen eigenen Task für die Anzeige erstellen.

Grüße Bernd

Workshop : Datenbanken mit ADO.NET
Xamarin Mobile App : Finderwille Einsatz App
Unternehmenssoftware : Quasar-3

T
2.228 Beiträge seit 2008
vor 3 Jahren

Wobei dein Code keine Schichtenarchitektur verwendet und die Datenhaltung samt UI vermischt, was auch unschön ist.
Wie ermittelst du den Speicherverbrauch an der Stelle?
Ansonsten solltest du für die Zahl auch eine Konstante anlegen und verwenden.

T-Virus

Developer, Developer, Developer, Developer....

99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.

3.825 Beiträge seit 2006
vor 3 Jahren

Ja, ist quick and dirty.

Eine saubere Trennung ist besser.


public double HauptspeichernutzungInMegabyte()
{
	return Environment.WorkingSet / 1024 / 1024;
}

Gilt für 32-Bit Applikationen.

Grüße Bernd

Workshop : Datenbanken mit ADO.NET
Xamarin Mobile App : Finderwille Einsatz App
Unternehmenssoftware : Quasar-3

ChrisProg Themenstarter:in
174 Beiträge seit 2009
vor 3 Jahren

(...)Wenn du jetzt aber sagst du hast 30 Parameter, dann vermute ich, dass diese wohl einen großen Einfluss auf den eigentlichen Ablauf des Querys haben.
Z.B. könnte ich mir vorstellen das aus einer TabelleA nur 1 Datensatz benötigt wird und bei einem neuen Lauf mit geänderten Parameter benötigt man vielleicht 10.000 Datensätze.

genau so ist es - die Parameter sind vereinfacht gesagt von/bis-Eingrenzungen

(...)Mein Tipp ist, wenn das Query schnell genug ist und es wird mit OPTION (RECOMPILE) behoben, dann lass es so. (...)

Ich werde es mal testeten u. berichten ...

(...)Wenn nicht, dann solltest du dir Gedanken machen wie du das große Query in kleinere unterteilen kannst.
Oder vielleicht mal die ganze Architektur überdenken, weil vernünftig hört sich das mit 30 Parameter nicht an 😉(...)

Ich habe (bis jetzt) die Erfahrung gemacht, das es immer noch schneller ist, ein großes SQL-Script zu benutzen, als die Daten unter c# zu verarbeiten / zusammen zu führen..
(was sich dann jetzt wohl durch den Speicherhunger von DataTables teilweise erklärt...)

(...)Wegen Skripte für Performance Fragen empfehle ich meinen Kunden immer die Skriptsammlung von Brent Ozar.
Für dich wäre z.B.
>
interessant.

Danke (werde ich mir mal in einer ruhigen Minute zu Gemüte führen)...

MfG Christian