how to speed up execution time of a stored procedure ?

Question

Hi Guys,

Recently I came across an interview question like this. A stored procedure is taking long time to execute.

How to reduce execution time for stored procedure? What are all the steps to be followed to reduce execution time of stored procedure ?.  

Answer 1

First check the execution plan to see if suitable indexes are used. If you see a table/index scan then a suitable index is missing.

Next check the code to see if it's a set based solution and no cursors are used.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Does the stored procedure accept parameters?

Does it use cursors?

How much data does it return?

Show me an execution plan of the query 

also use SET STATISTICS IO,TIME ON

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 3

Just to add to what Olaf and Uri has mention, check if index are there properly created to suite to the SQL statement in the SP.

Answer 4

Should I remove temp tables in stored procedure ?

Answer 5

Should I remove temp tables in stored procedure ?

Maybe you should. Maybe you should break up a big query and introduce a temp table instead.

The correct answer to the interview question is to first identify which statement(s) that takes most time and capture the query plan for this statement and then work from there.

Answer 6

There are several ways by which you can speed up the execution timing of a stored procedure. 

1. use SET NOCOUNT ON :- when you include this statement inside of your stored procedure it will reduce the Network traffic between server and the client
2. Avoid using sp_ at the start of your stored procedure name.