Re: [GENERAL] Prepared statement performance...

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