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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx