As far as when to use PreparedStatement vs. Statement, I'd (almost)
always go with a prepared statement: you can write cleaner code with
parameter placement than by concatenating strings into the SQL you want,
and it at least gives the database its best shot at caching query plans
and such. The only advantage of Statement is that you can spew the SQL
to a log and see the values you're trying to use, in case of trouble.
I'd think that it would make the most sense for the DB _server_ to cache
statements, not the driver.
danch
Rick Horowitz wrote:
>
> Hi Aaron,
>
> I'm not a DBA either...don't even play one, so please forgive any
> misunderstandings.
>
> The information you cite about Oracle is very interesting. I won't be
> using Oracle on my current project, however. Probably a free or
> inexpensive database, so I'm looking to make the best use of it. Also,
> I'm using BMP because I'm using a course-grained EJB entity approach and
> mapping many of my EJB entities to multiple db tables. So, jBoss' CMP
> approach will not be useful for me until it supports mapping of
> non-entity (dependent) objects to database tables.
>
> Filip Larsen's comment..
>
> > > >
> > > > 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.
>
> makes a lot of sense to me, but of course there's nothing that can be
> done in the connection pool about this, except to ask the driver its
> capabilities, as suggested by...
>
> Lennart Petersson wrote:
> >
> > 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.
> >
>
> If I understand this, Lennart is only referring to using prepared
> statements across commit/rollback, not from one connection to another.
> As I think about this further, and look at the PreparedStatement and
> Connection APIs, I cannot think of any approach (except for a
> non-standard hack) that could be implemented to allow PreparedStatement
> reuse across connections within a connection pool. Anyone think
> otherwise?
>
> If it is indeed the case that we are limited to using PreparedStatement
> within a given Connection, when is it recommended to use
> PreparedStatement vs Statement? Is there a performance tradeoff, like
> if the statement is used once, use Statement, else use
> PreparedStatement, or is it more complicated than that?
>
> Thanks again,
>
> Rick Horowitz
>
> Aaron Mulder wrote:
> >
> > (Caveat: I'm not a DBA, I just play one on TV)
> >
> > The DBMS really has a big impact the response here. For example,
> > Oracle has a global SQL cache, so if you use the same SQL repeatedly it
> > doesn't have to parse and optimize the query each time. In this case,
> > there's is no direct dependence on connections or prepared statements - if
> > you use a query and someone else uses the same query shortly thereafter,
> > it will come out of the cache.
> > However, if you construct SQL on the fly for statements, you will
> > usually not use the same SQL, whereas if you uses a propared statement you
> > can. For example:
> >
> > sql = "SELECT * FROM USER WHERE USERNAME='"+userName+"'";
> >
> > If you execute this several times with different contents of the
> > userName variable, you get many different statements.
> >
> > sql = "SELECT * FROM USER WHERE USERNAME=?;"
> >
> > This is only one statement, no matter how many different
> > parameters you pass it. So you can tap into the Oracle SQL cache better
> > by using prepared statements if you can make more queries look "the same".
> >
> > Aaron
> >
> > P.S. We use prepared statements in jBoss for container-managed
> > persistence.
> >
> > On Fri, 9 Jun 2000, Rick Horowitz wrote:
> > > 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]>
> > >
