Abfrage auf SQL-Server nur durch Parameter extrem langsam

[RolandF]

Hallo,

bei einer Abfrage auf eine SQL-Server 2008 Datenbank mit sehr vielen Messwerten, ist mir ein merkwürdiges Verhalten bei Abfragen über ADO.NET aufgefallen.

Wenn ich in einer Abfrage Parameter übergebe, ist die Ausführung um den Faktor 100 langsamer

Hier zunächst der SQL-String der Abfrage:

SELECT MAX(Timestamp) AS Datum, MAX(Value) AS Value
FROM tblValue
WHERE (Timestamp BETWEEN @DatumVon AND @DatumBis) AND (Tag_ID = @TagId)
GROUP BY DAY(Timestamp), MONTH(Timestamp), YEAR(Timestamp)

Zunächst hatte ich diese Abfrage in einem Dataset um die Daten per Fill zu laden. Nachdem ich immer wieder in einen Timeout gelaufen bin, dachte ich es liegt an dem typisierten Dataset, da die gleiche Abfrage direkt im SQL-Managementstudio in einer Sekunde ausgeführt wurde.
Also habe ich einen CommandReader mit dem gleichen SQL-String erstellt.
Ergebnis auch langsam.
Wenn ich probeweise in dem CommandReader im SQL-String die Parameter direkt durch konstante Werte ersetze ist das Ergebnis wieder in einer Sekunde da.
Hier der entsprechende SQL-String:

SELECT MAX(Timestamp) AS Datum, MAX(Value) AS Value
FROM tblValue
WHERE (Timestamp BETWEEN '1/1/2012' AND '4/1/2012') AND (Tag_ID = 11)
GROUP BY DAY(Timestamp), MONTH(Timestamp), YEAR(Timestamp)

Hier ein Auszug aus dem Quellcode:
[highlight=vbnet]
' Beispielcode:

cmd = New SqlCommand

strSQL = "SELECT MAX(Timestamp) AS Datum, MAX(Value) AS Value "
strSQL += "FROM tblValue "
strSQL += "WHERE (Timestamp BETWEEN @DatumVon AND @DatumBis) AND (Tag_ID = @TagId) "
strSQL += "GROUP BY DAY(Timestamp), MONTH(Timestamp), YEAR(Timestamp) "


par = New SqlParameter("DatumVon", SqlDbType.DateTime)
par.Value = dtpVon.Value
cmd.Parameters.Add(par)

par = New SqlParameter("DatumBis", SqlDbType.DateTime)
par.Value = dtpBis.Value
cmd.Parameters.Add(par)

par = New SqlParameter("TagId", SqlDbType.Int)
par.Value = cmbTags.SelectedValue
cmd.Parameters.Add(par)

cmd.CommandText = strSQL
cmd.Connection = con

rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess )
[/highlight]

Ich könnte natürlich die Parameter per Stringebearbeitung in den SQL-String einfügen, aber dass soll natürlich nicht die Lösung sein. Warum? Das muß und möchte ich hier nicht erklären.

Hat vielleicht jemand eine Idee?

Viele Grüße

Roland

[Ralf Jansen]

Versteht dein Server überhaupt die von dir benutzte Stringdarstellung eines Datetimes? Wenn das Format ungültig ist ist die Abfrage vielleicht nur so schnell weil schnell feststeht das es kein Ergebnis geben wird. (Versuch es mal mit einem ISO8601 Format das versteht der SQL Server immer egal wie der eingestellt ist)

Ansonsten würde ich dich an den Profiler des SQL Servers verweisen. Schau dir an was in beiden Fällen beim Server ankommt und was für ein Ausführungsplan dabei generiert wird.

Warum benutzt du SequentialAccess?

[RolandF]

Hallo Ralf,
danke für die schnelle Antwort!

Versteht dein Server überhaupt die von dir benutzte Stringdarstellung eines Datetimes? Wenn das Format ungültig ist ist die Abfrage vielleicht nur so schnell weil schnell feststeht das es kein Ergebnis geben wird.

das habe ich natürlich überprüft, das Ergebnis ist korrekt, und identisch mit dem Ergebnis, welches ich auch direkt im Management Studio bekomme.

Ansonsten würde ich dich an den Profiler des SQL Servers verweisen. Schau dir an was in beiden Fällen beim Server ankommt und was für ein Ausführungsplan dabei generiert wird.

Sehr gute Idee, über einen kleine Tipp wie ich das mache würde ich mich freuen ;-)

Warum benutzt du SequentialAccess?

Das war nur ein Versuch, etwas zu verändern, ich habe vergessen das wieder aus dem Programmcode zu entfernen.

[Ralf Jansen]

Den Profiler findest du im Management Studio im Menü unter Extras/SQL Server Profiler. Dort einfach eine neue Ablaufverfolgung starten. Bei der Vorlage für die Ablaufverfolgung kannst du 'Tuning' auswählen. Damit hast du schon mal ein Teil der zu profilenden Events. Du solltest dann manuell noch zusätzlich eins oder alle der 'Performance/Showplan irgendwas' Ereignisse hinzufügen. Dann solltest du alle Daten haben um das Problem zu erkennen.

[RolandF]

