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
Best Answer
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.