On 31/03/12 17:20, Mark Rotteveel 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 hate Java, in case you are unaware, so I'm not up to speed on what
features it offers to anyone. However, I'd say that the developer
should be knowledgeable about his/her database(s), development tools and
languages.

So, the tool that allows connection pools to cache statements needs to
be fully understood and used, if appropriate.


> The application (and its developer) should not be bothered with keeping
> track of prepared statements.

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?

>> See
>> http://qdosmsq.dunbar-it.co.uk/blog/2009/02/it-must-be-efficient-im-using-bind-variables/
>> for details. :-)
>
> Interesting read. However keeping track of connections and statements as
> part of your application makes it more complex and puts responsibilities
> in your application that don't belong there.
It's Oracle based I admit. However, I disagree again. The application
should be tracking it's resources and cleaning up afterwards.

> 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.

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?

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.

Agree to disagree?


Cheers,
Norm.

--
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767