Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsWhite Papers
Discussion GroupsFirst AidDatabasesJavaBeansGUIJava 3DVirtual MachineCORBASecurityToolsGeneral
Java DirectoryOpen Source ProjectsSample Book ChaptersUser GroupsWeb Resources
Related Topics
Databases.NETMore Topics ...

Java Forum / Databases / February 2008

Tip: Looking for answers? Try searching our database.

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

Thread view: 
mebe - 07 Feb 2008 10:54 GMT
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!
Lew - 07 Feb 2008 14:59 GMT
> 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
[quoted text clipped - 4 lines]
> 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.

Signature

Lew

mebe - 07 Feb 2008 16:21 GMT
> > i [sic] wonder whats the most efficient way to create PreparedStatements on
> > a
[quoted text clipped - 70 lines]
> --
> 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.
EricF - 08 Feb 2008 04:28 GMT
>> i [sic] wonder whats the most efficient way to create PreparedStatements on
>> a
[quoted text clipped - 68 lines]
>
>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


Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.