Stored Procedure Performance Tuning

Introduction

In the Database, we may be using Stored Procedures. Most of the time we are concerned about the performance of the Stored Procedures. In this article, I would like to share a few techniques to improve the stored procedure’s performance to get better results in less time.

Optimization Techniques

SET NOCOUNT ON/OFF – When a Stored Procedure is Created, When we specify the SET NOCOUNT is ON or OFF. This statement controls the number of rows affected in the T-SQL Query.

• SET NOCOUNT OFF – When we declare this at the beginning of the statement, the SQL SERVER shows the number of rows affected in the message panel.
• SET NOCOUNT ON – We do not get the number of rows affected in the Message panel. Always use SET NOCOUNT ON in Stored Procedures.

CREATE QUALIFIED PROCEDURES – When a new procedure is about to create, then create with fully qualified object name as SCHEMA.PROCEDURENAME, so that SQL Server can quickly find the complied plan. Do not create SP name with a prefix as SP_ProcedureName. Its a bad practice and also when SQL Server searches for the Procedure name, it will search first in the master database and then in our database.

IF EXIST & SELECT – IF EXISTS is used to check existence of a record, object etc.. And is a handy statement to improve performance of queries where in one only wants to check existence of a record in a table instead of using that record/row in the query. When doing so use IF EXISTS(SELECT 1 from TableName) instead of IF EXISTS(Select * from TableName) as only thing we are interested in is to check the presence of record/s. So, if the query return 1 then record is present else it’s not. It’s needless to return all column values.

Short Transactions – When we are performing multiple inserts, updates or deletes in a single batch it is required to check that all of the operation either succeeded or failed. In this situation, we should go for the transaction. When we are using transaction in store procedure that time we need to make sure that transaction should be very small, and it should not block other operations. The length of the transaction affects blocking and sometimes it ends up in a deadlock.

Conclusion

In this short article, I have explained about the performance tuning process for a stored procedures. I hope, you all found this article much useful. Please share your feedback in the comment section.