Java Performance Tuning
Java(TM) - see bottom of page
Our valued sponsors who help make this site possible
JProfiler: Get rid of your performance problems and memory leaks!
Training online: Concurrency, Threading, GC, Advanced Java and more ...
JDBC connection pooling tips.
Get rid of your performance problems and memory leaks!
Get rid of your performance problems and memory leaks!
Tips covering only connection pooling with JDBC. The primary tip,
repeated again and again, is to use connection pooling. Secondary
- sizing the pool
- reusing PreparedStatements
- how to use the pool elements across your application
The following pages have their detailed tips extracted below
The following detailed tips have been extracted from the raw tips page
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 connection pooling, either explicitly with your own implementation, or implicitly via a product that supports connection pooling.
Paper detailing the "Best Practices for Developing High Performance Web and Enterprise Applications" using IBM's WebSphere. All the tips are generally applicable to servlet/EJB development, as well as other types of server development. (Page last updated September 2000, Added 2001-01-19, Author Harvey W. Gunther, Publisher IBM). Tips:
- Use JDBC connection pooling, release JDBC resources when done, and reuse datasources for JDBC connections.
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.
WebSphere V3 Performance Tuning Guide (Page last updated March 2000, Added 2001-01-19, Authors Ken Ueno, Tom Alcott, Jeff Carlson, Andrew Dunshea, Hajo Kitzh÷fer, Yuko Hayakawa, Frank Mogus, Colin D. Wordsworth, Publisher IBM). Tips:
- One of the most time-consuming procedures of a database application is establishing a connection to the database. Use connection pooling to minimize this overhead.
Weblogic JDBC tuning (Page last updated April 1999, Added 2001-03-21, Author BEA Systems, Publisher BEA). Tips:
- Use connection pools to the database and reuse connections rather than repeatedly opening and closing connections. Optimal pool size is when the connection pool is just large enough to service requests without waits.
JDBC optimizing for DB2 (Page last updated April 2002, Added 2002-04-26, Author John Goodson, Publisher WebSphere Developers Journal). Tips:
- Use the same connection to execute multiple statements.
- Keep connection objects open, and reuse them, rather than repeatedly connecting and disconnecting.
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.
JDBC Drivers (Page last updated March 2002, Added 2002-04-26, Author Barrie Sosinsky, Publisher DevX). Tips:
- JDBC 3.0 has additional features to improve performance such as advancements in connection pooling, statement pooling, RowSet objects.
- Opening a connection is the most resource-expensive step in database transactions. Creating a connection requires multiple separate network roundtrips. However, once the connection object has been created, there is little penalty in leaving the connection object in place and reusing it for future connections.
- Connection pooling, keeps open a cache of database connection objects, making them available for immediate use. Instead of performing expensive network roundtrips to the database server to open a connection, a connection attempt results in the re-assignment of a connection from the local cache.
- 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.
Overview of common application servers. (Announced at http://www.theserverside.com/home/thread.jsp?thread_id=9581). I've extracted the performance related features (Page last updated October 2001, Added 2001-10-22, Author Pieter Van Gorp, Publisher Van Gorp). Tips:
Discussion on JDBC performance (Page last updated August 2000, Added 2001-02-21, Author , Publisher JGuru). Tips:
- Use a connection pool mechanism whenever possible.
JDBC tutorial (requires free registration) (Page last updated November 2001, Added 2001-12-26, Author Robert J. Brunner, Publisher IBM). Tips:
- Each request for a new database connection involves significant overhead. This can impact performance if obtaining new connections occurs frequently. Reuse connections from connection pools to limit the cost of creating connections. [The tutorial lists all the overheads involved in creating a database connection].
- The ConnectionPoolDataSource (from JDBC3.0) and PooledConnection interfaces provide built-in support for connection pools.
Article on building an object pool for improved performance. (Page last updated June 1998, Added 2000-12-20, Author Thomas E. Davis, Publisher JavaWorld). Tips:
- Use connection pools to recycle connections and reduce overheads [Article includes a JDBC connection pool implementation.]
Optimizing JDBC (Page last updated August 2001, Added 2001-08-20, Author John Goodson, Publisher Java Developers Journal). Tips:
- Minimize connections; try to reuse connections.
EJB performance tips (Page last updated November 2001, Added 2001-12-26, Authors Ravi Kalidindi and Rohini Datla, Publisher PreciseJava). Tips:
- Tune the connection pool size to minimize the creation and destruction of database connections.
JDBC performance tips (Page last updated November 2001, Added 2001-12-26, Authors Ravi Kalidindi and Rohini Datla, Publisher PreciseJava). Tips:
- Get database connections from a connection pool: use javax.sql.DataSource for optimal configurability. Use the vendor's connection pool; or ConnectionPoolDataSource and PooledConnection from JDBC2.0; or a proprietary connection pool.
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:
- 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.
Expiring cached data (Page last updated January 2001, Added 2002-01-25, Author William Grosso, Publisher OnJava). Tips:
- Database connection pools can take one of two strategies: a limited size pool, where attempts to make connections beyond the pool size must wait for a connection to become idle; or a flexible sized pool with a preferred size which removes idle connections as soon as the preferred size is exceeded (i.e. temporarily able to exceed the preferred size). The fixed size pool is generally considered to be the better choice.
EJB performance tips (Page last updated December 2001, Added 2001-12-26, Author Krishna Kothapalli and Raghava Kothapalli, Publisher JavaPro). Tips:
- Tune the underlying system, e.g. TCP/IP parameters, file limits, connection pool parameters, EJB pools sizes, thread counts, number of JVMs, JVM heap size, shared pool sizes, buffer sizes, indexes, SQL queries, keep/alive parameters, connection backlogs.
Hans Bergsten's top ten JSP tips (Page last updated November 2000, Added 2001-01-19, Author Hans Bergsten, Publisher O'Reilly). Tips:
- Database access is typically very expensive in terms of server resources. Use a connection pool to share database connections efficiently between all requests, but don't use the JDBC ResultSet object itself as the cache object.
Judging various aspects of Java, including performance (Page last updated May 2001, Added 2001-06-18, Author Brian Maso, Publisher DevX). Tips:
- J2EE defines component models with high scalability potential. Maximizing scalability requires sticking to stateless session beans and handling all database interactions programmatically (through pooled JDBC connections).
Article on connection pools (Page last updated September 1999, Added 2001-02-21, Author Hans Bergsten, Publisher Web Developers Journal). Tips:
- Reuse database connections using a connection pool.
Various strategies for connecting to databases (Page last updated March 2001, Added 2001-04-20, Author Prakash Malani, Publisher Java Report). Tips:
- Use pooled connections to reduce connection churn overheads.
- javax.sql.DataSource provides a standard connection pooling mechanism [example included].
- Obtain and release pooled conections within each method that requires the resource if the connection is very short (termed "Quick Catch-and-Release Strategy" in the article). However do not release the connection only to use it again almost immediately, instead hold the connection until it will not be immediately needed.
- The performance penalty of obtaining and releasing connections too frequently is quite small in comparison to potential scalability problems or issues raised because EntityBeans are holding on to the connections for too long.
- The "Quick Catch-and-Release Strategy" is the best default strategy to ensure good performance and scalability.
Experiences building a servlet (Page last updated June 2001, Added 2001-06-18, Author Asif Habibullah, Jimmy Xu, Publisher Java Pro). Tips:
- Make the servlets thread-safe and use connection pooling.
Choosing a J2EE application server, emphasizing the importance of performance issues (Page last updated February 2001, Added 2001-02-21, Author Steve Franklin, Publisher DevX). Tips:
- Application server performance is affected by: the JDK version; connection pooling availability; JDBC version and optimized driver support; caching support; transactional efficiency; EJB component pooling mechanisms; efficiency of webserver-appserver connection; efficiency of persistence mechanisms.
- Decide on what is acceptable downtime for your application, and ensure the app server can deliver the required robustness. High availability may require: transparent fail-over; clustering; load balancing; efficient connection pooling; caching; duplicated servers; scalable CPU support.
Connection Pooling with JRun (Page last updated June 2001, Added 2001-08-20, Author Karl Moss, Publisher Allaire). Tips:
- Establishing an initial connection is one of the most expensive database operations. Use a pool of connections that are ready and waiting for use to minimize the connection overhead.
- Connection pooling is one of the largest performance improvements available for applications which are database intensive.
- Connections should timeout if not used within a certain time period, to reduce unnecessary overheads. Initial and maximum pool sizes provide further mechanisms for fine-tuning the pool.
- JDBC 2.0 supports connection pooling, though a particular driver may or may not use the support. If pooling is supported by the driver, it is probably more efficient than a proprietary pooling mechanism since it can leverage database specific features.
Rambling discussion of building J.Crew website, in Chapter 3 of "J2EE Technology in Practice" (Page last updated September 2001, Added 2001-10-22, Authors Dao Ren, Dr. Rick Cattell and Jim Inscore, Publisher Sun). Tips:
- Use database connection pooling
Various tips. For web servers? (Page last updated 2000, Added 2000-10-23, Author ?, Publisher ?). Tips:
- Empirically test for the optimal number of database connections.
Last Updated: 2021-04-28
Copyright © 2000-2021 Fasterj.com. All Rights Reserved.
All trademarks and registered trademarks appearing on JavaPerformanceTuning.com are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries. JavaPerformanceTuning.com is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.
RSS Feed: http://www.JavaPerformanceTuning.com/newsletters.rss
Trouble with this page? Please contact us