Morning Mark, 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
