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_Emp_name, last_Emp_name, age, subject_Name FROM student_Info_details;
Instead of:
SELECT * FROM student_Info_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_Name, count(subject_Name)
FROM student_Info_details
WHERE subject_Name != 'Science'
AND subject_Name != 'Maths'
GROUP BY subject_Name;
Instead of:
SELECT subject_Name, count(subject_Name)
FROM student_Info_details
GROUP BY subject_Name
HAVING subject_Name!= 'Vancouver' AND subject_Name!= 'Toronto';
3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
For Example: Write the query as
SELECT name
FROM employee_Info
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)
FROM employee_Info_details)
AND deptpartment = 'Electronics';
Instead of:
SELECT name
FROM employee_Info
WHERE salary = (SELECT MAX(salary) FROM employee_Info_details)
AND age = (SELECT MAX(age) FROM employee_Info_details)
AND emp_deptpartment = 'Electronics';
4) Use operator EXISTS, IN and table joins appropriately in your query.
a) Usually IN has the slowest performance.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.
For Example: Write the query as
Select * from product_Detail p
where EXISTS (select * from order_items o
where o.product_Detail_id = p.product_Detail_id)
Instead of:
Select * from product_Detail p
where product_Detail_id IN
(select product_Detail_id from order_items
5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
For Example: Write the query as
SELECT d.deptpartment_id, d.deptpartment
FROM deptpartment d
WHERE EXISTS ( SELECT 'X' FROM employee_Info e WHERE e.deptpartment = d.deptpartment);
Instead of:
SELECT DISTINCT d.deptpartment_id, d.deptpartment
FROM deptpartment d,employee_Info e
WHERE e.deptpartment = e.deptpartment;
6) Try to use UNION ALL in place of UNION.
For Example: Write the query as
SELECT id, first_Emp_name
FROM student_Info_details_class10
UNION ALL
SELECT id, first_Emp_name
FROM sports_team;
Instead of:
SELECT id, first_Emp_name, subject_Name
FROM student_Info_details_class10
UNION
SELECT id, first_Emp_name
FROM sports_team;
7) Be careful while using conditions in WHERE clause.
For Example: Write the query as
SELECT id, first_Emp_name, age FROM student_Info_details WHERE age > 10;
Instead of:
SELECT id, first_Emp_name, age FROM student_Info_details WHERE age != 10;
Write the query as
SELECT id, first_Emp_name, age
FROM student_Info_details
WHERE first_Emp_name LIKE 'Chan%';
Instead of:
SELECT id, first_Emp_name, age
FROM student_Info_details
WHERE SUBSTR(first_Emp_name,1,3) = 'Cha';
Write the query as
SELECT id, first_Emp_name, age
FROM student_Info_details
WHERE first_Emp_name LIKE NVL ( :name, '%');
Instead of:
SELECT id, first_Emp_name, age
FROM student_Info_details
WHERE first_Emp_name = NVL ( :name, first_Emp_name);
Write the query as
SELECT product_Detail_id, product_Detail_name
FROM product_Detail
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)
Instead of:
SELECT product_Detail_id, product_Detail_name
FROM product_Detail
WHERE unit_price >= MAX(unit_price)
and unit_price <= MIN(unit_price)
Write the query as
SELECT id, name, salary
FROM employee_Info
WHERE deptpartment = 'Electronics'
AND location = 'Surat';
Instead of:
SELECT id, name, salary
FROM employee_Info
WHERE deptpartment || location= 'ElectronicsSurat';
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_Info
WHERE salary < 25000;
Instead of:
SELECT id, name, salary
FROM employee_Info
WHERE salary + 10000 < 35000;
Write the query as
SELECT id, first_Emp_name, age
FROM student_Info_details
WHERE age > 10;
Instead of:
SELECT id, first_Emp_name, age
FROM student_Info_details
WHERE age NOT = 10;
8) 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_Info
WHERE name LIKE 'Bhavdip%'
and location = 'Surat';
Instead of:
SELECT DECODE(location,'Surat',id,NULL) id FROM employee_Info
WHERE name LIKE 'Bhavdip%';
9) To store large binary objects, first place them in the file system and add the file path in the database.
10) To write queries which provide efficient performance follow the general SQL standard rules.
