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
