SQL SERVER – Stored Procedure Optimization

Monday, 17 October 2011

SQL SERVER – Stored Procedure Optimization

1)Use schema name with object name
eg. Select * from dbo.tablename

2)Do not use the prefix “sp_” in the stored procedure name:
If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database.

3)Use IF EXISTS (SELECT 1) instead of (SELECT *):
Always use select 1 instead of select * because select * takes all column.

4)Keep the Transaction as short as possible:
The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction.

5)Try to avoid using SQL Server cursors whenever possible:
Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance.

No comments:

Post a Comment