Thanks Andrea! That was a great overview of the trade-offs. I am looking at how we do connection pools in GeoTools - but more towards JNDI lookup then handling of prepared statements. Is there anything you would like me to look out for when I do this work?
It sounds from your email that a shared connection pool in a J2EE setting (ie shared with other web applications) may result in a glut of cached prepared statements? Jody > Switching to prepared statement does in general slow down things instead > of speeding them up, unless the connection pool cares about them in a > special way. > > Quite a bit of drivers do communicate with the dbms when the prepare > occurs, to report errors and to have the dbms create an execution plan, > and then again when you do execute them, sending only the parameters. > Two communication means we pay a lot on network latency, and gets paid > for each single statement, unless you reuse them. > > Enter the connection pool. Most connection pools nowadays can keep > an ancillary pool of prepared statement for each pooled connection, so > that if you're preparing again the same statement twice against the > same connection, the first communication won't be issued and the ps > reused: this results in a visible speedup because execution plan is > ready on the server side. > > If you really want big speedups thought, you need to use batching: > instead of doing execute() do an addBatch() for 10 or 100 statements, > and then issue a executeBatch() to send all of them in a single > call. The benefit in term of saved network time is really big. > Of course this means the feature writer must keep the prepared > statement reference as a field, and that not closing it may > leave the writer with pending changes inside. > > Please notice the second solution does not depend on having a > connection pool that is able to pool prepared statement. Yet, having > prepared statements pooled does not help only the write side, but the > read side > too. > > Hoping this is useful > Cheers > Andrea ------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys-and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV _______________________________________________ Geotools-devel mailing list Geotools-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-devel
