Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Pooling Prepared Statements

 
Nandakumar Subramaniam
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic