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