Stored Procedure langsamer als Abfrage

[Julia Kuhlmann]

Hallo,

wie kann es wohl sein, dass eine stored procedure auf einem MS-SQL-Server 2000 langsamer ausgeführt wird als dieselbe (zugegebenermassen ziemlich aufwendige) Abfrage?

Ich habe mit Delphi + ADO eine ziemlich komplizierte Procedure geschrieben, um temporäre Tabellen zu füllen (einige joins und subselects). Dabei liegen die temp. Tabellen in einer anderen Datenbank auf demselben Server. Das lief auch zufriedenstellend.
Nun hat die Datenmenge zugenommen (wg. Jahresende) und die Procedure macht kein Insert mehr, sondern meldet sich irgendwann mit timeout.

Im Query Analyzer habe ich die Stored Procedure nach 4 Minuten abgebrochen.
Nach Kopie der Abfrage in den Analyzer ohne Stored Procedure wird diese einwandfrei ausgeführt.
In Delphi mit der AdoCommand-Komponente ist das auch nachzuvollziehen.

Woran kann es liegen, dass die Stored Procedure sich weghängt?!
Der Profiler wirft keine Fehler aus.

Grüße

Julia

[hwoess]

Woran das liegt, kann ich dir jetzt auch nicht sagen, aber versuche doch mal nur einen kleinen Teil der stored proc auszuführen (Teile auskommentieren, beim select mit top 5 arbeiten usw.) um dich so schrittweise an das Problem heranzuarbeiten.

bye,
Helmut

[Andreas Kosch]

Hallo,

wie kann es wohl sein, dass eine stored procedure auf einem MS-SQL-Server 2000 langsamer ausgeführt wird als dieselbe (zugegebenermassen ziemlich aufwendige) Abfrage?

Der Grund für dieses Verhalten liegt am Zeitpunkt, zu dem der Optimizer des SQL Server den effizientesten Ausführungsplan sucht. Wenn die Stored Procedure nicht mit der Option WITH RECOMPILE gekennzeichnet wird, ermittelt der Optimizer den Ausführungsplan nur beim 1. Aufruf der SP. Wenn bei nachfolgenden Aufrufen die WHERE-Kritieren so geändert werden bzw. sich die Zusammensetzung des Datenbestandes so geändert hat, dass sich eine andere Gewichtigung ergibt, verwendet der SQL Server beim Ausführen den SP den "falschen Weg".

Je nach der konkreten Situation helfen die folgenden Wege weiter:

• WITH RECOMPILE
• OPTION(RECOMPILE)
• OPTION (KEEPFIXED PLAN)
• OPTION OPTIMIZE FOR
• sp_create_plan_guide

Nun hat die Datenmenge zugenommen (wg. Jahresende) ...

In diesem Fall wird es ausreichen, über sp_recompile den SQL Server zu beauftragen, die Stored Procedure beim nächsten Aufruf erneut zu "kompilieren" (d.h. im Optimizer zu bewerten).