SQL Tuning/Query Optimization/SQL Optimization

           SQL Tuning/Query Optimization/SQL Optimization

Query Optimization is often misunderstood as a technique or some setting that we need to change in SQL. In-real query optimization is something related to best practice that we have to follow in your day-to-day life.

Query optimization is all about how we use clause and operators in our query.

I have listed below some examples that we might be using daily and best practice that needs to follow to optimize for better performance.

• SQL query becomes faster if you specify column names in query instead of '*'

                  EXAMPLE:

                             Normal way:

                                 select * from emp_details

                             Optimized way:

                                 select fname,lname from emp_details   

                  

• Always use HAVING clause instead of WHERE clause whenever you are using GROUP BY in your query. Using HAVING clause allows SQL to first group the data and then filter the grouped data using HAVING clause. 

                  EXAMPLE:

                             Normal way:

                                 select fname.lname from emp_details

                                 where fname like 'XYZ'

                                 group by fname

                             Optimized way:

                                select fname,lname from emp_details

                                group by fname

                                having fname like 'XYZ'

• Create Index on the column which we will be using to filter data.Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause

• Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query. 

                 EXAMPLE:

                             Normal way:

                                SELECT name FROM employee

                  WHERE salary = (SELECT MAX(salary) FROM employee_details) 
                  AND age = (SELECT MAX(age) FROM employee_details) 
                  AND emp_dept = 'Electronics';

                             Optimized way:

                                SELECT name FROM employee 

                  WHERE (salary, age ) = (SELECT MAX (salary), MAX (age) 
                  FROM employee_details) 
                  AND dept = 'Electronics'; 

• Always use EXISTS instead of IN operator, Generally on large data IN operator works really slow compare to EXISTS. IN is efficient when most of the filter criteria is in the sub-query. EXISTS is efficient when most of the filter criteria is in the main query.

• Always use UNION ALL instead of UNION if you does not expect unique result. UNION checks for duplicate rows and eliminate it . Hence it takes more time for execution compare to UNION ALL

• If you want to re-use the query, create a STORE PROCEDURE for the same.

• Check if there is at-least 30% HHD is empty – it improves the performance a bit

• Remove any unnecessary joins from table