Thread: pooled prepared statements

Hi

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.

regards

thomas

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...

I'll admit this would be a nice feature for things like JPA where you
end up creating lots of preparedStatements on different connections,
reusing or caching a single one may be a worthwhile performance
optimisation - although not sure about how you would implement it nicely..

JOHN

Probably easier to create a server side function instead, then.

regards

thomas

John Lister 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...
>
> I'll admit this would be a nice feature for things like JPA where you
> end up creating lots of preparedStatements on different connections,
> reusing or caching a single one may be a worthwhile performance
> optimisation - although not sure about how you would implement it nicely..
>
> JOHN
>

Dave Cramer wrote:
>
>
> On Tue, May 12, 2009 at 11:58 AM, John Lister
> <john.lister@kickstone.com <mailto:john.lister@kickstone.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

> Probably easier to create a server side function instead, then.
But wouldn't you still have to go through all the planning steps within
the function for any queries, although i'll admit i'm not familiar with
Postgresql functions.

JOHN

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.

More or less, yes. (Assuming you're reusing the statement, not just
using it once and discarding it).

> Is that prepared statement shared among the connections or is it only
> available to that single connection?

It is associated only with the connection that prepared the statement.
The preparation of the statement on the server side is state that's
specific to the connection, and it's not shared between server backend
processes - so the JDBC driver couldn't share statements between
connections even if it wanted to.

> and more importantly, can many
> connections use that prepared statement concurrently?

No.

-O

John Lister wrote:
>
>> Probably easier to create a server side function instead, then.
> But wouldn't you still have to go through all the planning steps within
> the function for any queries, although i'll admit i'm not familiar with
> Postgresql functions.

Server-side functions are compiled when installed. Since my function
would only contain simple queries that are parameterized, it would
pre-compile well.

thomas

>>> Probably easier to create a server side function instead, then.
>> But wouldn't you still have to go through all the planning steps within
>> the function for any queries, although i'll admit i'm not familiar with
>> Postgresql functions.
>
> Server-side functions are compiled when installed. Since my function would
> only contain simple queries that are parameterized, it would pre-compile
> well.

It was the simpler queries I was thinking of and the overhead in the
planning(preparing) stage compared with fetching the results rather than any
complexity around them For example fetching session data using a session_id
on a web server which is done many times over..

Although i'm not sure how much overhead there is in the planning stages
compared with actually fetching the data and if the tradeoff of complexity
is worth it...

JOHN

Maybe your right. The query processing takes from 1-10 seconds, so
planning it is not a big factor. But when the load becomes higher, as it
will be as the data increases, the planning might possibly eat time
which could be used elsewhere.

My server, as it is now, is pushing the limits of postgres for my use,
so maybe I need to reclaim as much of wasted time as possible...

We'll see.

thomas

Dave Cramer wrote:
>
>
> On Wed, May 13, 2009 at 10:37 AM, John Lister <john.lister@kickstone.com
> <mailto:john.lister@kickstone.com>> wrote:
>
>                 Probably easier to create a server side function
>                 instead, then.
>
>             But wouldn't you still have to go through all the planning
>             steps within the function for any queries, although i'll
>             admit i'm not familiar with Postgresql functions.
>
>
>         Server-side functions are compiled when installed. Since my
>         function would only contain simple queries that are
>         parameterized, it would pre-compile well.
>
>
> While the function may be compiled, the overhead is the same for
> preparing the statement inside the function. So I don't think it's a
> huge win.
>

I still think you will have multiple prepared statements one per connection regardless of if it uses a procedure, and you would have to prepare the statement on first use and then store it in the session.

Dave

On Wed, 13 May 2009, Oliver Jowett 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.
>
> More or less, yes. (Assuming you're reusing the statement, not just
> using it once and discarding it).
>

It really depends on the driver and connection pool that you are using.
The idea of having a PreparedStatement cache (that doesn't require
retaining a reference to the PreparedStatement) has been suggested for the
driver, but rejected.  A connection pool which proxies the real connection
can provide this functionality instead.  The pooler provided with the
postgresql driver does not do this, but for example DBCP can.

So as long as your pool or driver are reasonably smart, don't overthink
this and let them handle it for you.

Kris Jurka