Re: [castor-dev] Performance aspect of refactoring of SQL engine

[Ralf Joachim]

Thanks for your opinion Werner and Jon. My feeling also says that we may not gain much by reusing of prepared statements. The problem is that such a feeling can be wrong and that you can find various best practise for JDBC telling to reuse prepared statements. I therefore thought it may be a good idea to test things before taking a decision. Does one of you know a source that compares the different possibilities to execute statements with JDBC? Regards Ralf Jon Wilmoth schrieb: I agree.  This is easily configured for a Tomcat connection pool (sample config below).  I think there's bigger performance related fish to fry with JDO.  For example http://jira.codehaus.org/browse/CASTOR-1409 cause one to load all relationships on a record when trying to save in order to prevent the relationship from being lost.   <Resource name="@DATABASE-POOL-JNDI-NAME@"               auth="Container"               type="javax.sql.DataSource"               url="">"@DATABASE_URL@"               driverClassName="@DATABASE_DRIVER@"               username="@DATABASE_USER@"               password="@DATABASE_PASSWORD@"               testOnBorrow="true"               validationQuery="@DATABASE_HEALTH_SQL@"               maxActive="@DATABASE_POOL_MAX_CONNECTIONS@"               maxIdle="@DATABASE_POOL_MIN_CONNECTIONS@"               maxWait="10000"               poolingStatements="true"/> p.s. My schedule hasn't allowed me to follow the Castor project for several months now, but I'm hoping to soon!   From: Werner Guttmann <wgut...@codehaus.org> To: dev@castor.codehaus.org Sent: Saturday, May 16, 2009 1:08:14 PM Subject: Re: [castor-dev] Performance aspect of refactoring of SQL engine Hi Ralf, most good JDBC drivers these days support prepared statement pooling. With that feature enabled, you will get most benefits for free. In other words, there's non need (in my view) to deal with this at the code level. Regards Werner Ralf Joachim 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 > --------------------------------------------------------------------- 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