Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a stored procedure implemented and calling of this stored proc from web page is taking more than 300ms, But when write the script in the web page and call the url, it is executing in 50ms

What are the steps that are required to reduce the time to execute a stored proc. Why is sp taking more time than the db script in the page

share|improve this question

2 Answers 2

The query plan is your window "under the hood" to see SQL Server's approach to running your queries (including stored procedures). See what the query plan is doing and optimize appropriately using indexes and updating statistics.

You can also use SQL Server Profiler to optimize queries. Run a trace using the "Replay" template, then feed the trace into Database Engine Tuning Advisor. This will give (and can implement) suggestions regarding database metadata that will allow SQL Server to run at its best.

share|improve this answer
    
I tried sql server profile and used the suggestions but it did not help much. Not sure what else to do improve the performance. Any suggestion are welcome –  Tripz Apr 26 '12 at 20:42

Stored procedures get compiled and stored in the procedure cache when they're executed for the first time - using the parameters that were supplied at the time.

So, the cached execution plan may not be optimal for the set of parameters that you're currently testing with. Try a dbcc freeproccache and test again.

If that works then you may want to consider adding a WITH RECOMPILE hint to the stored procedure to force a recompile on each execution.

share|improve this answer
    
"dbcc freeproccache" - did not help me :-(, my stored proc is taking lott of time, not sure how to reduce the execution time –  Tripz Apr 26 '12 at 20:37
    
Is your SP and the script you have written in a webpage exactly the same SQL? –  Chris McKeown Apr 26 '12 at 20:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.