SQL SERVER Tutorials

Step by Step ways to increase the performance of Stored Procedure in SQL Server

In this article, let us see the various ways to increase the performance of Stored Procedure in SQL Server.

1. First plan your requirement.

2. Select your columnnames for that table instead of simply giving * in your select statements. Because if you use * it will bring all columns from that table and it is not necessary for your requirement.It also takes more time to retrieve records from the server.
Good & Efficient: 

 SELECT FirstName, LastName, Address, City, State, ZipFROM Users

3.Create joins with INNER JOIN and dont use WHERE CLAUSE 

 SELECT Student.StudentID, Student.Name, Course.CourseNameFROM Students, Course
WHERE Students.StudentID = Course.StudentID
The above query will create Cartesian join with all combinations. Suppose we have 100 students with 20 courses, the query will generate 2000 records then filter for 100 records joined with studentid. To prevent creating a Cartesian Join, use INNER JOIN instead: 
 SELECT Student.StudentID, Student.Name, Course.CourseNameFROM Students INNER JOIN Course
ON Students.StudentID = Course.StudentID

4.Use WHERE clause instead of HAVING clause for FILTERS 

 SELECT Student.StudentID, Student.Name, Course.CourseNameFROM Students INNER JOIN Course
ON Students.StudentID = Course.StudentID
 GROUP BY Student.studentid
 HAVING coursejoiningdate between '#01/01/2021' and '#31/08/2021'
The above query will pull all records from courses and then filter based on the coursejoiningdate for the year 2021. Instead we can use like this, 
 SELECT Student.StudentID, Student.Name, Course.CourseNameFROM Students INNER JOIN Course
ON Students.StudentID = Course.StudentID
 WHERE coursejoiningdate between '#01/01/2021' and '#31/08/2021'
 GROUP BY Student.studentid
Here it will bring first the results based on the coursejoiningdate filter.

5.Don't use WHERE clause for joins instead use INNER JOIN 

 SELECT Student.StudentID, Student.Name, Course.CourseNameFROM Students, Course
WHERE Students.StudentID = Course.StudentID
The above query will create Cartesian join with all combinations. Suppose we have 100 students with 20 courses, the query will generate 2000 records then filter for 100 records joined with studentid. To prevent creating a Cartesian Join, use INNER JOIN instead: 
 SELECT Student.StudentID, Student.Name, Course.CourseNameFROM Students INNER JOIN Course
ON Students.StudentID = Course.StudentID