aspose file tools*
The moose likes JDBC and the fly likes Pooling Prepared Statements Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Pooling Prepared Statements" Watch "Pooling Prepared Statements" New topic
Author

Pooling Prepared Statements

Nandakumar Subramaniam
Greenhorn

Joined: Aug 11, 2004
Posts: 4
Hi All,

I need to write that would return a PoolingConnection with the ability to pool the prepared statements. I will have to the apache open source project commons-pool and common-dbcp for the same purpose. I would like to see some code samples from some one who has done something similar.

Thanks,
Nanda.

javascript: x()
banghead
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

PreparedStatements are designed as an interface to pooled statements on the database - pooling them in Java code would be a pointless exercise!

When you pass a PreparedStatement to the database, it matches the PS to a previously run PS by the String value and reuses the precompiled and optimised database statement. One thing to be aware is that the String matching may be case sensitive, but otherwise 99% of the cost cutting is done by the database and all you'd be doing iis creating a memory overhead.

Dave
[ October 28, 2004: Message edited by: David O'Meara ]
Saskia de Jong
Ranch Hand

Joined: Jan 24, 2006
Posts: 34
Originally posted by David O'Meara:
PreparedStatements are designed as an interface to pooled statements on the database - pooling them in Java code would be a pointless exercise!


Are you sure about that? What about the situation where you close a connection for one request and then wish to re-use the execution plan for a next request?

Plans are bound to connections, and the pool might just give you a random connection back. I think DBCP has some special support for caching prepared statements.
Wei Dai
Ranch Hand

Joined: Jun 22, 2005
Posts: 86
Pooling Statement is good, but Pooling PreparedStatement shouldn't be a good idea. Maybe your PreparedStatement object will hold some opening tables before call execute method. Those opening tables will result in sql failure from other connections, which requires exclusive table lock.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Originally posted by Saskia de Jong:
Plans are bound to connections, and the pool might just give you a random connection back. I think DBCP has some special support for caching prepared statements.


Fair point, but if PreparedStatements are bound to a Connection (and I'm not convinced of this, and in any case it may be vendor specific) hpw many actual connections are active in your application?

Keeping in mind that the numbers below are specific to the app I work on and may not be transferable...

We use a Connection pool with default of two Connections for development and this works fine. In UAT we have ten. Ten Database connections in a pool can service a huge number of queries. So then a database intensive application will scroll through these connections fairly quickly, and eventually each Connection will have a cache of the statement.

Again I'll say you're not wrong, but unless it causes a problem I wouldn't worry about it
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
Originally posted by David O'Meara:
When you pass a PreparedStatement to the database, it matches the PS to a previously run PS by the String value and reuses the precompiled and optimised database statement.


Er, um.. close. In databases that have execution plan optimizations the SQL string, prepared or raw dynamic, will be matched to an execution plan. The exact dynamic string or unbound prepared statement string (ignoring potential bind value probing) will determine the plan used. However, there are still client-side resources for the prepared statement needed to manage the bind item communication. That is what you actually cache in Java for pools that cache prepared statements (e.g. Weblogic). Performance issues discussed here.


Reid - SCJP2 (April 2002)
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Pooling Prepared Statements