In this article, I will share some tips for improving stored procedure performance and highlight key dos and don'ts when writing stored procedures in SQL Server. To illustrate these concepts, I will use an example based on an employee details table with columns such as FirstName, LastName, AddressLine1, and Country.
Select only the specific columns you need in a query rather than using SELECT *
In the snippet below, 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
Avoid using the prefix sp_ for your stored procedure names
Avoid prefixing your stored procedure names with sp_; instead, use a different prefix, such as usp_YourStoredProcName.
Using sp_ is considered bad practice because SQL Server first searches for procedures with that prefix in the master database before searching in your specific database. This behavior causes unnecessary overhead, as SQL Server prioritizes system procedures when it encounters the sp_ prefix, potentially increasing query execution 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 determine whether a record is present in the table
Suppose the Exists function is used to check whether 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 example below, 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.
Implement a try-catch block to handle errors effectively
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
Include the schema name when referencing an 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
Enable the SET NOCOUNT ON statement at the start of the stored 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. This means if we do not use this, it will print an extra message, Which will affect 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 message about how many rows are affected. 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]()
Ensure proper use of primary keys and indexes in the table
It is good practice to use the primary key for each table and Index in appropriate scenarios. It will speed up the retrieval and searching of records from the database. Here, we have three snippets to help you understand a few points about how select statements work 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 in 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]()
Here are additional considerations to keep in mind when writing stored procedures
- Avoid using aggregate functions in the
WHERE clause, as they can negatively impact performance.
- Aim to implement program logic in the application's business layer rather than within the stored procedure.
- Minimize the use of cursors inside stored procedures whenever possible.
- Avoid performing DDL (Data Definition Language) operations within stored procedures, as they can alter the execution plan, preventing the stored procedure from reusing the same plan effectively.
Thanks for taking your precious time to read this.
Read More