Der kostenlose SQL Server-Newsletter von TechRepublic, der jeden Dienstag zugestellt wird, enthält praktische Tipps, mit denen Sie sich mit diesem leistungsstarken relationalen Datenbankverwaltungssystem vertraut machen können. Abonnieren Sie noch heute automatisch!Die allgemein akzeptierte Weisheit über gespeicherte Prozeduren (oder Sprocs) ist, dass SQL, da SQL sie optimieren und kompilieren kann, schneller ausgeführt wird als die entsprechenden SQL-Anweisungen, die von QueryAnalyzer ausgeführt werden (oder möglicherweise von einer Front-End-App wie einer Webseite oder VBprogram ). Dies gilt bis zu einem gewissen Grad.
Zuerst müssen Sie wissen, was SQL Server mit einem neuen Sproc macht. Zum Zeitpunkt der Erstellung wird die Syntax überprüft. Wenn es keine Fehler findet, fügt es den Sproc zu den Systemtabellen hinzu: sysobjects, sysdepends und syscomments (letzterer speichert den Hauptteil des Sproc). Standardmäßig wird der Sproc zum Zeitpunkt der Erstellung nicht kompiliert.
Bei der ersten Ausführung des Sproc optimiert und kompiliert SQL Server es. In diesem Fall erstellt SQL Server einen Abfrageplan und speichert ihn in seinem Prozedurcache. Bei nachfolgenden Aufrufen sucht SQL zuerst im Cache, findet dort den Sproc und kompiliert ihn nicht. Wenn sich der Sproc nicht im Cache befindet, kompiliert SQL Server ihn und legt ihn im Cache ab.
Meine Erfahrung mit der WITH RECOMPILE Option
Vor einiger Zeit unterstützte ich eine Suchseite, die es Benutzern ermöglichte, nach einer von mehreren Spalten zu suchen. Dann rief die Seite einen Sproc auf und übergab einen Parameter, um anzugeben, welche Spalte gesucht werden soll. Ich habe den Parameter mithilfe eines CASE-Blocks untersucht und dann eine von mehreren Abfragen ausgeführt, abhängig von der zu durchsuchenden Spalte.
Ich wusste, dass etwas nicht stimmte, als ich anfing, meine gespeicherte Prozedur allegedlyclever zu testen. Theoretisch sollte die Leistung jeder Suche mindestens ungefähr gleich sein, aber das ist nicht das, was passiert ist. Wenn ich mehrere Suchvorgänge unabhängig von der Reihenfolge durchführte, war die erste schnell, und nachfolgende Suchvorgänge waren viel langsamer.
Schließlich wurde mir klar, dass beim ersten Aufruf der Prozedur ein Abfrageplan erstellt und im Cache gespeichert wurde. Solange ich in dieser bestimmten Spalte suchte, würde alles wie erwartet funktionieren. In dem Moment, als ich die Spalten wechselte, brach die Leistung jedoch ein. Warum ist das passiert?
Die erste Suche, die ich durchgeführt habe, hat einen Abfrageplan erstellt und storedit im Cache gespeichert. Angenommen, ich habe in der Spalte OrderDate gesucht. Wenn ich die Suche auf die Spalte CompanyName umstellte, verwendete SQL blind den zwischengespeicherten Queryplan und suchte mithilfe des OrderDateindex nach dem Namen des Zielunternehmens. Kein Wunder, dass die Leistung so dramatisch sinken würde.
Die Lösung ist recht einfach. Ich habe den Befehl ausgeführt, indem ich die Option WITH RECOMPILE :
EXEC MySproc_Select '12/31/2004' WITH RECOMPILE
Dies weist SQL Server an, den vorhandenen Abfrageplan wegzuwerfen und einen anderen zu erstellen – aber nur einmal.
Sie können das WITH RECOMPILE auch direkt vor dem AS Schlüsselwort zum storedprocedure hinzufügen. Dies weist SQL Server an, den Ausführungsplan bei jeder Ausführung des Sproc zu verwerfen.
Es gibt noch eine dritte Option. Ich hätte für jede Suchmethode eine separate Sproc erstellen und dann entscheiden können, welche innerhalb des Fallblocks ausgeführt werden soll. Auf diese Weise verbleibt der den Sub-Sprocs zugeordnete Abfrageplan im Cache, wo SQL sie nutzen kann. Da jeder der sprocssearched genau eine Spalte, gibt es keine Notwendigkeit, neu zu kompilieren.
Die Fähigkeit von SQL Server, ein storedprocedure zu optimieren und zu kompilieren, ist großartig, aber wenn Sie nicht vorsichtig sind, kann es Sie beißen, wenn Sie es am wenigsten erwarten. Nachdem Sie nun wissen, wie Sie mit dem Problem umgehen sollen, gibt es möglicherweise einige Situationen in Ihrer eigenen Datenbank, die Sie möglicherweise erneut aufrufen möchten.