10 Basic SQL Statement Optimization Tips

Mostly SQL statement is using for fetching data from any specific database. We can fetch data with same result by writing different SQL statement. But data will show in different performance manner. So using the perfect SQL statement always related to the performance of that SQL statement. So we must be very careful during writing the best perform SQL statement. Otherwise full software or application might get slower only for the poor SQL statement.

Worldwide there are some basic rules for writing best perform SQL statement. Let’s see those SQL statement with example.

SQL Tuning/SQL Optimization Techniques:

1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than ‘*’.

For Example: Write the query as

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

Instead of:

SELECT * FROM student_details;

2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any 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!= ‘SQL Performance AND subject!= ‘SQL Tuning by Tester’;

3) Sometimes you may have more than one sub-queries in your main query. Try to minimize the number of sub-query block in your 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) You can use operator EXISTS, IN and table joins appropriately in your query.

1. a) Usually IN has the slowest performance.
2. b) IN is efficient when most of the filter criteria is in the sub-query.
3. c) EXISTS is efficient when most of the filter criteria is in the main query.

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) Always try to use EXISTS instead of DISTINCT when using joins which involves tables having 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) Always try to use UNION ALL in place 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 careful while using conditions in WHERE clause.

For Example: Write the query as

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

Instead of:

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

Write the query as

SELECT id, first_name, age

FROM student_details

WHERE first_name LIKE ‘Moinur%’;

Instead of:

SELECT id, first_name, age

FROM student_details

WHERE SUBSTR(first_name,1,3) = ‘Moi’;

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 = ‘Dhaka’;

Instead of:

SELECT id, name, salary

FROM employee

WHERE dept || location= ‘ElectronicsDhaka’;

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 < 35000;

Instead of:

SELECT id, name, salary

FROM employee

WHERE salary + 10000 < 45000;

Write the query as

SELECT id, first_name, age

FROM student_details

WHERE age > 75;

Instead of:

SELECT id, first_name, age

FROM student_details

WHERE age NOT = 75;

8) You can use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause.

For Example: Write the query as

SELECT id FROM employee

WHERE name LIKE ‘Moinur%’

and location = ‘Dhaka’;

Instead of:

SELECT DECODE(location,’Dhaka’,id,NULL) id FROM employee

WHERE name LIKE ‘Moinur%’;

9) You can use binary objects To store large, first place them in the file system and add the file path in the database.

10) By following the below general SQL standard rules you can write queries which provide efficient performance .

1. a) Use single case for all SQL verbs
2. b) Begin all SQL verbs on a new line
3. c) Separate all words with a single space
4. d) Right or left aligning verbs within the initial SQL verb

As a Software tester or Software Developer we hope, above quick tips will help you to write good perform SQL statement. If you like this article please let us know by giving comment below.