Discuss: PreparedStatement and Connection Pooling

R

Raj

I've looked high and low for a satisfactory answer but could not find
one.

I've always thought PreparedStatements were more efficient than
Statements since they are precompiled. Then I read somewhere that if
the preparedstatements were being closed, the database will have to
recompile the preparedstatements again. Somebody tell me that this is
not true since the connections are only being returned to the
connection pool and not actually being closed, the precompiled
preparedstatement is still out there.

Or if I am wrong, please correct me.

Thanks,
Raj
 
A

Adam Maass

Raj said:
I've looked high and low for a satisfactory answer but could not find
one.

I've always thought PreparedStatements were more efficient than
Statements since they are precompiled. Then I read somewhere that if
the preparedstatements were being closed, the database will have to
recompile the preparedstatements again. Somebody tell me that this is
not true since the connections are only being returned to the
connection pool and not actually being closed, the precompiled
preparedstatement is still out there.

Depends on the database and driver.

My understanding of Oracle is that *any* SQL text sent to it for execution
is first checked against a list of recently-executed SQL so that the
execution plans, if any, could be re-used. Some Oracle design books make a
big deal out of standardizing your SQL indentation style to increase the
chances of a match during this process. But of course, if your
PreparedStatement objects use the same SQL, then you will have matches after
the first one.

Of course, if you're using WebLogic connection pooling, there's an awful lot
that happens behind-the-scenes to avoid "premature" closing of database
resources. (IE, closing a PreparedStatement doesn't actually release
anything in the database except any open ResultSets -- and closing a
Connection simply returns it to the pool.)

Normally, I wouldn't worry about these kinds of things. There is one
advantage to PreparedStatement even if the driver does nothing more than
turn it into a regular plain-vanilla Statement behind your back: The
PreparedStatement contract handles string escape syntax for you. That is,
you don't have to worry about escaping strings with embedded apostrophes in
them; you let the driver do it for you.

-- Adam Maass
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top