JProfiler Get rid of your performance problems and memory leaks! Modern Garbage Collection Tuning Java Performance Training Courses COURSES AVAILABLE NOW. We can provide training courses to handle all your Java performance needs Java Performance Tuning, 2nd ed The classic and most comprehensive book on tuning Java Java Performance Tuning Newsletter Your source of Java performance news. Subscribe now! Enter email: Training online Threading Essentials course JProfiler Get rid of your performance problems and memory leaks!

The following pages have their detailed tips extracted below

• JDBC Performance Tips (targeted at AS/400, but generically applicable)
• Performance tuning report in German
• Tuning IBM's WebSphere product
• JDBC optimizing for DB2
• J2EE Performance tuning
• JDBC Drivers
• Discussion on JDBC performance
• Oracle JDBC tips
• Chapter 19, "Performance" of Java Programming with Oracle JDBC
• Database performance
• Advanced JDBC tutorial (requires free registration).
• Optimizing JDBC
• JDBC performance tips
• Optimizing JDBC Prepared Statments
• Optimizing entity beans
• Experiences building a servlet
• Sun community discussion on "Optimizing Entity Beans" with Akara Sucharitakul
• Some IBM Java performance tips
• Wrapping PreparedStatement

The following detailed tips have been extracted from the raw tips page

http://www.as400.ibm.com/developer/java/topics/jdbctips.html
JDBC Performance Tips (targeted at AS/400, but generically applicable) (Page last updated February 2001, Added 2001-03-21, Authors Richard Dettinger and Mark Megerian, Publisher IBM). Tips:

• Use prepared statements (PreparedStatement class) [article provides coded example of using Statement vs. PreparedStatement].
• Note that two database calls are made for each row in a ResultSet: one to describe the column, the second to tell the db where to put the data. PreparedStatements make the description calls at construction time, Statements make them on every execution.

http://www.bastie.de/resource/res/mjp.pdf and http://www.bastie.de/java/mjperformance/contents.html
Performance tuning report in German. Thanks to Peter Kofler for extracting the tips. (Page last updated November 2001, Added 2001-07-20, Author Sebastian Ritter, Publisher Ritter). Tips:

• use PreparedStatements for DB access

http://www-4.ibm.com/software/webservers/appserv/3steps_perf_tuning.pdf
Tuning IBM's WebSphere product. White paper: "Methodology for Production Performance Tuning". Only non-product specific Java tips have been extracted here. (Page last updated September 2000, Added 2001-01-19, Author Gennaro (Jerry) Cuomo, Publisher IBM). Tips:

• Use connection pools and cached prepared statements for database access.

http://www.sys-con.com/websphere/article.cfm?id=40
JDBC optimizing for DB2 (Page last updated April 2002, Added 2002-04-26, Author John Goodson, Publisher WebSphere Developers Journal). Tips:

• Use prepared statements for repeatedly executing SQL statements

http://www.sys-con.com/java/article.cfm?id=1171
J2EE Performance tuning (Page last updated October 2001, Added 2001-10-22, Author James McGovern, Publisher Java Developers Journal). Tips:

• Follow standard JDBC optimizations: use connection pools; prefer stored procedures or direct SQL; use type 4 drivers; remove extra columns from the result set; use prepared statements when practical; have your DBA tune the query; choose the appropriate transaction levels.

http://portals.devx.com/datadirect/Article/6338
JDBC Drivers (Page last updated March 2002, Added 2002-04-26, Author Barrie Sosinsky, Publisher DevX). Tips:

• Prepared statement pooling (available from JDBC 3.0) caches SQL queries that have been previously optimized and run so that, should they be needed again, they do not have to go through optimization pre-processing again (avoiding optimization steps, such as checking syntax, validating addresses, and optimizing access paths and execution plans). Statement pooling can be a significant performance booster.
• Statement pooling and connection pooling in JDBC 3.0 can cooperate to share statement pools, so that connections that can use a cached statement from another connection, thus incurring statement preparation overheads only once on the first execution of some SQL by any connection.

http://www.jguru.com/jguru/faq/view.jsp?EID=131579
Discussion on JDBC performance (Page last updated August 2000, Added 2001-02-21, Author , Publisher JGuru). Tips:

• Use prepared statements.

http://www.onjava.com/pub/a/onjava/2001/12/19/oraclejdbc.html
Oracle JDBC tips (Page last updated December 2001, Added 2001-12-26, Author Donald Bales, Publisher OnJava). Tips:

• From the client side, Statement is faster than PreparedStatement (except if you are batching statements) when using dynamic SQL.
• Use PreparedStatements for all, except dynamic, SQL statements.
• Use PreparedStatements for batching repetitive inserts or updates.
• OraclePreparedStatement.setExecuteBatch() (proprietary method) is the fastest way to execute batch statements.

