Re: [GENERAL] Prepared statement performance...

    You may be able to configure your app server database pools to
cache PreparedStatements.  Some version of JBoss and WebLogic support
this, at any rate.  The idea is that just like connections aren't really
closed when you call close (just returned to the pool), PSs aren't really
closed when you call close (just kept in a cache for the connection).
This would let you take advantage of server side PSs in an app server
environment.
    The danger is that if each connection has a high PS cache size,
you can run into problems like "too many open cursors" on Oracle (when 50
connections each try to cache 50 PSs or whatever).  I'm not sure whether
PostgreSQL would complain or not.  Does it support multiple open
PreparedStatements per Connection?  And if so, are there any backend
limits to the total number of open server side PSs?

Aaron

On Mon, 30 Sep 2002, Simpson, Mike W wrote:
> I've been dying to have usable PreparedStatements in my web application, but
> AFAIK this is still not possible given your description below.  Assuming I
> can't dedicate a connection to each user (connections are pooled), even
> Server Side PreparedStatements are useless (since they fall out of context
> when the statement is destroyed, and I can't hold it without holding a
> client side PreparedStatement), right?
>
> Imagine an online catalog with millions of records, and we want to support
> searching and browsing.  Parsing the initial search query will be quite
> expensive if there are a great many columns on the table, but each
> subsequent NEXT_PAGE click really just needs to re run the same query with
> new params passed to the limit and offset clauses.  Virtually any website
> with a database backend will have some variation on this scenario, and
> caching the execution plans for these common queries could significantly
> improve performance in all these cases.
>
> I agree that server side prepared statements are useful even when we don't
> need to bind variables, but unless we have single user systems, neither
> definition gets us very far.  Maybe if we could cache prepared statements in
> application code and dynamically bind them back to connections on each
> request we could make use of this functionality, but I can't see how
> recreating the prepared statement will ever pay off with connection pools in
> place.
>
> Mike
>
>
>     Dimtry,
>
>     Server side prepare does not map to jdbc concept of
> PreparedStatement and it is important to understand how they are not the
> same.
>
>     Server side prepare means that you can parse and plan the statement
> once and reexecute it multiple times so:
>     select foo from bar;
>     becomes
>     prepare <name> as select foo from bar;
>     execute <name>;
>     deallocate <name>;
>
>     This applies to all sql statements. So server side prepared
> statements can equally be used for regular JDBC Statement objects as well as
> JDBC PreparedStatements.
>
>     JDBC PreparedStatements provide an interface to bind values into a
> sql statement.
>
>     Server side prepare provides the ability to reduce the overhead of
> parse/plan across muliple executions of a sql statement that may or may not
> have bind values.
>
>     They are different even though they both have the word 'prepare' in
> their names.
>
>     thanks,
>     --Barry
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>