Post
Ask Question

Enhance SQL Server Stored Procedure Performance – Tuning Tips

In this article, I am going to discuss a few tips to improve store procedure performance and give a few points about dos and don'ts while writing store procedures in SQL server. To demonstrate a few examples, I have chosen an employee detail table with a few columns like FirstName, LastName, AddressLine1, and Country.

Pick specific columns in a select statement that you want to use; don't use select.

Here, in the below snippet, I have written two select statements that are used to fetch records from the employee table. The first one is best practices, which means I pick the specific column in the select statement. In the second snippet, I have used select *, but it is not recommended to use this when fetching records from the table.

--- Always do specific columns
SELECT FirstName, LastName, AddressLine1, Country FROM EmployeeDetail

--- Do not Do
SELECT * FROM EmployeeDetail

Do not create your procedure name prefix with sp_

Do not create your procedure name prefix with sp_ always use a different prefix, i.e. usp_YourStoreProcName.

SP_StoreProcName is bad practice because when the SQL server searches for a procedure name, it will first search in the master database, then your database. If you prefix it with SP_, then it will start looking in system procedures, so it will take more time.

-- Do not create like this
CREATE PROCEDURE [dbo].[sp_GetEmployeeDetails]
@EmployeeId int
AS
BEGIN
    SELECT FirstName, LastName, AddressLine1, Country FROM EmployeeDetail WHERE EmployeeId = @EmployeeId
END

-- Always keep prefix different from sp_
CREATE PROCEDURE [dbo].[usp_GetEmployeeDetails]
@EmployeeId int
AS
BEGIN
    SET NOCOUNT ON
    SELECT FirstName, LastName, AddressLine1, Country FROM EmployeeDetail WHERE EmployeeId = @EmployeeId
END

Use If Exists to check if the record exists in the table or not

If Exists function is used to check if a record exists in a table or not. If any records are found, then it will return true; otherwise, it will return false. One benefit of using the If Exists function is that if any match is found, then it will stop execution and return true, so it will not process the remaining records, which will save time and improve performance.

In the below example, I have used the If exists function with select 1 and select *. We should always use select 1 because, for the If Exist function, it doesn't matter how many records there are; it checks for any record, and if it finds one, it will return true.

-- When you want to check if the record is there in the table
-- Do's
Use IF Exists(select 1 from EmployeeDetail where EmployeeId=@EmployeeId)

-- Don'ts
Use IF Exists(select * from EmployeeDetail where EmployeeId=@EmployeeId)

Keep your transaction as short as possible

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

Use try-catch block for error handling

Error handling is a core part of any application. SQL servers 2005 and 2008 have a new, simple way of handling errors using a try-catch block.

BEGIN TRY
    -- SQL Statement
    -- DML Statement
    -- Select Statement
END TRY
BEGIN CATCH
    -- Error handling code
    -- Your logic to handle error
END CATCH

Use schema name with object name

The schema name should be used with the store procedure name because it will help compile the plan. It will not search in another schema before deciding to use the cached plan. So, always prefix your object with the schema name.

-- Always keep prefix different from sp_
Create procedure [dbo].[usp_GetEmployeeDetails]
@EmployeeId int
As
Begin
    SET NOCOUNT ON
    select FirstName, LastName, AddressLine1, Country from EmployeeDetail where EmployeeId=@EmployeeId
End

Set the NOCOUNT ON statement at the beginning of the Store procedure

In the first procedure, I did not use the SET NOCOUNT ON statement, so it printed a message for how many rows were affected. It means if we do not use this, it will print an extra message and it affects performance.

-- Always keep prefix different from sp_
Create procedure [dbo].[usp_GetEmployeeDetails]
@EmployeeId int
As
Begin

select FirstName,LastName, AddressLine1, Contry from EmployeeDetail

End

Messages

In the procedure below, I have the user set NOCOUNT ON so it does not return the how many rows are affected message. This message doesn't matter for limited numbers of records, but when dealing with a large number of records, it matters a lot. So always use the SET NOCOUNT ON statement at the top of your store procedure.

-- USING SET NOCOUNT ON STATEMENT
CREATE PROCEDURE [dbo].[usp_GetEmployeeDetails]
AS
BEGIN
    SET NOCOUNT ON
    SELECT FirstName, LastName, AddressLine1, Contry FROM EmployeeDetail WHERE Contry = 'Ind'
END

Results

Use the Primary key & Index properly in the table

It is good practice to use the primary key for each table and Index in appropriate scenarios. It will speed up retrieving and searching records from the database. Here, we have three snippets to understand a few points about how to select statement works in different scenarios.

In the first screen, I used table Employee1, where I do not have a primary key for the table, and I have to try to retrieve data from the table and then perform the table scan.

SQL server

In the second screen, I have another table, EmployeeDetail, which has the primary key EmployeeId I have tried to retrieve all data from the employee table but it performs an Index scan because it has the primary key. So, it's good to have a primary key for each table. If you create a primary key on the column, it will create a cluster index on that column. The index scan is better than the table scan.

Index scan

In the below screen, you can see I have a primary key column, and I have used a primary key column in the where clause, so it is performing an Index scan, which is better than the above two in performance.

Primary key

A few more points we can keep in mind while writing store procedures.

  1. While writing store procedures, avoid aggregate function in where clause because it reduces performance.
  2. Try to write program logic in the business layer of the application, not in store procedure.
  3. Try to avoid the cursor inside the procedure if it is possible.
  4. Try to avoid DDL operation in store procedures because DDL operation may change the execution plan, so SP can not reuse the same plan.

Thanks for taking your precious time to read this.

Read More


Similar Articles