1. Use UNION ALL statement instead of UNION, wherever possible.
UNION ALL statement is quite faster than UNION, because UNION ALL statement does not check duplicate rows, while the UNION statement always check for duplicate rows, whether duplicate record is exists or not.
2. Include SET NOCOUNT ON statement into your stored procedures
Usually T-SQL will show message regarding affected row but with the help of SET NOCOUNT ON statement we can stop these message. This can reduce network traffic, as your client will not receive the message indicating the number of rows affected by a T-SQL statement.
3. DISTINCT clause if really required only.
DISTINCT clause will result in some performance degradation, use this clause only when it is really required another don’t use it.
4. Use constraints instead of triggers, whenever possible.
Constraints are more efficient than triggers and can boost performance. So, whenever possible, use constraints instead of triggers.
5. Use sysindexes for getting record count
Mostly we use select count(*) for fetching record but sysindexes table have every tables row count so you can use this table instead of count(*) query. Some more alternate way exists to fetch row count. You can google it for that. Below query for sysindexes table:-
SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name')
6. Use views and stored procedures instead of large queries.
It can reduce network traffic as your client will send only stored procedures or view name (perhaps with some parameters) to server instead of large queries text. Also it can be used to facilitate permission management, because you can restrict user access to table columns which they should not see.
7. Use DECODE if possible
To avoid the scanning of same rows or joining the same table repetitively. DECODE can also be used in place of GROUP BY or ORDER BY clause.
SELECT id FROM employee
WHERE name LIKE 'Arun%'
and location = 'USA';
SELECT DECODE(location,'USA',id,NULL) id FROM employee
WHERE name LIKE 'Arun%';
8. Reduce Sub query as much as possible.
Sometimes user may have more than one sub queries in main query. Try to minimize the number of sub query block in query. More sub queries will take more time to fetch result.
SELECT name FROM employee WHERE (sal, experience) = (SELECT MAX (sal), MAX (experience) FROM employee_details) AND dept = 'Accounts';
SELECT name FROM employee WHERE sal = (SELECT MAX(sal) FROM employee_details)
AND experience = (SELECT MAX(experience) FROM employee_details) AND emp_dept = 'Accounts';
9. Compare always similar data type values
Always use similar datatype for check or join. If datatype is not same then convert and make common data type. It will improve performance to fetching result.
ON DECIMAL(A.INTCOL, 7, 0) = B.DECICOL
ON A.INTCOL = INTEGER(B.DECICOL)
10. Selected only needed column
While fetching record from query don’t use star. Always use only required field. It will help to reduce traffic for server request and response. No doubt performance will be increase in fetching selected column instead of star.
With help of above tips you can improve your sql query performance. If you have more please add in comment section. It will help upcoming visitors.