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
