Use below tips to increase sql query performance.
1. The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.
EX: select * from table;
select col1,col2,.... from table; -- faster when compared to previous once
2. Try to avoid HAVING clause to filter the records..
3. EXISTS is efficient when most of the filter criteria is in the main query.
4. IN is efficient when most of the filter criteria is in the sub-query.
5. Separate all words with a single space.
6. Use any harcoded values in last of the where clause. Because sql engine execute the statements from last.
EX: select a.* from emp a, dept b
where a.deptno = b.deptono
and b.deptno = 40;
1. The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.
EX: select * from table;
select col1,col2,.... from table; -- faster when compared to previous once
2. Try to avoid HAVING clause to filter the records..
3. EXISTS is efficient when most of the filter criteria is in the main query.
4. IN is efficient when most of the filter criteria is in the sub-query.
5. Separate all words with a single space.
6. Use any harcoded values in last of the where clause. Because sql engine execute the statements from last.
EX: select a.* from emp a, dept b
where a.deptno = b.deptono
and b.deptno = 40;
Thanks for posting all these useful tips to improve the performance of sql query. I will take care of all these points when writing up a query next time. The points that you have mentioned are good and worth noticing.
ReplyDeleteoracle r12 new features
Dear Sree Hari ,
ReplyDeleteVery simple but very effective , thanks for the suggestions ....
Please post ..
Thanks and Regards
Thomas Paul
thomaspaul123@gmail.com