I posted the following question to the comp.lang.java.databases
newsgroup a few days ago, and thought I'd post my question and the
responses I received here. This seems to me to be something that jBoss
will want to take into account for performance reasons.
Rick
Rick Horowitz wrote:
> Is it appropriate to use prepared statements in jdbc across multiple
> transactions, and when using pooled database connections? For example,
> if I cannot guarantee that subsequent usages of the same
> PreparedStatement object will be used by the same Connection, can I
> still re-use PreparedStatement objects?
Hallo Rick,
You would (have to) use preparedStatements for each of your connections,
because they're tied to a connection.
In a Connection pool preparedStatements are a very good idea because
they're
much faster than dynamic statement objects, and in case you want to
insert or
alter data it's the only effective way because of the host varialbes
named
�?� in JDBC.
Greetings, Eduard.
Filip Larsen wrote:
>
> Rick Horowitz wrote:
>
> > Thanks for your reply. What would you suggest if I'm working in an EJB
> > environment? I don't have any control over the connection pool.
>
> If the server uses JDBC 2 it ought be possible to insert your own pool
> implmentation in the connection "chain" somewhere. I did this on a
> "homebrewn" EJB-server once, and we measured around 40% reduction in
> execution time from statement generation to end of query/update for
> simple sql statment.
>
> If the server is not using JDBC 2 then it is up to the servers
> implementation of the pool whether or not you are allowed to cache
> prepared statement; I would guess that in general servers do not have
> this capability.
>
> You may be able to write your own pool "in the beans", but I guess it
> requires that you can identify which connection goes with what
> statement, which is hard using the connections the bean get since these
> are not real connections but instead some (non-standardized) "adaptor"
> connection the server pool gives to the bean in order to handle
> transaction demarcation and connection close detection.
>
> Finally, to be honest, I think caching of prepared statements should be
> in the driver, since it is best to optimize it anyway. But I haven't
> seen any driver (to Oracle at least) that is able to do that.
>
> BR,
> --
> Filip Larsen <[EMAIL PROTECTED]>
