Der Parameter funktioniert nicht so gut wie der Wert
Ich habe eine gespeicherte Prozedur, die furchtbar funktioniert. Wenn ich eine Variable deklariere, setze ihren Wert und verwende sie dann in der WHERE-Klausel, deren Ausführung über eine Stunde dauert. Wenn ich die Variablen in der where-Klausel fest codiere, läuft sie in weniger als einer Sekunde.
Ich habe angefangen, durch Ausführungspläne herauszufinden, was damit nicht stimmt. Es sieht so aus, als wenn ich einige deklarierte Variablen versuche und übergebe, kapselt der Ausführungsplan Hash Match, weil er Werte aus einer View auswählt, die einen UNION und einen allgemeinen Tabellenausdruck verwendet.
%Vor% Wenn ich die Stored Procedure mit den eingestellten Werten starte, dauert es mehr als eine Stunde und hier ist der Ausführungsplan.
Wenn ich die Stored Procedure unter Verwendung der DECLARE- und SET-Werte lösche und die Where-Klausel auf die folgende Anweisung setze, läuft sie in weniger als einer Sekunde und hier ist der Ausführungsplan:
%Vor% 
Beachten Sie, wie die fest codierten Variablen die Indizierung verwenden, während die erste einen Hash-Satz verwendet. Warum das? Warum arbeiten fest codierte Werte in der where-Klausel anders als die deklarierten Variablen?
------- Dies wird schließlich mit Hilfe von @ user1166147 ------
ausgeführtIch habe die gespeicherte Prozedur geändert, um sp_executesql zu verwenden.
%Vor%2 Antworten
ZUSAMMENFASSUNG BEARBEITEN Nach einer Anfrage von Damien_The_Unbeliever
Das Ziel besteht darin, die besten / meisten Informationen über den Variablenwert zu SQL zu erhalten, BEVOR der Plan erstellt wird. Im Allgemeinen wird dies durch Parameter-Sniffing durchgeführt. Es kann einen Grund dafür geben, dass das Parameter-Sniffing in diesem Fall deaktiviert wurde. Ohne eine bessere Darstellung des tatsächlichen Codes zu sehen, können wir nicht wirklich sagen, was die Lösung ist oder warum das Problem existiert. Probieren Sie die folgenden Schritte aus, um die betroffenen Bereiche dazu zu zwingen, Pläne mit tatsächlichen Werten zu generieren.
* LANGE VERSION MIT MEHR DETAIL *
Ist das Ihr tatsächlich gespeicherter Prozess? Haben Sie Standardwerte für Ihre Parameter? Wenn ja, was sind sie?
Parameter-Sniffing kann helfen - aber es muss typische Parameterwerte haben, um den Plan gut zu erstellen, und wenn nicht, wird es nicht wirklich helfen oder wird einen schlechten Plan basierend auf dem nicht typischen Parameterwert erzeugen. Wenn also eine Variable einen Standardwert von null oder einen Wert hat, der kein typischer Wert ist, wenn sie das erste Mal ausgeführt wird und der Plan kompiliert wird, wird ein schlechter Plan erstellt.
Wenn jemand anderes diesen Sproc geschrieben hat - hat er aus irgendeinem Grund absichtlich das Parameter-Sniffing mit den lokalen Variablen deaktiviert. Geschäftsregeln erfordern möglicherweise diese variablen Strukturen.
Das Ziel besteht darin, die besten / meisten Informationen über den Variablenwert in SQL zu erhalten, BEVOR der Plan erstellt wird, und im Allgemeinen führt Parameter Sniffing dies aus. Aber es gibt Dinge, die dazu führen können, dass die Leistung sich negativ auf die Leistung auswirkt, und vielleicht ist sie deshalb "behindert". Es scheint immer noch, als ob der Plan mit atypischen Werten für die Parameter oder nicht genug Informationen erstellt wird - mit Parameter Sniffing oder nicht.
Versuchen Sie, die Abfrage im Sproc mit Use sp_executesql aufzurufen, um die betroffenen Abfragen auszuführen, und erzwingen Sie, einen Plan für diesen Bereich mit den tatsächlichen Variablen zu generieren, und sehen Sie, ob es besser ist. Dies kann Ihre Lösung sein, wenn Sie einen solchen unregelmäßigen Parameterwert haben müssen - erstellen Sie gespeicherte Prozeduren, die die betroffenen Teile ausführen und später innerhalb der gespeicherten Prozedur aufrufen, nachdem die Variable einen typischen Wert erhalten hat.
Ohne eine bessere Darstellung des tatsächlichen Codes zu sehen, ist es schwer zu erkennen, wo das Problem liegt. Hoffentlich hilft diese Info -
Sie können es zwingen, Ihre Abfrage basierend auf den typischen Werten zu optimieren, die Sie vielleicht besser kennen. Fügen Sie Ihrer ursprünglichen Abfrage Folgendes hinzu:
%Vor%Es hätte ähnliche Auswirkungen ohne dynamisches SQL. Wenn Sie die typischen Werte nicht kennen, können Sie sie vom Optimierer schnüffeln lassen:
%Vor%Auch hier kein dynamisches SQL
Tags und Links sql sql-server sql-server-2005 stored-procedures tsql