SQL tuning and Performance tuning

SQL tuning or SQL optimization:

- SQL statements are used to retrieve data from database.
- Use of best query is important when performance is concerned.
- We need to sql query based on the requirement.
- Below is list of SQL queries that can be optimized for better performance.

1) The sql query becomes faster if you use the actual column names in SELECT statement instead of than '*'.
SELECT id, first_name, last_name, age, subject FROM student_details;
Instead of:
SELECT * FROM student_details;

2) HAVING clause is used to filter rows after all rows are selected. It is just like a filter. Dont use HAVING clause for any other purpose.

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 more than one subqueries in your main query. Try to minimize subquery block in your query.

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 operator EXISTS, IN and table joins appropriately in your query.

a) IN has slowest performance.

b) IN is efficient when most of the filter criteria is in the subquery

c) EXISTS is efficient when most of the filter criteria is in main query.

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 instead of DISTINCT when using joins which involves tables having one to many relationship.

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 in place of UNION.

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 when using conditions in WHERE clause.

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

Instead of:

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

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

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

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)

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

Instead of:

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

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

Instead of:

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

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 avoid scanning of same rows or joining same table repetitively.

DECODE can also be used in place of GROUP BY or ORDER BY clause.

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 write queries which provide efficient performance follow the general SQL standard rules.

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

To get best results when writing SQL queries, best query performance.

Below is the list of queries tips to help your SQL queries optimized for better performance.

1) Avoid using SELECT *

2) SQL select only rows needed. Less rows retrieved, faster the SQL query will run.

3) When you create a new table, always create a unique clustered index belong to it, possibly its a numeric type.

4) Use SQL JOIN instead of subqueries.

SELECT a.id, 
(SELECT MAX(created) 
FROM posts 
WHERE author_id = a.id) 
AS latest_post FROM authors a

Subqueries can be replaced by JOINS which is faster to execute.

SELECT a.id, MAX(p.created) AS latest_post 
FROM authors a 
INNER JOIN posts p 
ON (a.id = p.author_id) 
GROUP BY a.id

5) Use UNION instead of OR.

SELECT * FROM a, b WHERE a.p = b.q OR a.x = b.y;

can be replaced by:

SELECT * FROM a, b WHERE a.p = b.q
UNION
SELECT * FROM a, b WHERE a.x = b.y;

6) Avoid CURSORS. Cursors have less performance as every FETCH statement executed is equivalent to another SQL SELECT statement execution that returns a single row.

7) ORDER BY USING: lets you specify an arbitrary sort function. You can also do stuff like ORDER BY col1+col2 which is cool.