At 12:22 PM 6/23/2000 -0700, you wrote:
>One of the points, I thought, of PreparedStatement was that you prepared it
>ONCE and executed it many times. How can one achieve this in a servlet that
>uses connection pooling? Is there a way to prepare statements ahead of time,
>so that when doGet is called, you only need to execute the sql?
Not really, if you're using connection pooling. One option would be to keep
a connection or two out of the pool and prepare your statements using that
unpooled connection. The other option would be to grab a connection from
the pool, create your statements, and put the connection back. Bad practice
either way, though.
Thankfully, you don't really lose much if anything in the way of
performance by recreating PreparedStatements. Most of the work of SQL
parsing is in the database, not the JDBC engine. Using a PreparedStatement
allows you to submit a more generic piece of SQL to the database (SELECT *
FROM TABLE WHERE COL = ? rather than WHERE COL = 123). Besides giving you
some programming flexibility and making it easier to escape strings, this
allows the database to use the cached SQL the next time you execute the
same statement, even if it's from a different instance of the
PreparedStatement object.
Bottom line: you get most if not all of the benefit even if you create the
PreparedStatement more than once. There are probably a few milliseconds to
be saved, but if you're having major performance issues the problem is in
structure of your SQL or the general state of your database (not enough
RAM, RAID 5, MS Access, etc.).
Hope that eases your mind,
Will
---
William Crawford + 01 617 577 7844
Invantage, Inc [EMAIL PROTECTED]
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
