Procedure Optimization Techniques

1. Set nocount on: Sql counts and returns number of records effected sql statement. Sql skip this if we set set nocount on.
2. Use Schema name with objects: Use schema name with objects. Ex. Before execution sql will first try to find sp with this name in all schemas even a cached plan available then it pics the cached plan so it degared performance.
3. Don't use sp_ prefix: sp_ format is used in master database for procedures. For sp_ pattern sql first searches sp in master database then in current session db, this degrade performance.
4. Use 1 instead of column or * : When we need to check whether a record is exist or not.
   IF EXISTS (SELECT * FROM Emp where name like '%Ram%') --Avoid
   IF EXISTS (SELECT 1 FROM Emp where name like '%Ram%') --Recmmonded
5. Use sp_executesql procedure for dynamic queries: This procedure supports parameter. A cached plan can be reused for sql queries also but if there is no change in query (single character) even of spaces. with sp_executesql you can pass parameters so query remain unchaged if you change parameter value
   Ex.
   
   --Avoid
   SET @Salary = 25000
   SET @Query = 'SELECT * FROM dbo.tblEmp WHERE salary > ' + @salary
   EXEC (@Query)
   --Recommended
   SET @Query = 'SELECT * FROM dbo.tblEmp WHERE salary = @salary'
   EXECUTE sp_executesql @Query, N'@salary int', @salary = 25
6. Avoid Cursors: Use while loop wherever possible in place of cursor as cursor consumes more sql resources.
7. Keep Transaction short as short as possible: Transaction uses locks that blocks other executions it degrades performance, so keep transaction shorter.