Re: pooled prepared statements

Dave Cramer wrote:
>
>
> On Tue, May 12, 2009 at 11:58 AM, John Lister
> <john(dot)lister(at)kickstone(dot)com <mailto:john(dot)lister(at)kickstone(dot)com>> wrote:
>
> Thomas Finneid wrote:
>
> When a PreparedStatment is created by a pooled connection, as
> far as I understand if, that creation happens on the server
> side, and a reference, of sorts, is returned to the client jdbc.
>
> Is that prepared statement shared among the connections or is
> it only available to that single connection? and more
> importantly, can many connections use that prepared statement
> concurrently?
>
> If it is shared, then it must be usable by concurrent
> connections, otherwise it will be difficult for the client to
> know if the statement is occupied or not. So I just want to
> confirm that I understand how the JDBC driver works.
>
> Once the query usage count exceeds the prepareThreshold parameter,
> then the driver does create a "prepare statement object" on the
> server. This is only valid for the time the PreparedStatement is
> open and only on that connection. So to answer your question, they
> cannot be shared by multiple connections.
>
> You can create your own using the PREPARE and EXECUTE sql commands
> so long as you track them across different connections...
>
>
> AFAIK, you can't do that either assuming you mean prepare on one
> connection and execute on another ?
No, I meant, prepare on one connection, next time detect if you've
prepared on that connection and execute it, otherwise prepare again.
Hopefully at some point all connections in the pool would have the
connection prepared... Not sure how heavy that would be on resources -
and if possible with the current setup.

Also what would be nice is some form of server side caching of the
preparation (if requested by the client) so that things like JPA can get
the benefits of planning once across multiple prepareStatement objects.

Just a thought

JOHN