How to use PreparedStatements on a servlet the most efficient way?

Hello,

i 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)
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?)

Please let me know if you have any information about this topic.
Thanks in advance!

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
> that generated it is closed, re-executed, or used to retrieve the next
> result from a sequence of multiple results.
<http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html>

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

On 7 Feb., 15:59, Lew <l...@lewscanon.com> 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
> > that generated it is closed, re-executed, or used to retrieve the next
> > result from a sequence of multiple results.
>
> <http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html>
>
> 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

Wow, i am impressed!
Lew, thank you verry much for the information.
I don't have any performance problems yet, but i just started coding
the servlet and i wanted to be sure to use the best approach.
Considering what you sayed about ResultSets, i realize that it was a
foolish idea - and so i am even more thankful for your detailed
explanation.

In article <e4KdnQyixeNGgTbanZ2dnUVZ_oGjnZ2d@comcast.com>, Lew <lew@lewscanon.com> 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
>> that generated it is closed, re-executed, or used to retrieve the next
>> result from a sequence of multiple results.
><http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html>
>
>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