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