On 2-4-2012 8:58, Norman Dunbar wrote:

>> I disagree, the application (or application developer) should do that
>> (preparing statements every time it needs it). It is the driver and/or
>> connection pool that should keep a pool of prepared statements (see
>> section 11.6 of JDBC 4.1) for reuse.
> I disagree with your disagreement! ;-)

;)

> I think the developer should know exactly what the application is doing,
> otherwise the developer doesn't know what they should be coding for or
> protecting against surely? Especially when the cache isn't caching, for
> some reason?

If an application developer should ever be concerned with it, he should
isolate that concern to a separate layer. Preferably by implementing a
proxying JDBC datasource (or use one of a thirdy party) which allows for
that pooling/caching. He should not have code all around his application
to make that caching work.

>> It also duplicates what
>> connection pools (including statement pools) could be doing for you.
> Not really. Oracle natively supplies connection pools and cursor caching
> for you, if you know how to use them. And this brings me back to my
> initial point above, the developer *should* know how to use them.

As a developer, I would expect the Oracle JDBC driver or UCP driver to
take care of most of those details for me (and it does, eg see
http://docs.oracle.com/cd/E11882_01/java.112/e12265/optimize.htm#sthref213
).
My concern as a developer is getting the application to work and add
features, not getting muddled in the intricacies of the database(s) I use.

I am exaggerating, but my point is to have a separation of concerns: my
application code should not be concerned with this, doing that will
complicate my code, make it harder to debug and couple it tightly to a
specific database/driver implementation.

> I mean, take the example linked to above. What happens when this
> application is moved to a system that doesn't have the external
> connection pooling?

This pooling is either part of the application itself (by way of the
driver and its config) or of the application server running the
application. The driver taking care of the pooling would either be part
of the application, or be configured on the system according to the
deployment instructions of the application.

> On an Oracle system, you get lots of parsing, granted most of it will be
> soft parsing which isn't as resource intensive as hard parsing, but
> regardless, each parse takes a latch (two actually) and there is a queue
> of other statements, needing to be parsed waiting, for all these
> unnecessary parses. Performance suffers.
>
> Do it (what I call) right - prepare the statements as few times as
> possible (ie once) and use it as many times as possible, and you save
> resources, improve performance and make the user's life a whole lot better.

Most Java applications work like this:

1) Open connection from datasource (usually: obtain logical connection
from connection pool behind that datasource)
2) Prepare statement
3) Set parameters
4) execute
5) (optional) traverse resultset
6) close statement
7) repeat steps 3-5 or 2-6
8) Close connection (usually: close logical, physical is returned to pool)

The lifetime of steps 1-8 is usually very short (eg one request/response
cycle in a web application).

In this situation a developer cannot easily keep track of the
connection, and he shouldn't as he only 'owns' the connection for the
duration of steps 1-8!
For all intents and purposes to the application developer it will be as
if he retrieves a new connection every time (and depending on the
configuration and implementation class of the datasource it could be)!
So for the application developer there is no way to track statements
over multiple invocations of steps 1 to 8.
That is where statement pooling comes in. If statement pooling is
available/enabled, step 2 will check the statement pool of the physical
connection and retrieve the prepared statement if available (and
otherwise prepare a new one), while step 6 will return the statement to
the statement pool.

> Agree to disagree?

No problem.

--
Mark Rotteveel