Store procedure slower than script

[almirjr]

Hi,

I have a store procedure wich takes more than 1 min. to process. But if I declare the variables as the parameters specifications and run it as a script in query analyser, it tooks less than 5 seg.s.

well, the sql-script is the same, the variables used at sql-script are the same....

what can be the difference so that it took more time to process a sp than a script ?

where can i start to invistage ?

Thanks, Almir.

 

[cheyney]

go into query analyser, select "Show Server Trace" from the query menu.

Run both the stored procedure and the code within the stored procedure from query analyser. Check the "Trace" tab after each execution. This will summarize where execution time is spent. This will be a start to finding where the discrepancy comes from.

Cheyney