Hi! This is something that definitly should be implemented (maybe already is?).
I know from the EJB server we are using now (Ejipt) that it is a really big
performace hit. Just make sure that the connection pool queries the database
metadata to be sure that the database can handle prepared statements over
commits/rollbacks. If the database can't handle a prepared statement over a
commit/rollback, then the prepared statement will be invalidated when there is
a commit/rollback and can't be used again befor recreated.
When we used Ejipt against SQLBase we discovered a bug in SQLBase. It said that
it handled prepared statements over commits/rollbacks, but they didn't and we
got SQLExceptions after a while. Ok, so we turned off the prepared statements
feature in Ejipt manually and then we saw that it was a really huge performance
drop!
/Lennart
Rick Horowitz skrev:
> 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]>
--
________________________________
Lennart Petersson
Benefit AB
Tyn�sgatan 14
S-652 24 Karlstad
Phone: +46 (0)54 177253
mailto:[EMAIL PROTECTED]
Yahoo! Messenger, my nickname is: en_grillad_special
http://www.benefit.se
