Re: Global Named Prepared Statements

If Stored Procedures are equivalent to prepared statements [ as far as
preparing the query plan is concerned], then what i'm looking for is
perhaps a Global Prepared Statements at the client/driver side.

Specifically, It wold be good if the JDBC driver prepares all the queries
for invoking stored procedures at once per JVM so that each connection need
not incur the cost of preparing [parsing and storing] those queries per
connection.

Thus we can put all the queries [stored procedure calls] at a single place,
and prepare those queries during boot of the server [or deployment of the
web application], and then execute those queries endless times by closing
just the resultset object while keeping the statement open for ever.

I know this is not form to discuss the JDBC related questions but put my
thoughts here to complete the question i raised. If folks think this idea
is valid then i will take it up with the JDBC Driver team.

Thanks and Regards,
Samba

=====================================================
On Tue, May 15, 2012 at 6:46 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Tue, May 15, 2012 at 1:21 AM, Martijn van Oosterhout
> <kleptog(at)svana(dot)org> wrote:
> > On Tue, May 15, 2012 at 05:38:27AM +0530, Samba wrote:
> >> Hi,
> >>
> >> Does postgresql support Global Prepared Statements, which are prepared
> only
> >> once per server and not per every connection?
> >
> > As pointed out, no.
> >
> >> Problem with per-connection prepared statements is that the onus of
> >> preparing those statements for each connection lies with the client
> which
> >> makes those connections. Ideally, the performance of an application
> must be
> >> decided by the Server that hosts and not by the client that uses the
> >> service.
> >
> > How is this different from using CREATE FUNCTION to create a function
> > which has the desired effect? This is a well understood and commonly
> > used paradigm. When using a connection pooler any query plan caching
> > will happen automatically.
>
> this is not necessarily true, right? for example, 'sql' language
> functions don't cache plans while plpgsql functions mostly (no
> EXECUTE) do. other languages will typically have ability to save
> plans (or not). but yeah, functions generally cover this case quite
> nicely.
>
> i rarely use prepared statements anymore but if you're counting
> microseconds of latency for trivial queries, they still have a niche
> role...but to really see the benefit you'd want to be coding directly
> against the C api and making the appropriate calls (PQexecPrepared,
> etc).
>
> merlin
>