Habe den Profiler jetzt genutzt:
Das Ergebnis zeigt, dass bei übergabe mit Parameter zusätzlich eine Zeit angehängt wird. Werde jetzt erst mal versuchen das zu verhindern.

Hier die übergeben Commands:

1) Per eigenem SQL (ohne Parameter)

SELECT     MAX(Timestamp) AS Datum, MAX(Value) AS Value
FROM         tblValue
WHERE     (Timestamp BETWEEN '2012-1-1' AND '2012-4-1') AND (Tag_ID = 11)
GROUP BY DAY(Timestamp), MONTH(Timestamp), YEAR(Timestamp)

2) Per SQL mit Parameter:

exec sp_executesql N'SELECT     MAX(Timestamp) AS Datum, MAX(Value) AS Value
FROM         tblValue
WHERE     (Timestamp BETWEEN @DatumVon AND @DatumBis) AND (Tag_ID = @TagId)
GROUP BY DAY(Timestamp), MONTH(Timestamp), YEAR(Timestamp)',N'@DatumVon datetime,@DatumBis datetime,@TagId int',
@DatumVon='2012-01-01 00:00:00',@DatumBis='2012-04-01 00:00:00',@TagId=11

[Ralf Jansen]

Ich denke das macht keinen Unterschied. Ein DateTime ist immer mit Time auch wenn du denn Bestandteil in der Version ohne Parameter nicht angegeben hast und deshalb nicht siehst.

Konntest du etwas an der Ausgabe von ShowPlan erkennen?

[RolandF]

Ich denke das macht keinen Unterschied. Ein DateTime ist immer mit Time auch wenn du denn Bestandteil in der Version ohne Parameter nicht angegeben hast und deshalb nicht siehst.

Konntest du etwas an der Ausgabe von ShowPlan erkennen?

Du hast Recht, auch ohne Zeit das gleiche Problem! jetzt hatte ich einmal den Parametertyp geändert dann kam folgendes am Server an:

exec sp_executesql N'SELECT     MAX(Timestamp) AS Datum, MAX(Value) AS Value
FROM         tblValue
WHERE     (Timestamp BETWEEN @DatumVon AND @DatumBis) AND (Tag_ID = @TagId)
GROUP BY DAY(Timestamp), MONTH(Timestamp), YEAR(Timestamp)',N'@DatumVon date,@DatumBis date,@TagId int',
@DatumVon='2012-04-05',@DatumBis='2012-04-12',@TagId=11

Und wieder Timeout

Konntest du etwas an der Ausgabe von ShowPlan erkennen?

Bisher habe ich nur die Spalte TextData des Traces genutzt. Die anderen Spalten Duration, CPU etc. geben natürlich bei dem Fall mit Timeout höhere Werte, das habe ich ja auch erwartet. Oder denkst Du an eine bestimmte Spalte, die interessant sein könnte?

Jetzt stehe ich zunächst mal wieder Ahnungslos da.

Ab schon mal vielen Dank!

[Ralf Jansen]

Ich meine nicht eine bestimmte Spalte sondern einen anderen Event.

Wenn aus irgendeinem Grund ein anderer Ausführungsplan gewählt wurde (nimmt andere Indizes oder keinen) sollten wir das da sehen.

[RolandF]

Ich meine nicht eine bestimmte Spalte sondern einen anderen Event.

Wenn aus irgendeinem Grund ein anderer Ausführungsplan gewählt wurde (nimmt andere Indizes oder keinen) sollten wir das da sehen.

Ah, ich hatte nicht das Template Tuning verwendet und dadurch weniger Events gesehen.
Jetzt werde ich erst mal von Events erschlagen. Da muss ich mich jetzt mal in Ruhe durcharbeiten. Auf den ersten Blink ist mir noch nichts aufgefallen.

Ich bin wirklich begeistert von Deiner Hilfe, danke nochmal.

[RolandF]

Kleines Update:

Ich hatte doch das richtige Template verwendet, nur hatte ich auf meinen Loginnamen gefiltert.
Beim zweiten Versuch hatte ich das Filter auf meinen Loginnamen vergessen und aus diesem Grund so viele Events gesehen. Die haben aber nichts mit meiner Transaktion zu tun.

Gefiltert auf meinen User kommen nur zwei Events an: Trace Start und RPC:Completed.
Ich glaube ich überschlafe das Problem erst mal, morgen ist auch noch ein Tag

[Ralf Jansen]

Kann dein Problem nicht ganz nachvollziehen. Aber du hattest die 'Alle Ereignisse anzeigen' Checkbox gesehen?

[RolandF]

Kann dein Problem nicht ganz nachvollziehen. Aber du hattest die 'Alle Ereignisse anzeigen' Checkbox gesehen?

Ja hatte ich natürlich gesetzt. Es kamen ja auch für andere user andere Events an.
Mich interessierten aber nur die Events meines Users, und das waren nur die wenigen.

Das Problem hat sich zum Glück inzwischen gelöst.
Durch den Befehl "EXEC sp_updatestats" im Managemantstudio war das Problem gelöst.
Darauf bin ich aber nicht selbst gekommen, und ich kann mir bis jetzt noch nicht einmal die Hintergründe erklären. das werde ich machen wenn ich mal wieder Zeit habe.

Aber Dir noch einmal vielen Dank, jetzt kenne ich auch den Profiler.

Grüße
Roland