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 PreparedStatement 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:
- 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
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.
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
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:
- 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.
Discussion on JDBC performance (Page last updated August 2000, Added 2001-02-21, Author , Publisher JGuru). Tips:
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.
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.
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.
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.
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)")
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.
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.
Optimizing entity beans (Page last updated May 2001, Added 2001-05-21, Author Akara Sucharitakul, Publisher Sun). Tips:
- Always prepare your SQL statements.
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.
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.
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:
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.]
Last Updated: 2021-03-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