Top 10 Tips to Improve SQL Query Performance
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
- SELECT * FROM Employee
- 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
- SELECT * FROM Employee
- 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:-
- In the Isdelete column, we need to set only boolean (true, false) value if the record is deleted or not.
- In the Creation Date column, we need to set the DateTime datatype.
- 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/
Leave a Reply
Want to join the discussion?Feel free to contribute!