Increase SQL Server stored procedure performance

 Increase SQL Server stored procedure performance



1. SET NOCOUNT ON
This help to stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.

This can reduce network traffic.

2.Use return values


3.Don't write select * from [tablename]

write select [columnname1],[columnname2] from [tablename]
This helps to speed of the query.


4.Don't use Prefix "Sp_" in your store procedure.

Becoz if you use "Sp" then SQL Server looks in the master database then your database.


5.Use sp_executesql stored procedure instead of the EXECUTE statement.


6.Avoid using temporary tables inside your stored procedure.

Becoz Using temporary tables inside stored procedure reduces the chance to reuse the execution plan.


7.Avoid using DDL (Data Definition Language) statements inside your stored procedure.

Using DDL statements inside stored procedure reduces the chance to reuse the execution plan.