Tips To Increase SQL Server Stored Procedure Performance

Step 1

Use SET NOCOUNT ON

This statement is used to stop the message, which shows the number of rows affected by SQL statement like INSERT, UPDATE and DELETE.

Ex.

 
It will remove this extra overhead from the network.


Step 2

 

Use schema name before objects.

It helps SQL Server to find the object.

Ex. SELECT EmpID, EmpName, EmpSalary FROM dbo.Employee

If you want to change the schema, you can change.

IF(NOT EXISTS(SELECT 1 FROM sys.schemas WHERE name = 'emp'))
BEGIN
EXEC('CREATE SCHEMA [emp] AUTHORIZATION [dbo]')
END
ALTER SCHEMA emp
TRANSFER dbo.Employees

It will improve the performance of the stored procedure

Step 3

Use EXISTS () instead of COUNT ().

This SQL optimization technique concerns the use of EXISTS (). If you want to check if a record exists, use EXISTS () instead of COUNT (). While COUNT () scans the entire table. This will give you better performance.

Ex. SELECT Count(1) FROM dbo.Employee

Ex. IF ( EXISTS (SELECT 1 FROM db.Employees))

BEGIN

END

Step 4

Don’t use functions in the WHERE clause, they reduce performance

While writing select query, if you use the function in where condition, it will reduce the performance of your query. Try to avoid the function in where clause.

Step 5

Use NO LOCK

Use NOLOCK will improve the performance of the select query

SELECT EmpID,EmpName,EmpSalary FROM dbo.Employee WITH(NOLOCK) WHERE Convert(varchar(12),ip.CreatedDate,101)=Convert(varchar(12),GETDATE(),101)

Step 6

Specify column names instead of using * in SELECT statement

Try to avoid *

SELECT * FROM dbo.Employee WITH(NOLOCK) WHERE Convert(varchar(12),ip.CreatedDate,101)=Convert(varchar(12),GETDATE(),101)

Best practice is to specify the column name.

SELECT EmpID,EmpName,EmpSalary FROM dbo.Employee WITH(NOLOCK) WHERE Convert(varchar(12),ip.CreatedDate,101)=Convert(varchar(12),GETDATE(),101)

Step 7

Try to avoid temp temporary table

Temporary tables usually increase a query’s complexity. It’s suggested to avoid the temporary tables.

Step 8

Try to create Proper Index

Proper indexing will improve the speed of the operations in the database.

Step 9

Try to use Join query instead of sub-query and co-related subquery

Using JOIN is better for the performance than using subqueries or nested queries

Also, use minimum JOINS (as required) and check whether proper JOIN is used or not.