What areas should I focus on for the best performance in a JDBC application?
- Use a connection pool mechanism whenever possible.
- Use prepared statements. These can be beneficial, for example with DB specific escaping, even when used only once.
- Use stored procedures when they can be created in a standard manner. Do watch out for DB specific SP definitions that can cause migration headaches.
- Even though the jdbc promotes portability, true portability comes from NOT depending on any database specific data types, functions and so on.
- Select only required columns rather than using select * from Tablexyz.
- Always close Statement and ResultSet objects as soon as possible.
- Write modular classes to handle database interaction specifics.
- Work with DatabaseMetaData to get information about database functionality.
- Softcode database specific parameters with, for example, properties files.
- Always catch AND handle database warnings and exceptions. Be sure to check for additional pending exceptions.
- Test your code with debug statements to determine the time it takes to execute your query and so on to help in tuning your code. Also use query plan functionality if available.
- Use proper ( and a single standard if possible ) formats, especially for dates.
- Use proper data types for specific kind of data. For example, store birthdate as a date type rather than, say, varchar.
Rahul kumar Gupta adds:
- Use scrollable ResultSet ( JDBC 2.0 ).
- Stay away from the JDBC-ODBC and other Type 1 drivers where possible.
Peter Kua adds:
For more detailed information, take a look at JDBC Performance Tips. While these are aimed specifically at the AS/400, most of the tips are applicable to JDBC in general.
Joe Sam Shirah adds:
The DB reference manuals covering JDBC for your own and other databases can also provide valuable tips. See: Where can I find online documentation for database xyz?