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 design performance tips
JProfiler
|
Get rid of your performance problems and memory leaks!
|
JProfiler
|
Get rid of your performance problems and memory leaks!
|
|
|
Java performance tuning tips relevent for JDBC design.
Trying to limit the amount of data that is shunted around
is one biggie. How you design the transactions is another:
design the transactions to minimize exclusively tying
up resources, i.e. minimize concurrency conflicts.
Note also that using the simplest mapping, of one object to
one row, will hit performance sooner or later, and require
some kind of technique to efficiently handle iterating
through those objects.
The following pages have their detailed tips extracted below
The following detailed tips have been extracted from the raw tips page
http://www.onjava.com/pub/a/onjava/2001/12/05/optimization.html
Measuring JDBC performance (Page last updated December 2001, Added 2001-12-26, Author Jack Shirazi, Publisher OnJava). Tips:
- [Article discusses how to create JDBC wrapers to measure the performance of database calls].
- JDBC wrappers are simple and robust, and require very little alteration to the application using them (i.e, are low maintenance), so they are suitable to be retained within a deployed application.
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:
- If you are not using stored procedures or triggers, turn off autocommit. All transaction levels operate faster with autocommit turned off, and doing this means you must code commits. Coding commits while leaving autocommit on will result in extra commits being done for every db operation.
- Use the appropriate transaction level. Increasing performance costs for transaction levels are: TRANSACTION_NONE; TRANSACTION_READ_UNCOMMITTED; TRANSACTION_READ_COMMITTED; TRANSACTION_REPEATABLE_READ; TRANSACTION_SERIALIZABLE. Note that TRANSACTION_NONE, with autocommit set to true gives access to triggers, stored procedures, and large object columns.
- Store string and char data as Unicode (two-byte characters) in the database.
- Use batch updates (sending multiple rows to the database in one call).
http://www.sys-con.com/java/article.cfm?id=671
J2EE Application server performance (Page last updated April 2001, Added 2001-04-20, Author Misha Davidson, Publisher Java Developers Journal). Tips:
- Avoid n-way database joins: every join has a multiplicative effect on the amount of work the database has to do. The performance degradation may not be noticeable until large datasets are involved.
- Avoid bringing back thousands of rows of data: this can use a disproportionate amount of resources.
- Use optimistic transactions: write to the database while checking that new data is not be overwritten by using WHERE clauses containing the old data. However note that optimistic transactions can lead to worse performance if many transactions fail.
- For read-only queries involving large amounts of data, avoid EJB objects and use JavaBeans as an intermediary to access manipulate and store the data for JSP access.
http://www.weblogic.com/docs51/techdeploy/jdbcperf.html
Weblogic JDBC tuning (Page last updated April 1999, Added 2001-03-21, Author BEA Systems, Publisher BEA). Tips:
- Avoid moving data unless absolutely necessary. Process the data and produce results as close to its source as possible. Use stored procedures.
- Use built-in DBMS set-based processing to operate on multiple rows/tables in one request.
- Avoid row at a time processing, process multiple rows together wherever possible.
- Proper use of SQL can reduce resource requirements. Use queries which return the minimum of data needed: avoid
SELECT *
queries. A complex query that returns a small subset of data is more efficient than a simple query that returns more data than is needed.
- Never let a DBMS transaction span user input.
- Consider using optimistic locking. Optimistic locking employs timestamps to verify that data has not been changed by another user, otherwise the transaction fails.
- Use in-place updates, i.e. change data in rows/tables that already exist rather than adding or deleting rows/tables. Try to avoid moving rows or changing their sizes.
- Store operational data and historic data separately (or more generally store frequently used data separately from infrequently used data).
- DBMSs work well with parallelism. Try to design the application to do other things while interacting with the DBMS.
- Use pipelining and parallelism. Designing applications to support lots of parallel processes working on easily distinguished subsets of the work makes the application faster. If there are multiple steps to processing, try to design your application so that subsequent steps can start working on the portion of data that any prior process has finished, instead of having to wait until the prior process is complete.
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 the same connection to execute multiple statements.
- Keep connection objects open, and reuse them, rather than repeatedly connecting and disconnecting.
- Use the most efficiently handled data type: character strings are faster than integers, which are in turn more efficient than floating-point and timestamps.
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:
- If only using an entity bean for data access, use JDBC directly instead.
- Use a dedicated remote object to generate unique primary keys.
- Consider storing all database character data in Unicode to eliminate conversion overheads. But beware: this step will cause your database size to grow, as Unicode requires 2 bytes per character.
- Use block fetches when the query will give a large ResultSet and all rows are needed. Use the Page-by-Page Iterator pattern when only some of the rows may be needed.
- Consider using an in-memory database (product) for data that doesn't need to be persisted.
http://www.onjava.com/pub/a/onjava/2001/11/07/atomic.html
Atomic File Transactions. (Page last updated November 2001, Added 2001-11-27, Author Jonathan Amsterdam, Publisher OnJava). Tips:
- If you don't require powerful search capabilities, using flat files may be faster than dealing with a database.
http://www.sys-con.com/java/article.cfm?id=1268
EJB design (Page last updated January 2002, Added 2002-01-25, Author Boris Lublinsky, Publisher Java Developers Journal). Tips:
- Entity beans should not be simply mapped to database tables. Treating entity beans as such fine-grained objects which are effectively wrappers on table rows leads to increased network communications and heavier database communications than if entity beans are treated as coarse-grained components.
- For optimal performance, entity beans should be designed to: have large granularity, which usually means they should contain multiple Java classes and support multiple database tables; be associated with a certain amount of persistent data, typically multiple database tables, one of which should define the primary key for the whole bean; support meaningful business methods and encapsulate business rules to access the data.
- Entity beans are transactional resources due to their stateful nature, but application server vendors often rely on the underlying database to lock and resolve access appropriately. Although this approach greatly improves performance, it provides the potential for database lockup.
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 the most appropriate datatype specific kinds of data, e.g. store dates as a date type rather than varchar.
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:
- Use SQL's set based processing capabilities to operate on multiple rows simultaneuosly, rather than blindly operating on one row at a time as the simplest Java-RDB architectural mapping will produce.
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:
- Performance should be considered at the start of a project.
- Given a simple SQL statement and a stored procedure call that accomplishes the same task, the simple SQL statement will always execute faster because the stored procedure executes the same SQL statement but also has the overhead of the procedure call itself. On the other hand complex tasks requiring several SQL statements can be faster using stored procedures as fewer network trips and data transfers will be needed.
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:
- Thoughtful page design makes for a better user experience by enabling the application to seem faster than it really is.
- Use the flush method associated with the out object to display static text and graphics on the browser page before the database query returns, to prevent the user from having to look at a blank page for a long time.
- Scaled systems need optimized SQL calls, querying the right amount of data, and displaying pages before the query is complete.
http://www-105.ibm.com/developerworks/education.nsf/java-onlinecourse-bytitle/56A6275393F0BE5786256AFD004DBBB4?OpenDocument
JDBC tutorial (requires free registration) (Page last updated November 2001, Added 2001-12-26, Author Robert J. Brunner, Publisher IBM). Tips:
- The higher the level of transaction protection, the higher the performance penalty. Transaction levels in order of increasing level are: TRANSACTION_NONE, TRANSACTION_READ_UNCOMMITTED, TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ, TRANSACTION_SERIALIZABLE. Use Connection.setTransactionIsolation() to set the desired tansaction level.
- Savepoints (from JDBC3.0) require expensive resources. Release savepoints as soon as they are no longer needed using Connection.releaseSavepoint().
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:
- Retrieve data as efficiently as possible: Minimize the amount of data returned by the query; Don't make average users pay the same query cost of the users with extensive query requirements; Remember that users seldom want to see too much data in one go; Use setMaxRows(), setMaxFieldSize(), and SetFetchSize(); Decrease the column size; Use the smallest packet size that will meet your needs (if the driver supports packet sizing).
http://www.precisejava.com/javaperf/j2ee/EJB.htm
EJB performance tips (Page last updated November 2001, Added 2001-12-26, Authors Ravi Kalidindi and Rohini Datla, Publisher PreciseJava). Tips:
- Transactions should span the minimum time possible as transactions lock database rows.
- Use a dirty flag where supported by the EJB server to avoid writing unchanged EJBs to the database.
- Commit the data after the transaction completes rather than after each method call (where supported by EJB server).
- Do bulk updates to reduce database calls.
- Use JDBC directly rather than using entity beans when dealing with large amounts of data such as searching a large database.
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:
- Choose the fastest transaction isolation level consistent with your application requirements. Levels from fastest to slowest are: TRANSACTION_NONE, TRANSACTION_READ_UNCOMMITED, TRANSACTION_READ_COMMITED, TRANSACTION_REPEATABLE_READ, TRANSACTION_SERIALIZABLE.
- Write SQL queries that minimize the data returned.
- Use the Page-by-Page Iterator pattern to repeatedly pass small amounts of data rather than huge chunks.
http://www.onjava.com/pub/a/onjava/2001/12/19/eejbs.html
EJBs are wonderful (Page last updated December 2001, Added 2001-12-26, Author Tyler Jewell, Publisher OnJava). Tips:
- There are studies that demonstrate entity EJBs with CMP have lackluster performance when compared with a stateless session bean (SLSB) with JDBC. [Author points out however that SLSB/JDBC combination is less robust, less configurable, and less maintainable].
http://www.fawcette.com/javapro/2001_12/magazine/features/kkothapalli/
EJB performance tips (Page last updated December 2001, Added 2001-12-26, Author Krishna Kothapalli and Raghava Kothapalli, Publisher JavaPro). Tips:
- Use session bean wrapper for returning multiple data rows from an entity bean, rather than returning one row at a time.
- Use session beans for database batch operations, entity beans typically operate only one row at a time.
- Use entity beans when only a few rows are required for the entity, and when rows need to be frequently updated.
http://www.sys-con.com/java/article.cfm?id=713
Moving from JSP to EJB (Page last updated June 2001, Added 2001-06-18, Author Patrick Sean Neville, Publisher Java Developers Journal). Tips:
- Under heavy loads, entity beans should do more than merely represent a table in a database. If you are merely retrieving and updating data values, consider using JDBC within session beans instead.
- If you have one large database host but only a small Web and middleware host, consider moving much of your logic into stored procedures and calling them via JDBC in session beans.
http://www.devx.com/judgingjava/articles/maso/default.asp
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).
http://www.javaworld.com/javaworld/jw-02-2001/jw-0202-cachedrow.html
Article on using CachedRowSet, a ResultSet that doesn't need continuous connection to the database (Page last updated February 2001, Added 2001-02-21, Author Taylor G. Cowan, Publisher JavaWorld). Tips:
- CachedRowSet is probably not appropriate for managing large datasets.
http://www.javareport.com/html/from_pages/article.asp?id=799&mon=4&yr=2001
Various strategies for connecting to databases (Page last updated March 2001, Added 2001-04-20, Author Prakash Malani, Publisher Java Report). Tips:
- 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.
http://www.theserverside.com/resources/article.jsp?l=Tips-On-Performance-Testing-And-Optimization
Server performance testing (Page last updated 2000, Added 2001-05-21, Author Floyd Marinescu, Publisher The Server Side). Tips:
- Minimize network calls, especially database calls: make one large database call rather than many small ones; make sure ejbStore isn?t storing anything for read only operations; use Details Objects to get entity bean state rather than making many trips for each aspect of state.
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:
- Minimize database access in ejbStores. Use a "dirty" flag to avoid writing tee bean unless it has been changed.
- Avoid deadlocks. Note that the sequence of ejbStore calls is not defined, so the developer has no control over the access/locking sequence to database records.
http://www.java-zone.com/free/articles/sf0101/sf0101-1.asp
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:
- 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.
Last Updated: 2025-01-27
Copyright © 2000-2025 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.
URL: http://www.JavaPerformanceTuning.com/tips/jdbcdesign.shtml
RSS Feed: http://www.JavaPerformanceTuning.com/newsletters.rss
Trouble with this page? Please contact us