Hi, In my personal experience with JDBC I know that for reusing Prepared statements really gives a huge performance improvement as the JDBC.
Suppose we have "INSERT INTO <TABLE> VALUES (PARAMETER1,PARAMETER2)" and we want to execute it for like thousand times in a transaction. Now with simple Statement every time the Query will be validated(optimized internally) (1000 times) by the SQL compiler of DB and then data will be inserted to table. On the other hand with PreparedStatement the Query will the validated(optimized) only for the first time and for remaining 999 only parameters will be passed to Query(no validation and optimization overhead for other 999) Rest is just math. There is one more thing with PreparedStatement, excute() and excuteBatch(). excute() send parameters per query over the network (JDBC to DB server) while excuteBatch() sends the Batch(1000 times at ones) of parameters over networks. So using Batch gives high performance improvement if the network latency is costly(which mostly is the case as webserver and database server are separated these days). I have personally ran the tests to check these things. Regards, Udai Gupta On 5/15/09, Ralf Joachim <ralf.joac...@syscon.eu> wrote: > Hi all, > > as you may already know we have a GSoC student working at refactoring > SQL engine of Castor. While planing next improvement steps I had also > been thinking about possibilities to improve performance of execution of > SQL statements. I got one idea on how to improve performance but this > would require quite some refactoring work. As I do not know how big the > improvement could be, I like to hear your opinoins if it is worth to > spend time in this refactoring. > > > Current codebase of Castor works as follows: > > When a JDO configuration is loaded or used for the first time it creates > SQL strings with parameters for every entity of the configiguration and > every standard database operation executed for this entities. Standard > operations in this context are those that get executed by a call to > create(), load(), update() or remove() methods on a Database instance. > > When one of the operations from above get called Castor every time > prepares a java.sql.PreparedStatement, binds the required parameters and > executes the prepared statement. > > > Looking at best practis for prepared statements it is said that a > application should prepare a statement only once per connection as you > could reuse the prepared statement to bind different parameters and > execute it multiple times. As I expect that some database drivers and > about all database servers do such optimisations themself, my question > is, how big would be the performance improvement we could expect when > reusing prepared statements. > > As we need to prepare statements for every connection in any case, the > same prepared statement can only be reused within a single transaction. > According to this an application that uses an own transaction would not > gain any performance improvement. In the worst case we could get a very > small performance penulty by the code needed to handle creation and > closing of prepared statements in this case. > > On the other hand are there quite some applications that execute > hundreds or thoutheds of operations within one transaction. If all those > operations lead to the execution of the same SQL statement with > different parameters we should get a performance improvement according > to the best practis mentioned above. > > > Do anyone have experience with this? Any opinions? > > Regards > Ralf > > -- > > Syscon Ingenieurbüro für Meß- und Datentechnik GmbH > Ralf Joachim > Raiffeisenstraße 11 > 72127 Kusterdingen > Germany > > Tel. +49 7071 3690 52 > Mobil: +49 173 9630135 > Fax +49 7071 3690 98 > > Internet: www.syscon.eu > E-Mail: ralf.joac...@syscon.eu > > Sitz der Gesellschaft: D-72127 Kusterdingen > Registereintrag: Amtsgericht Stuttgart, HRB 382295 > Geschäftsleitung: Jens Joachim, Ralf Joachim > > > > > --------------------------------------------------------------------- > To unsubscribe from this list, please visit: > > http://xircles.codehaus.org/manage_email > > > --------------------------------------------------------------------- To unsubscribe from this list, please visit: http://xircles.codehaus.org/manage_email
