Tuning the Data Access and Data Layer

There are multiple tweaks if performed on data access and data layer,significant improvement would be visible in the application. List of some such tweaks is as mentioned below -

Tuning Data Layer

1. Use materialized view, (if suitable) in place of very time consuming queries or views
2. Use query re-write
3. Use parallel execution of queries 
4. Use PARALLEL_DEGREE_LIMIT to control parallization on database level
5. Partition big tables 
6. Choosing the right isolation level
7. Use sql trace and explain plan both to analyze query for tuning
8. Loading data very fast using partition exchange
9. Types of index and correct one to choose from

Tuning dataaccess Layer

1. Identify and resolve select n+1 problem in hibernate
2. Use in clause in hibernate only if options inside in clause are few  
3. Avoid using explicit getSession in spring-orm
4. Use jdbc batch while firing multiple query using jdbc 
5. Prefer batch operation in hibernate while executing multiple queries 
6. Prefer ordered collection over sorted collection in hibertnate
7. Prefer parametrized SQL over completed sql created at run time
8. For batch operations prefer 11g version driver for better performance