Improving Stored Procedure performance

This article provides some best practices to improve SP performance, which will improve overall application performance.

There are many more other best practices, which I will discuss in future.

1. Using SET NOCOUNT ON statement
Use SET NOCOUNT ON at the beginning of your stored procedures.When SET NOCOUNT is ON,    the count of effected rows is not returned. This improves the performance of stored procedures by reducing network traffic.

2. Do not use the ‘sp_’ in the stored procedure name 
Do not start the stored procedures name with “sp” because sp_ prefix makes SQL Server look at the master database for a compiled plan. It needs an exclusive compile.

3. Use schema name with object name
This helps finding the data directly instead of searching the other possible schema. 

Select * From dbo.tbl_Customer-- Preferred method

Select * From tbl_Customer -- Avoid this method 

4. Use TRY-Catch for error handling

5. Use IF EXISTS (SELECT 1) instead of (SELECT *)
To minimize the data for processing and network transferring use 1 in the SELECT clause of an internal statement.

IF EXISTS (Select CustomerID From dbo.Tbl_Customer

Where CustomerID = 1 )

6. Do not use those columns in the select statement which are not required. Don’t use select * statement

7. Try to avoid using cursors whenever possible
Cursor uses a lot of resources for overhead processing to maintain current record position in a record set and this decreases the performance.


References:
http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/

http://www.c-sharpcorner.com/blogs/2117/improving-stored-procedure-performance-checklist.aspx