Hi Ralf, Ralf Joachim wrote: > 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. Sure. I am just trying to share what I know.
> Does one of you know a source that compares the different possibilities > to execute statements with JDBC? Not really, to be honest. > 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 --------------------------------------------------------------------- To unsubscribe from this list, please visit: http://xircles.codehaus.org/manage_email
