What about using the stored procedures provided by almost all databases. You
can use them almost anywhere you want to use the prepared statements.
If you do not want to use stored procedures, you can still get a connection
from the pool, create a prepared statement, use it n times, and then return
the connection to the pool when you are through with the prepared statement.
You can also create a pool of prepared statements with dedicated
connections.
.....
.....
Vivek
-----Original Message-----
From: William Crawford [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 23, 2000 6:32 PM
To: [EMAIL PROTECTED]
Subject: Re: PreparedStatement and ConnectionPools
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
___________________________________________________________________________
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
