Top 10 Tips to Improve SQL Query Performance

Best practices for Improve SQL Query Performance. Let’s have a look,

Do not use * in select Statment

If we use * then it gives all columns of the particular table and it takes time so that’s why it’s better to use the column name which is required for the result.

Example:-

SELECT * 
FROM Department - - - Bad Parctice

SELECT Id,DeptName,Description 
FROM Department - - - Good Practice

Use Exists instead of Sub Query

If sometimes need to use subquery at that time first use Exist() function of the SQL server only when the subquery returns large data. In this case, Exist() function works faster than In because Exist() function returns a boolean value based on the query.

Example:-

SELECT Id, Name 
FROM Employee 
WHERE DeptId In (SELECT Id 
FROM Department
WHERE Name like ‘%Management%’) - - - Bad Practice

SELECT Id, Name 
FROM Employee 
WHERE DeptId Exist (SELECT Id 
FROM Department 
WHERE Name like ‘%Management%’) - - - Good Practice

Use Proper join instead of subqueries

It’s better to use join instead of subquery because based on join like left or right query only checked that records which are matched with criteria while in subquery they check all records and then return result so it’s time-consuming that’s why in this case use proper join as per requirement.

Example:-

SELECT Id, Name 
FROM Employee 
WHERE DeptId in (SELECT Id 
FROM Department
WHERE Name like ‘%Management%’) - - Bad Practice

SELECT Emp.Id, Emp.Name,Dept.DeptName 
FROM Employee Emp
RIGHT JOIN Department Dept on Emp.DeptId = Dept.Id
WHERE Dept.DeptName like ‘%Management%’ - - - Good Practice

Use “Where” instead of “Having” a clause

Here if we use “Where” then it will check in all records with the specific condition while if we use “Having” at that time user must need to apply “Group by” and through the “Group by” all data are displayed in group-wise and then after “Having” clause find the data from only that grouping which are filtered by

“Group by“, that’s why it’s executing fastly compare to “Where” while any table has much more data.

Example:-

SELECT Emp.Id, Emp.Name,Dept.DeptName,Emp.Salary
FROM Employee Emp
RIGHT JOIN Department Dept on Emp.DeptId = Dept.Id
WHERE Emp.Salary >= 20000; - - Good Practice

SELECT Emp.Id, Emp.Name,Dept.DeptName,Emp.Salary
FROM Employee Emp
RIGHT JOIN Department Dept on Emp.DeptId = Dept.Id
GROUP BY dept.DeptName
HAVING Emp.Salary >= 20000; - - - Bad Practice

Apply index on necessary columns

An index creates a unique data column without overlapping each other. It improves the speed of data retrieval. While applied index on that time its work like as per the below diagram.

 

For user-defined stored procedures avoid prefixes like “sp_”

As in SQL databases, the master database has some inbuild stored procedures which are starting with “sp_” so whenever we create a user-defined stored procedure at that time we can avoid prefixes like “sp_” because when we use that stored procedure in our code at that time SQL first finds from master database and it takes some extra time that’s why if we give another name instead of “sp_” then it will take less time.

Apply UNION ALL instead of UNION if possible

The major difference between UNION and UNION ALL is that UNION returns distinct records while UNION ALL returns all records including duplicates. While we apply UNION on that time function first apply sort and then find the distinct record and then return a result that’s why it takes more time to compare to UNION ALL. As if we require only distinct records at that time we can use UNION.

Example:-

• If table Employee have following records
  • Ramesh
  • Mahesh
  • Sita
  • Nita
  • Naresh
• If table Employee_new have the following records
  • Sita
  • Gita
  • Ramesh
• While we apply UNION at that time queries like:
  • SELECT * FROM Employee
    UNION
    SELECT * FROM Employee_new
• Result:-
  • Ramesh
  • Mahesh
  • Sita
  • Nita
  • Naresh
  • Gita
• While we apply UNION ALL at that time queries like:
  • SELECT * FROM Employee
    UNION ALL
    SELECT * FROM EMployee_new
• Result:-
  • Ramesh
  • Mahesh
  • Sita
  • Nita
  • Naresh
  • Sita
  • Gita
  • Ramesh

Avoid Negative search

As we know sometimes users are using negative search in where conditions like not equals (<>), not like, etc. but we need to avoid that type of search because if we give the exact search criteria on that time while query executing if that type of data is got then immediately query returns the result. Negative searches decrease the query execution speed that’s why we need to avoid that.

Avoid query in a loop

If sometimes we need to execute the same query multiple times like if we need to insert 10 records in any table at that time don’t use insert query in the loop instead of using bulk insert.

Example:-

For(Int i = 0;i <= 5; i++)
{
INSER INTO Table1(Id,Value) Values( i , ‘Value’ + i );
} - - - Bad Practice

INSERT INTO Table1(Id, Value) 
Values(1,Value1),(2,Value2),(2,Value3),(4,Value4),(5,Value5) - - - Good Practice

Apply valid datatype on the column

As we know sometimes some new users do not know the actual use of datatype and they all apply varchar datatype for all columns but it’s not the right way, as per client requirements we need to identify first which type of data is required and based on that need to apply proper datatype.

Example:-

1. In the Isdelete column, we need to set only boolean (true, false) value if the record is deleted or not.
2. In the Creation Date column, we need to set the DateTime datatype.
3. If the Department column is used as the foreign key in any table at that time we need to store that department’s id only not a name so we can set Integer datatype.

Conclusion (Improve SQL Query Performance):-

In the last, if we take care of the above points then it’s good for all backend developers because small tips are very useful when we face any performance issues.

References:-

https://www.c-sharpcorner.com/UploadFile/ff2f08/tips-to-improve-sql-database-performance/

https://www.winwire.com/blog/improve-sql-query-performance/