SQL Query tuning Tips &Tricks

SQL Query tuning Tips  

1-Use Boolean function during the aggeration /calculation/comparing to avoid performance issues. & Avoid using case when statement in case data is very larger(1.5 cr records).

2-Select ONLY those columns in a query that are required. Extra columns which are not actually used, incur more I/O on the database and increase network traffic.
3-DISTINCT incurs an extra sort operation and therefore slows your queries down.
4-should not be any Cartesian product in the query unless there is a definite requirement (LOD Calculation In tableau ).
4-Use Union all instead of Union.UNION incurs an extra sort operation which can be avoided.

5-Avoid column alias name during the joining.
6-Use of the EXISTS operator where possible once a match has been found it can stop and avoid a full table scan(instead of IN)
7-Use equi-joins(comparing with = operator) whenever possible, they improve SQL efficiency.
8- Separate SQL statements for different tasks to avoid complex calculations in one Select statement. (Archive it by Union all)
9-Joins to complex views are not recommended, impacting the executing query time.

10- Avoid doing an ORDER BY & Group by on a large data set especially if the response time is important.
11- While querying on a partitioned table try to use the partition key in the “WHERE” clause if possible. This will ensure partition pruning
12-Avoid Having Clause. Having a clause is required if you further wish to filter the result of aggregations..
13-Drop unused Indexes.
14- Use joins instead of sub-queries.