Few tips on Writting Efficient SQL Queries
1. Check out the database Design – most often, database have bad design and are not normalized .normalize till 3rd normal form.
2. Only query what you really need .select only the fields you need. never use select * - specify only the fields you need .it will be faster and will use less bandwidth .
3. Be careful with the joins – always try to join on index fields.
4. Indexation – All primary keys need indexes because they make join faster.
5.You can also add indexes of the fields you often use for filtering in the where clause.
6. Be careful with adding indexes because they need to be maintained by the database .if you do many updates on that field, maintaining indexes might take more time.
7. When table is read only, you can add indexes with less negative impact because indexes don’t need to be maintained.
8. Always use names of the columns instead of select *
9. Oracle parser always processes table names from right to left ,the table which is specified last (driving table) is actually the first one to be processed .driving table is the table with lowest number of rows .(its scans and sorts each table and merges the first with the second so better it is to have less numbers of rows in the first table so less processing time )
10. If there are three tables are being joined, select the intersection table as the driving table .The intersection table is the table that has many tables dependent on it.
11. The condition which filter out maximum records should be placed at the end of the where clause as parsing is done from bottom to Top.
12. Always use table aliases and prefix all column names by their aliases where there is more than one table involved in a query .This will reduce the parse time and avoid syntax errors.
13. Use EXISTS in place of IN a better choice for performance.
14. Use NOT Exists in place of NOT IN – because it forces a full read of the table in the sub query select
15. Use joins in place of sub queries(exists/in).in general join tables rather than specifying sub queries for them
Example
select sum(payableamt) TOTALPAIDAMT
from clm_payable cp
where statuscd='INTIMATIONCOMPLETED'
and cp.scheduleseq in (select scheduleseq from clm_schedule_s ss where ss.claimseq = PI_CLAIMSEQ);
Modified To
select sum(cp .payableamt) TOTALPAIDAMT
from clm_payable cp , clm_schedule_s ss
where cp. scheduleseq=ss. scheduleseq
and cp.statuscd='INTIMATIONCOMPLETED'
and ss.claimseq = PI_CLAIMSEQ;
17. Avoid join that requires the distinct Qualifier on the select list.
18. Avoid calculations on Indexed columns.-if indexed column is part of the function in the where clause, the optimizer does not use an index and will perform a full table scan instead.
19. Avoid NOT on indexed columns –when using NOT on indexed column, the optimizer does not use index and will perform full table scan instead.
20 Use >= instead of >
Example
If there is an Index on depart no .
Select * from emp where deptno>=4
Instead of
Select * from emp where deptno>3
Because instead of looking in the index for the first row with column =3 and the scanning forward for the first value that is >3, the DBMS may jump directly to the first entry that is =4
21. Avoid Using a having Clause in select Statement.
2. Only query what you really need .select only the fields you need. never use select * - specify only the fields you need .it will be faster and will use less bandwidth .
3. Be careful with the joins – always try to join on index fields.
4. Indexation – All primary keys need indexes because they make join faster.
5.You can also add indexes of the fields you often use for filtering in the where clause.
6. Be careful with adding indexes because they need to be maintained by the database .if you do many updates on that field, maintaining indexes might take more time.
7. When table is read only, you can add indexes with less negative impact because indexes don’t need to be maintained.
8. Always use names of the columns instead of select *
9. Oracle parser always processes table names from right to left ,the table which is specified last (driving table) is actually the first one to be processed .driving table is the table with lowest number of rows .(its scans and sorts each table and merges the first with the second so better it is to have less numbers of rows in the first table so less processing time )
10. If there are three tables are being joined, select the intersection table as the driving table .The intersection table is the table that has many tables dependent on it.
11. The condition which filter out maximum records should be placed at the end of the where clause as parsing is done from bottom to Top.
12. Always use table aliases and prefix all column names by their aliases where there is more than one table involved in a query .This will reduce the parse time and avoid syntax errors.
13. Use EXISTS in place of IN a better choice for performance.
14. Use NOT Exists in place of NOT IN – because it forces a full read of the table in the sub query select
15. Use joins in place of sub queries(exists/in).in general join tables rather than specifying sub queries for them
Example
select sum(payableamt) TOTALPAIDAMT
from clm_payable cp
where statuscd='INTIMATIONCOMPLETED'
and cp.scheduleseq in (select scheduleseq from clm_schedule_s ss where ss.claimseq = PI_CLAIMSEQ);
Modified To
select sum(cp .payableamt) TOTALPAIDAMT
from clm_payable cp , clm_schedule_s ss
where cp. scheduleseq=ss. scheduleseq
and cp.statuscd='INTIMATIONCOMPLETED'
and ss.claimseq = PI_CLAIMSEQ;
17. Avoid join that requires the distinct Qualifier on the select list.
18. Avoid calculations on Indexed columns.-if indexed column is part of the function in the where clause, the optimizer does not use an index and will perform a full table scan instead.
19. Avoid NOT on indexed columns –when using NOT on indexed column, the optimizer does not use index and will perform full table scan instead.
20 Use >= instead of >
Example
If there is an Index on depart no .
Select * from emp where deptno>=4
Instead of
Select * from emp where deptno>3
Because instead of looking in the index for the first row with column =3 and the scanning forward for the first value that is >3, the DBMS may jump directly to the first entry that is =4
21. Avoid Using a having Clause in select Statement.
