SQL Query Optimization Tips
The most important part and parcel of a Database management system is the SQL. It will differ from product to product. Given below are some tips on how to write a well optimized query and avoid performance bottlenecks.
Primary verification of the SQL.
1) The SQL query will need to be verified by two parameters
- The SQL returns the appropriate results.
- The SQL adequately addresses the issues of optimization and efficiency.
Structured Query Language (SQL) – Understanding Table JOINS
1) INNER JOIN
The inner join is given as
SELECT d.Dept_name, e.emp_name
FROM Department d
INNER JOIN employee e
ON d.dept_id = e.dept_id
Structured Query Language (SQL) – Using the UNION / UNION ALL
In this case, let’s consider the tables such that it will be easier to understand the intricacies of the Union.
Table DEPARTMENT contains the Dept_ID and the Dept_name.
Table EMPLOYEE contains the Emp_ID, emp_name, incentives, deductions and contains the details for the permanent employees.
Table TEMP_EMPLOYEE contains the EMP_ID, emp_name and contains the details for temporary employees. The tables have been segregated to understand the UNION query.
Structured Query Language – Using Group By/ Having
The GROUP BY query is typically used with aggregates.
SELECT d.dept_name, d.location, count(e.emp_ID)
FROM department d, employee e
WHERE d.dept_id = e.dept_id
GROUP BY d.dept_name, d.location