SQL Query Optimization Tools | Query Tuning Tips

In our last SQL tutorial, we discussed SQL Subqueries. In this session of SQL, we will see SQL Query Optimization or SQL Query Tuning.

Moreover, we will learn different techniques for optimizing or tuning a query in SQL.  Also, we will see SQL Query Optimization examples.

So, let us start the SQL Query Optimization tutorial.

Introduction to SQL Query Optimization

SQL Statements are used to recover data from the database.

We can get the same outcomes by optimizing diverse SQL queries.

In any case, use of the best query is vital when execution is considered.

SQL Query Optimization/Tuning Techniques

1. The SQL query turns out to be quicker in the case that you use the real segment names in SELECT rather than ‘*’.

For example: Write the query as

SELECT id, first_name, last_name, age, subject FROM student_details;

Rather than:

SELECT * FROM student_details;

2. Having condition is used to channel the rows after every one of the rows is chosen. Try not to use HAVING provision for some other purposes.

For example: Write the query as

SELECT subject, count(subject)
FROM student_details
WHERE subject != 'Science'
AND subject != 'Maths'
GROUP BY subject;

Instead of:

SELECT subject, count(subject)
FROM student_details
GROUP BY subject
HAVING subject!= 'Vancouver' AND subject!= 'Toronto';

3. Sometimes you may have in excess of one subquery in your principle query.

For example: Write the query as

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

Instead of:

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

4. Use administrator EXISTS, IN and table joins suitably in your query.

a) Usually IN has the slowest execution.
b) IN is effective when a large portion is in the sub-query.
c) EXISTS is useful.

For example: Write the query as

Select * from product p
where EXISTS (select * from order_items o
where o.product_id = p.product_id)

Instead of:

Select * from product p
where product_id IN
(select product_id from order_items

5. Use EXISTS rather than DISTINCT when using joins which includes tables having the one-to-many relationship.

For example: Write the query as

SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);

Instead of:

SELECT DISTINCT d.dept_id, d.dept
FROM dept d,employee e
WHERE e.dept = e.dept;

6. Try to use UNION ALL instead of UNION.

For example: Write the query as

SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team;

Instead of:

SELECT id, first_name, subject
FROM student_details_class10
UNION
SELECT id, first_name
FROM sports_team;

7. Be cautious while using conditions in WHERE statement.

SELECT id, first_name, age FROM student_details WHERE age > 10;

Instead of:

SELECT id, first_name, age FROM student_details WHERE age != 10;

Write the query as

SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE 'Chan%';

Instead of:

SELECT id, first_name, age
FROM student_details
WHERE SUBSTR(first_name,1,3) = 'Cha';

Write the query as

SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE NVL ( :name, '%');

Instead of:

SELECT id, first_name, age
FROM student_details
WHERE first_name = NVL ( :name, first_name);

Write the query as

SELECT product_id, product_name
FROM product
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)

Instead of:

SELECT product_id, product_name
FROM product
WHERE unit_price >= MAX(unit_price)
and unit_price <= MIN(unit_price)

Write the query as

SELECT id, name, salary
FROM employee
WHERE dept = 'Electronics'
AND location = 'Bangalore';

Instead of:

SELECT id, name, salary
FROM employee
WHERE dept || location= 'ElectronicsBangalore';

Use non-column expression on one side of the query because it will be processed earlier.

Write the query as

SELECT id, name, salary
FROM employee
WHERE salary < 25000;

Instead of:

SELECT id, name, salary
FROM employee
WHERE salary + 10000 < 35000;

Write the query as

SELECT id, first_name, age
FROM student_details
WHERE age > 10;

Instead of:

SELECT id, first_name, age
FROM student_details
WHERE age NOT = 10;

8. Use DECODE to stay away from the checking of same columns or joining a similar table monotonously. It can likewise be made used instead of GROUP BY or ORDER BY statement.

For example: Write the query as

SELECT id FROM employee
WHERE name LIKE 'Ramesh%'
and location = 'Bangalore';

Instead of:

SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee
WHERE name LIKE 'Ramesh%';

9. To compose the query which gives proficient execution take after the general SQL standard.

a. Use single case for all SQL verbs.
b. Begin all SQL verbs on another line.
c. Separate all words with a solitary space.
d. Right or left adjusting verbs inside the underlying SQL verb.

So, this was all in SQL Query Optimization. Hope you like our explanation of SQL Tuning.

Summary

Hence, in this SQL Query Optimization tutorial, we discussed how to optimize a query in SQL.

Moreover, we saw SQL Query Optimization Techniques. Also, we discussed SQL Query Optimization examples.

Still, if you are having any confusion in SQL Query Optimization/Tuning, feel free to ask in the comments.