Performance Optimization Tips

1. Replace IN, NOT IN with Exists & Not Exists.
2. Avoid using “Insert Into … Select …” statements as it can take lot time to execute if select query returns huge number of records. Instead user cursors.
3. Avoid sub queries, instead use joins or subquery factoring (with clause) this will optimize the code.
4. If insert & update statements are used inside Loops, then commit them after every 500 or 1000 updates or inserts & don’t forget to commit after end loop.
5. Plan the deletion of records from the temporary / log tables created in the programs either using separate deletion program or in the same program where the tables are used.
6. Analyze the tables with heavy updates to optimize the performance.
7. Don’t forget to close the files & cursors opened in the program.
8. Use ANSI SQL as they are easy to understand by any database developers and are highly optimizable.
9. Fields used frequently in where clause of the queries should be indexed.
10. Tables with records running over millions should be considered for partitioning.
11. Avoid using string searches.
12. Use type casting diligently. Stop writing queries that require implicit type casting as shown below.

Select *
from Employees
where EMPID = ‘100’;

EMPID is a numeric field, by enclosing 100 in single quotes we are triggering implicit type casting which may impact performance adversely.

13. Avoid using OR by re-writing the query using UNION.
14. Use Hints to optimize the queries that take too long to execute.
15. Be cautious while using PARALLEL hint.
16. Use Bulk Collect to optimize the PL/SQL code wherever possible.
17. Too many indexes on a table may pull down the performance. Therefore, the number of indexes on a table should be between 5 to 7 only.
18. Make use of function-based indexes, if function-based searches are used frequently.
19. Never use * in select queries instead use fields names as SQL engine takes time in elaborating * with all fields name. Besides, if new field is added to the table, it is automatically added with the usage of *, which may not be required to be added resulting in run time error.