http://www.oreilly.com/catalog/jorajdbc/chapter/ch19.html
Chapter 19, "Performance" of Java Programming with Oracle JDBC (Page last updated December 2001, Added 2001-12-26, Author Donald Bales, Publisher O'Reilly). Tips:

• It takes about 65 iterations of a prepared statement before its total time for execution catches up with a statement, because of prepared statement initialization overheads.
• Use PreparedStatements to batch statements for optimal performance.

http://www.fawcette.com/javapro/2002_01/magazine/columns/weblication/
Database performance (Page last updated December 2001, Added 2001-12-26, Author Peter Varhol, Publisher JavaPro). Tips:

• Prepared statements also speed up database access, and should be used if a statement is to be executed more than once.

http://www-105.ibm.com/developerworks/education.nsf/java-onlinecourse-bytitle/975BFD2C367CFFD686256B0500581B3B?OpenDocument
Advanced JDBC tutorial (requires free registration). (Page last updated November 2001, Added 2001-12-26, Author Robert J. Brunner, Publisher IBM). Tips:

• PreparedStatement objects are compiled (prepared) by the JDBC driver or database for faster performance, and accept input parameters so they can be reused with different data.

http://www.sys-con.com/java/article.cfm?id=725
Optimizing JDBC (Page last updated August 2001, Added 2001-08-20, Author John Goodson, Publisher Java Developers Journal). Tips:

• Use a parametrized remote procedure call (RPC) rather than passing parameters as part of the RPC call, e.g. use Connection.prepareCall("Call getCustName (?)").setLong (1,12345) rather than Connection.prepareCall("Call getCustName (12345)")

http://www.precisejava.com/javaperf/j2ee/JDBC.htm
JDBC performance tips (Page last updated November 2001, Added 2001-12-26, Authors Ravi Kalidindi and Rohini Datla, Publisher PreciseJava). Tips:

• Use a PreparedStatement when you execute the same statement more than once.

http://www.theserverside.com/resources/article.jsp?l=Prepared-Statments
Optimizing JDBC Prepared Statments. Also a followup discussion at http://www.theserverside.com/discussion/thread.jsp?thread_id=8013 (Page last updated July 2001, Added 2001-08-20, Author ?, Publisher The Server Side). Tips:

• Databases analyze query statements to decide how to process them most optimally, then cache the resulting query plan, keyed on the full statement. Reusing identical statements reuses the query plan.
• Altering the statement causes a new query plan to be generated for each new statement. However statements with parameters can have the query plan reused, so use parameters rather than regenerating the statement with different values.
• Using a new connection requires a prepared statement to be recreated. Reusing connections allows a prepared statement to be reused.
• Connection pools should have associated PreparedStatement caches so that the PreparedStatements are automatically reused.

http://developer.java.sun.com/developer/technicalArticles/ebeans/sevenrules/
Optimizing entity beans (Page last updated May 2001, Added 2001-05-21, Author Akara Sucharitakul, Publisher Sun). Tips:

• Always prepare your SQL statements.

http://www.java-pro.com/upload/free/features/Javapro/2001/07jul01/ah0107/ah0107-1.asp
Experiences building a servlet (Page last updated June 2001, Added 2001-06-18, Author Asif Habibullah, Jimmy Xu, Publisher Java Pro). Tips:

• Use PreparedStatements rather than plain Statement objects.

http://developer.java.sun.com/developer/community/chat/JavaLive/2001/jl0619.html
Sun community discussion on "Optimizing Entity Beans" with Akara Sucharitakul (Page last updated June 2001, Added 2001-07-20, Author Edward Ort, Publisher Sun). Tips:

• Prepared SQL statements get compiled in the database only once, future invocations do not recompile them. The result of this is a decrease in the database load, and an increase in performance of up to 5x.

http://www.as400.ibm.com/developer/java/faq/perffaq.html
Some IBM Java performance tips. Although intended for AS/400 Java, many tips are generally applicable (Page last updated ?, Added 2000-10-23, Author ?, Publisher IBM). Tips:

• Use Prepared Statements.

http://www.javaworld.com/javaworld/jw-01-2002/jw-0125-overpower.html
Wrapping PreparedStatement (Page last updated January 2002, Added 2002-02-22, Author Bob Byron and Troy Thompson, Publisher JavaWorld). Tips:

• With Statement, the same SQL statement with different parameters must be recompiled by the database each time. But PreparedStatements can be parametrized, and these do not need to be recompiled by the database for use with different parameters.
• [Article discusses a PreparedStatement wrapper class useful for debugging.]