Post
Ask Question

Tips To Increase SQL Server Stored Procedure Performance

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.
 
SET NOCOUNT ON 
 
It will remove this extra overhead from the network.
 
SET NOCOUNT ON 
 

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.
  1. IF(NOT EXISTS(SELECT 1 FROM sys.schemas WHERE name = 'emp'))  
  2. BEGIN  
  3. EXEC('CREATE SCHEMA [emp] AUTHORIZATION [dbo]')  
  4. END  
  5. ALTER SCHEMA emp  
  6. TRANSFER dbo.Employees   
It will improve the performance of the stored procedure
 

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
 

Don’t use functions in the WHERE clause

 
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.
 

Use NO LOCK

 
Use NOLOCK will improve the performance of the select query
  1. SELECT EmpID,EmpName,EmpSalary   
  2. FROM dbo.Employee WITH(NOLOCK)   
  3. WHERE Convert(varchar(12),ip.CreatedDate,101)=Convert(varchar(12),GETDATE(),101)

Specify column names instead of using * in SELECT statement

 
Try to avoid *
  1. SELECT * FROM dbo.Employee WITH(NOLOCK)   
  2. WHERE Convert(varchar(12),ip.CreatedDate,101)=Convert(varchar(12),GETDATE(),101) 
Best practice is to specify the column name.
  1. SELECT EmpID,EmpName,EmpSalary   
  2. FROM dbo.Employee WITH(NOLOCK)   
  3. WHERE Convert(varchar(12),ip.CreatedDate,101)=Convert(varchar(12),GETDATE(),101) 

Avoid temp temporary table

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

Create Proper Index

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

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.
 
Next Recommended Reading How To Call A Function In SQL Server Stored procedure