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 transaction performance tips
Get rid of your performance problems and memory leaks!
Get rid of your performance problems and memory leaks!
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:
- 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.
J2EE Application server performance (Page last updated April 2001, Added 2001-04-20, Author Misha Davidson, Publisher Java Developers Journal). Tips:
- 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.
Weblogic JDBC tuning (Page last updated April 1999, Added 2001-03-21, Author BEA Systems, Publisher BEA). Tips:
- 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.
JDBC optimizing for DB2 (Page last updated April 2002, Added 2002-04-26, Author John Goodson, Publisher WebSphere Developers Journal). Tips:
- Turn off autocommit, but don't leave transactions open for too long.
- Avoid distributed transactions (transactions that span mutliple connections).
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.
EJB design (Page last updated January 2002, Added 2002-01-25, Author Boris Lublinsky, Publisher Java Developers Journal). Tips:
- 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.
Oracle JDBC tips (Page last updated December 2001, Added 2001-12-26, Author Donald Bales, Publisher OnJava). Tips:
- Turn off autocommit, Connection.setAutoCommit(false).
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:
- If more than one SQL statement is executed by your program, you can gain a small performance increase by turning off auto-commit.
Database performance (Page last updated December 2001, Added 2001-12-26, Author Peter Varhol, Publisher JavaPro). Tips:
- Performance can be better if changes to the database are batched: turn off autocommit; add multiple SQL statements using the Statement.addBatch() method; execute Statement.executeBatch().
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.
- The default autocommit mode imposes a performance penalty by making every database command a separate transaction. Turn off autocommit (Connection.setAutoCommit(false)), and explicitly specify transactions.
- Savepoints (from JDBC3.0) require expensive resources. Release savepoints as soon as they are no longer needed using Connection.releaseSavepoint().
- Use Connection.setReadOnly(true) to optimize read-only database interactions.
Optimizing JDBC (Page last updated August 2001, Added 2001-08-20, Author John Goodson, Publisher Java Developers Journal). Tips:
- Turn autocommit off.
- Avoid using distributed transactions.
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 the lowest cost locking available from the database that is consistent with any transaction.
- 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).
JDBC performance tips (Page last updated November 2001, Added 2001-12-26, Authors Ravi Kalidindi and Rohini Datla, Publisher PreciseJava). Tips:
- Batch your transactions. Turn off autocommit and explicitly commit a set of statements.
- 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.
Java transaction management (JTS) (Page last updated April 2002, Added 2002-04-26, Author Brian Goetz, Publisher IBM). Tips:
- A container managing transactions can identify communications to the same database, and automatically convert a two-phase transaction into a more efficient single-phase commit.
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.
Last Updated: 2021-08-29
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