Re: How to use PreparedStatements on a servlet the most efficient way?
- From: efriedNoSpam@xxxxxxxxx (EricF)
- Date: Fri, 08 Feb 2008 04:28:50 GMT
In article <e4KdnQyixeNGgTbanZ2dnUVZ_oGjnZ2d@xxxxxxxxxxx>, Lew <lew@xxxxxxxxxxxxx> wrote:
mebe wrote:
i [sic] wonder whats the most efficient way to create PreparedStatements on
a
servlet. Currently i see the following two options:
1 Creating them in the method at which they are executed. (Seems
obvious)
And pretty much necessary. Although strictly speaking they don't have to be
created in the method necessarily, they do have to have carefully controlled
lifespans.
2 Creating all PreparedStatements in the servlets contructor and store
them
in the servlets class. This way only the parameters must be supplied
if a
PreparedStatement should be executed, and the time to create the
PreparedStatement could be saved.
But is this approach thread save? (What if two identical requests are
issued at the same time? Wouldn't they interfere each other?)
Never mind thread-safe, it's not even safe in a single thread, unless you take
the proper care.
PreparedStatements are tied to the connection that created them. In order to
keep PreparedStatements around you have to keep their connections open. Since
connections are usually a relatively scarce resource that is a problem. Of
course, one connection can support many Statements, so you could manage that
by jamming all the app's activity through a single connection, or a small set
of them, but that could be a bottleneck itself.
If you have a ResultSet open from a PreparedStatement and you re-execute the
Statement, you lose that ResultSet - it closes. (Disconnected RowSets are an
exception, I think.)
A ResultSet object is automatically closed when the Statement object<http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html>
that generated it is closed, re-executed, or used to retrieve the next
result from a sequence of multiple results.
That means you have to make darn sure that the PreparedStatement is not
re-used by one block of code while another still has a ResultSet open from it.
Which further implies that if you have two clients who need to execute the
same query concurrently, they cannot share a PreparedStatement; you will need
two. Generalize to /n/ for /n/ concurrent queries. If you want all of them
to be kept open, then you'll need /n/ PreparedStatements of each SQL command
or query at all times, even if the usual load is far smaller than /n/
concurrent clients.
There is an intermediate approach, much like a thread pool or a connection
pool a PreparedStatement pool of limited lifespan might be useful under the
right circumstances, but it's a lot of work. It also could increase fragility
of the system, a mighty price to pay for a few milliseconds.
Another intermediate approach might be to create a connection, or a primary
connection, for each session, and create the most commonly-used
PreparedStatements off that connection to service that one session. You still
have to be careful that that session doesn't use one of its PreparedStatements
while it still has an active ResultSet on it.
There's a lot of work managing PreparedStatements if you go beyond the default
"create'em when you need'em" approach. It could be worth it if the
PreparedStatement overhead justifies it, but it's not an optimization you'd
want to use early in development while getting the essential logic correct.
Build it right first, then speed it up. (Didn't Jon Bentley coin, "Make it
right, then make it fast" in his /Programming Pearls/ column? Even if not, he
sure put a lot of other wisdom in there.) Once you've gotten the logic right,
use actual metrics to determine what needs speed.
Is PreparedStatement creation a huge bottleneck in your application? What do
your measurements tell you? Please share those results with us.
Lew
Gread advice.
The bulk of the work in creating a PreparedStatement is done by the database,
and any decent modern database will cache them. There shouldn't be much
overhead, but as Lew suggested, you need metrics.
Eric
.
- References:
- Prev by Date: Re: ResultSet getDate(TIME_STAMP) returns java.lang.NumberFormatException: For input string:
- Next by Date: Re: Hibernate: table without primary key
- Previous by thread: Re: How to use PreparedStatements on a servlet the most efficient way?
- Next by thread: Re: Hibernate: table without primary key
- Index(es):
Relevant Pages
|
|
