Hi Andrea, You sure know your JDBC stuff!! :). I would love to see us use batched prepared statements across the board, and in a more structured efficient manner. Problem is as usual the jdbc beast is somewhat untamable.
Not sure how to proceed. Perhaps I will do some benchmarking to see how significant the performance hit is on using the prepared statement. -Justin Andrea Aime wrote: > Jody Garnett ha scritto: >> Thanks - I glanced over (and the prepaired statement approach seems >> similar to what is done on the Oracle side of things). >> >> I understand that this is a security concern, however did you notice >> any other improvement switching over to using prepaired statements? > > 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 > > !DSPAM:1004,45dd4e27244041702038478! > -- Justin Deoliveira The Open Planning Project http://topp.openplans.org ------------------------------------------------------------------------- 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
