1

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

Tripz
  • 23
  • 1
  • 4

2 Answers2

1

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.

Joel E Salas
  • 5,562
  • 15
  • 25
  • 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
1

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.

Chris McKeown
  • 7,128
  • 1
  • 17
  • 25