How to optimize the store procedure in SQL? [Resolved]

It depends on the purpose of stored procedure. while writing stored procedure you have to consider the following things:
1. Avoid manipulations on columns in the WHERE condition
2. Find the Most Costly Statements and then try to optimize the costly running part of query
3. Do not use the prefix “sp_” in the stored procedure name
4. Use IF EXISTS (SELECT 1) instead of (SELECT *)
5. Use the sp_executesql stored procedure instead of the EXECUTE statement
6. Try to avoid using SQL Server cursors whenever possible
7. Keep the Transaction as short as possible
8. Use TRY-Catch for error handling
9. Include SET NOCOUNT ON statement
10. Use schema name with object name

refernces:
http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/
Sample: http://stackoverflow.com/questions/17969864/need-tips-to-optimize-sql-server-stored-procedure

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Programmer123, if this helps please login to Mark As Answer. | Alert Moderator

You can also use the
1. Actual execution pale and
2. Estimated execution plan
to optimize the store procedure

See the links:-
http://sqlserverplanet.com/optimization/how-to-optimize-a-stored-procedure-using-the-execution-plan
http://voltdb.com/docs/PerfGuide/ExecPlansRead.php

Programmer123, if this helps please login to Mark As Answer. | Alert Moderator

More tips:-

1. Utilize indexes
2. Find common queries
3. Always go SET based - This means, never use while loops or cursors.
4. Do Not Use Scaler UDF’s - Scaler user defined functions make a query run as if they were in a loop.
5. Use Exists instead of JOIN
6. Limit the Select list - Returning too many columns can have a drastic effect on your query.
7. Learn execution plans
8. Avoid poor performing techniques
-> CTE’s – Common table expressions:- Instead of using CTE’s use temp tables. They perform badly. They do have a good use, and that is recursion.
-> Table Variables:- If you have under 1000 rows, consider it. Otherwise, I suggest #temp tables.

Programmer123, if this helps please login to Mark As Answer. | Alert Moderator

Please refer:-

http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/
http://stackoverflow.com/questions/17969864/need-tips-to-optimize-sql-server-stored-procedure
http://www.techrepublic.com/article/increase-sql-server-stored-procedure-performance-with-these-tips/
http://technet.microsoft.com/en-us/library/cc917719.aspx
http://www.google.co.in/url?sa=t&rct=j&q=&esrc=s&source=web&cd=5&cad=rja&sqi=2&ved=0CEMQFjAE&url=http%3A%2F%2Fwww.sql.ru%2Fphotos%2Ftech-ed03%2Fppt%2Fdba322.ppt&ei=TQ-5Uuz1DoOVrAff8YEw&usg=AFQjCNFZH8kRtWPz6S2ca7C-Lh2M6Ghr0Q&bvm=bv.58187178,d.bmk

Programmer123, if this helps please login to Mark As Answer. | Alert Moderator