Improve performance of Stored Procedures

Frequently we use the Stored Procedure in our application to perform data manipulation and data query or both. In that case Performance is most importance because best performance is the main concern to develop a successful application. In this post I want to point out what are the things we need to follow to write best stored procedure with performance.

• SET NOCOUNT
  When we executing the select or DML (Insert, Update, and Delete) query in SQL server it return the message that inform to how many row that query affect in database. This information is useful for debugging purpose but definitely it take some time to write that message. It will affect the performance.
  To avoid it we have to Include SET NOCOUNT ON In above the query. For example
  CREATE PROC dbo.ProcName
  AS
BEGIN
SET NOCOUNT ON;
  --Procedure code here
  SELECT column1 FROM dbo.TblTable1
  -- Reset SET NOCOUNT to OFF
  SET NOCOUNT OFF;
GO
END
• Create Clustered and Non-Clustered Indexes
  Practice to create clustered and non clustered index since indexes helps in to access data fastly. But be careful, more indexes on a tables will slow the INSERT,UPDATE,DELETE operations. Hence try to keep small no of indexes on a table.
• Use schema name with object name
  Schema name should be used with all the object we used in stored procedure. Because if we use the object without schema name, in executing time it search all the schema one by one to find the object. So better use Schema with object. For example.
  CREATE PROC dbo.ProcName
  AS
BEGIN
SET NOCOUNT ON;
  --Preferred way querying table
  SELECT column1 FROM dbo.TblTable1
  – Should avoid
  SELECT column1 FROM TblTable1
  --Preferred way to call sp
  EXEC dbo.MyProce
  – Should avoid
  EXEC MyProce
  -- Reset SET NOCOUNT to OFF
  SET NOCOUNT OFF;
GO
END
• 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. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.
• Use IF EXISTS (SELECT 1) instead of (SELECT *)
  Sometimes we need to check the records is already present in table or not for that we use IF EXISTS. For example if the records already exist in table then update otherwise insert. In that case we use IF EXISTS class it return “true” if any records is return by internal query either single value “1” are all columns.
  CREATE PROC dbo.ProcName
  AS
BEGIN
SET NOCOUNT ON;
  --Preferred way querying table
IF EXISTS(select 1 from Batch WHERE BatchName = 'batch1')
  BEGIN
  Update Batch SetBoolParam =0 WHERE BatchName = 'batch1'
END
ELSE
BEGIN
Insert Batch(BatchName) values ('batch1')
END
---- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO
END

• Use TRY-Catch for error handling
  The stored procedure have more than one T-SQL statement .In that case weed to check after every t-SQl statement is any error have. To avoid that we use TRY-Catch
  CREATE PROC dbo.ProcName
  AS
BEGIN
SET NOCOUNT ON;
  BEGIN TRY
Select 1/1
  Select 1/0
  END TRY
BEGIN CATCH
RAISERROR ('Error raised in TRY block.', 16, 1);
  END CATCH
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
  GO
  END
• Use TRY-Catch for error handling
  The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch
  DECLARE @Query VARCHAR (100)
  DECLARE @Age INT
SET @Age = 25
  SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + convert( VARCHAR (3),@Age)
  EXEC (@Query)
  If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,
  DECLARE @Query VARCHAR (100)
  DECLARE @Age INT
SET @Age = 25
  SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + convert( VARCHAR (3),@Age)
  execute sp_executesql @Query N'@Age int' , @Age = 25
  the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance
• Choose Appropriate Data Type
  Choose appropriate SQL Data Type to store your data since it also helps in to improve the query performance. Example: To store strings use varchar in place of text data type since varchar performs better than text. Use text data type, whenever you required storing of large text data (more than 8000 characters). Up to 8000 characters data you can store in varchar.
• Avoid nchar and nvarchar
  Practice to avoid nchar and nvarchar data type since both the data types takes just double memory as char and varchar. Use nchar and nvarchar when you required to store Unicode (16-bit characters) data like as Hindi, Chinese characters etc.
• Avoid * in SELECT statement
  Practice to avoid * in Select statement since SQL Server converts the * to columns name before query execution. One more thing, instead of querying all columns by using * in select statement, give the name of columns which you required
  -- Avoid
SELECT * FROM tblName
  --Best practice
SELECT col1,col2,col3 FROM tblName
• CREATE PROCEDURE with WITH RECOMP
  The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure because in this case the wrong execution plan will not be used.