Hello,
Benchmarking is precisely what I am trying to do. However, if I
prepare a statement every time I need to execute the statement, it would not
be an accurate measurement of optimizations that preparedStatements can
offer. In fact, this would probably be worse than just executing the
statement once and for all. This is why I am wondering if this is possible
so I can test for performance before integrating this with our solution.
PreparedStatement can POTENTIALLY offer a great deal of optimizations, if
used PROPERLY.
Also, from what I have read so far, pre compiled statements do a very
nice job of preventing SQL Injections. I am certainly open to hearing
better suggestions from you. Although you sound like a manager, kindly note
that I am not your employee.
Sincerely,
Khawaja Shams
On 1/13/06, George Sexton <[EMAIL PROTECTED]> wrote:
>
> Let me just ask one simple question:
>
> Have you benchmarked your queries with prepared and unprepared statements
> to
> see if there is really a performance difference?
>
> Many people blindly follow "common knowledge" about performance, and this
> "common knowledge" is often wrong. People waste time and energy creating
> "optimized solutions" to issues that weren't broken.
>
> If you were my employee, I would first tell you to prove to me that there
> is
> a genuine difference, and that the difference was large enough to justify
> the additional maintainability/stability issues that might arise out of
> any
> solution.
>
> Lord Kelvin said:
>
> "If you can not measure it, you can not improve it."
>
>
> 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 11:12 PM
> > To: Tomcat Users List
> > Subject: Re: PreparedStatement w/ Connection Pooling
> >
> > I am guessing the main attribute to change is the following:
> > poolPreparedStatements="true"
> >
> > Suppose that this is set to true, how do I access these
> > pooled statement? My
> > setup is exactly as you described, so this modification seems
> > minor, but I
> > would appreciate guidance on how to get a hold of these
> > pooled prepared
> > statement. Thanks for your help.
> >
> > Sincerely,
> > Khawaja Shams
> >
> > On 1/12/06, Marc Richards <[EMAIL PROTECTED]> wrote:
> > >
> > > 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-exampl
> > es-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]
> > >
> > >
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
