If you're already using pooling then I imagine you
already have a resource defined for the pool in either
server.xml or in the context xml file. To pool
prepared statements, which is desireable, you should
configure it to look something like this in Tomcat
5.5:
<Resource
type="javax.sql.DataSource"
auth="container"
name="jdbc/cPool"
username="user"
password="pwd"
driverClassName="com.ibm.as400.access.AS400JDBCDriver"
url="jdbc:as400://serverName;extended
dynamic=true;package
library=pkgLib;package=pkgName;package
add=true;package cache=true"
initialSize="5"
removeAbandoned="true"
removeAbandonedTimeout="600"
maxActive="0"
maxIdle="10"
minIdle="2"
maxWait="10"
poolPreparedStatements="true"
logAbandoned="false"
/>
Note that in this example I'm using an IBM iSeries DB2
database driver, not my sql, but the rest of the
config should be close to the same with the exception
that on a production machine you'll want to have your
initialSize, maxIdle and minIdle set much higher
depending on the number of concurrent connections that
you expect to make.
Also note the url parameter - DB2's jdbc driver and
database support dynamic package support with prepared
statements, which can sometimes offer huge sql
performance gains due to the system's ability to offer
better optimization for prepared statements and cache
the optimization so that the SQL optimizer need not be
initialized for every subsequent statement. I don't
know if my sql supports this or not, but you would
definitely want to look into it.
Lastly, note that this configuration is only
applicable to Tomcat 5.5 and possibly later versions.
Tomcat 5.0 and previous require a different syntax,
using a more structured xml format, which can be
reviewed here:
http://tomcat.apache.org/tomcat-5.0-doc/jndi-datasource-examples-howto.html
The best possible database configuration is as
described above - using connection pooling with
prepared statements and SQL package support - in cases
where stored procedures are more difficult to manage.
Stored procedures work great when you have several
queries that are required to run in sequence (and
usually offer better performance in such cases), but
may end up a tad overkill from a maintenance
perspective when you have a few isolated queries here
and there.
-marc
--- George Sexton <[EMAIL PROTECTED]> wrote:
> Generally speaking, if you were using most SQL
> databases the approach would
> be to use a stored procedure. The stored procedure
> compilation saves the
> various query plans as part of the data of the
> stored procedure. I doubt
> MySQL does it, but you could ask on their
> development list.
>
> George Sexton
> MH Software, Inc.
> http://www.mhsoftware.com/
> Voice: 303 438 9585
>
>
> > -----Original Message-----
> > From: Khawaja Shams [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, January 12, 2006 2:19 PM
> > To: Tomcat Users List
> > Subject: PreparedStatement w/ Connection Pooling
> >
> > Hello all,
> > I am working on a web application that will
> manage
> > assignments/students/classes for the teachers at a
> school.
> > Most of my pages
> > in the application need to access our mysql
> database very
> > frequently. For
> > this and for other security reason, I would like
> to use
> > preparedStatements.
> > However, from what I understand, preparedStatement
> are
> > prepared on top of a
> > connection. If I am using connection pooling,
> this seems to
> > be useless for
> > performance as I would be preparing a statement
> each time I get a
> > connection. Is there any way to get around this?
> Is there
> > such thing as a
> > preparedStatement pool? Would you recommend
> implementing this
> > on my own?
> > Thanks for your time and help.
> >
> >
> > Best Regards,
> > Khawaja Shams
> >
>
>
>
---------------------------------------------------------------------
> To unsubscribe, e-mail:
> [EMAIL PROTECTED]
> For additional commands, e-mail:
> [EMAIL PROTECTED]
>
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
