Thanks for sharing your opinion Udai. I guess it will be best to create a small test to get an impression on how big the improvement would be. Will create a task for Ahmad to test this.
Regards Ralf Udai Gupta schrieb: > 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 > -- 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
