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

[Ralf Joachim]

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. [phone]
Mobil: [phone]
Fax [phone]

Internet: www.syscon.eu
E-Mail: ***@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

[Udai Gupta]

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

Post by Ralf Joachim
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.
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. [phone]
Mobil: [phone]
Fax [phone]
Internet: www.syscon.eu
Sitz der Gesellschaft: D-72127 Kusterdingen
Registereintrag: Amtsgericht Stuttgart, HRB 382295
Geschäftsleitung: Jens Joachim, Ralf Joachim
---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

[Ralf Joachim]

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

Post by Udai Gupta
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

Post by Ralf Joachim
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.
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. [phone]
Mobil: [phone]
Fax [phone]
Internet: www.syscon.eu
Sitz der Gesellschaft: D-72127 Kusterdingen
Registereintrag: Amtsgericht Stuttgart, HRB 382295
Geschäftsleitung: Jens Joachim, Ralf Joachim
---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

--
Syscon Ingenieurbüro für Meß- und Datentechnik GmbH
Ralf Joachim
Raiffeisenstraße 11
72127 Kusterdingen
Germany

Tel. [phone]
Mobil: [phone]
Fax [phone]

Internet: www.syscon.eu
E-Mail: ***@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

[Udai Gupta]

Hi Ralf,

Yes creating test is an option to measure (to get an idea) the
improvement because I also did some similar when I came to know about
the performance improvement using PrepareStatment and its Batch
feature. It was a Live project and I used simple Statements with long
transactions everywhere and I was bit busy with other things that time
(or you can say lazy :p ), so I wanted to make sure if the improvement
is enough rewarding to spend that much time. It was like a miracle for
me to see the performance improvement in the transactions (49 x 900 x
6 quires at a time) that time and more when I found about Batch. So I
decided to investigate more about PrepareStatement and these things I
found.

1. 1000 queries, 1(first) registered(validated, optimized)and
remaining 999 will follow the first one (no validation, no
optimization).

Now, this validation(syntax) and query optimization(internal engine
process) varies from engine to engine. In whole this is more of an
mathematical (probably Relational Calculus and Query Optimization)
analysis to give the answer how much improvement we will get because
people have variables like
var1 - Query (Sub variables - Complexity, Where, SubQuery, Parameters etc)
var2 - No. of queries per transactions
var3 - others

So the cost of var1 will be involved for every query while using
Statement, but var1 will cost only once if we use PrepareStatement. So
here what give us the improvement is var1 mostly and improvement
depends on the cost of the var1.

2. excuteBatch(), It's a network latency issue (depends on the DB and
Application server network) . In my case it was huge as the
Application server and DB server was at different physical location.

3. It is recommended by OWASP (owasp.org) that PreparedStatment is
secure from SQL injections because the query registers first with the
DB and the later parameter get into it by using bind, so one can't
extend it to modify the query.

eg: query = "SELECT * FROM <MYTABLE> WHERE id = "+p;

Now the Parameter p is coming from the request.getParameter which if
not check someone could a vulnerability.
if I send p = 123; DROP <MYTABLE>

But this PreparedStatement this doesn't occur even if you be careless
with parameters(but careful with query).

I might be repeating myself but I think that test is a good idea, and
I would like to finally add that you can see as much as improvement as
you want by increasing the cost of var1 and var2 in Point 1 and for
excuteBatch() by Network Latency.

Cheers,
Udai Gupta

Post by Ralf Joachim
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

Post by Udai Gupta
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

Post by Ralf Joachim
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.
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. [phone]
Mobil: [phone]
Fax [phone]
Internet: www.syscon.eu
Sitz der Gesellschaft: D-72127 Kusterdingen
Registereintrag: Amtsgericht Stuttgart, HRB 382295
Geschäftsleitung: Jens Joachim, Ralf Joachim
---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

--
Syscon Ingenieurbüro für Meß- und Datentechnik GmbH
Ralf Joachim
Raiffeisenstraße 11
72127 Kusterdingen
Germany
Tel. [phone]
Mobil: [phone]
Fax [phone]
Internet: www.syscon.eu
Sitz der Gesellschaft: D-72127 Kusterdingen
Registereintrag: Amtsgericht Stuttgart, HRB 382295
Geschäftsleitung: Jens Joachim, Ralf Joachim
---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

[Werner Guttmann]

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

Post by Ralf Joachim
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.
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

[Jon Wilmoth]

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 <***@codehaus.org>
To: ***@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

Post by Ralf Joachim
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.
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

[Udai Gupta]

Hi All,

I think pooling gives performance improvement due to maintaining the
physical connection by reducing the access time to database
(authentication, creation on socket) which applies for
PrepareStatement and Statement.

While the PrepareStatement gives Performance improvement because of
one time registration of query in a transaction which I think Ralf
asked.

But yes I agree that Pooling gives huge performance improvement is
used wisely because i have seen cases where Pooling can cost resources
by making DB slow if not configured correctly (and these configuration
varies from application to application).

Correct me If I am wrong.

I do not get how PrepareStatement pooling works, I think pooling works
regardless of what kind of Statement you are using.

Regards,
Udai Gupta

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.
auth="Container"
type="javax.sql.DataSource"
testOnBorrow="true"
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!
________________________________
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

Post by Ralf Joachim
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.
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

---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

[Ralf Joachim]

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Thanks for your opinion Werner and Jon.<br>
<br>
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.<br>
<br>
Does one of you know a source that compares the different possibilities
to execute statements with JDBC?<br>
<br>
Regards<br>
Ralf<br>
<br>
Jon Wilmoth schrieb:
<blockquote cite="mid:***@web35605.mail.mud.yahoo.com"
type="cite">
<style type="text/css"><!-- DIV {margin:0px;} --></style>
<div
style="font-family: times new roman,new york,times,serif; font-size: 12pt;">
<div>I agree.&nbsp; This is easily configured for a Tomcat connection pool
(sample config below).&nbsp; I think there's bigger performance related fish
to fry with JDO.&nbsp; For example <a moz-do-not-send="true"
href="http://jira.codehaus.org/browse/CASTOR-1409">http://jira.codehaus.org/browse/CASTOR-1409</a>&nbsp;cause
one to load all relationships on a record when trying to save in order
to prevent the relationship from being lost.</div>
<div
style="font-size: 12pt; font-family: times new roman,new york,times,serif;">&nbsp;</div>
<div
style="font-size: 12pt; font-family: times new roman,new york,times,serif;"><span
class="c9Y6TC"><font color="#0000ff">&lt;</font></span><span
class="cLUY2"><font color="#ea8f0f">Resource </font></span><span
class="c18YC0"><font color="#d00020">name=</font></span><span
class="c5M9S0"><font color="#000090">"@DATABASE-POOL-JNDI-NAME@"</font></span><span
class="c18YC0"><br>
<font color="#d00020">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;auth=</font></span><span
class="c5M9S0"><font color="#000090">"Container"</font></span><span
class="c18YC0"><font color="#d00020"> <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;type=</font></span><span class="c5M9S0"><font
color="#000090">"javax.sql.DataSource"</font></span><span
class="c18YC0"><br>
<font color="#d00020">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;url=</font></span><span
class="c5M9S0"><font color="#000090">"@DATABASE_URL@"</font></span><span
class="c18YC0"><br>
<font color="#d00020">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;driverClassName=</font></span><span
class="c5M9S0"><font color="#000090">"@DATABASE_DRIVER@"</font></span><span
class="c18YC0"><br>
<font color="#d00020">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;username=</font></span><span
class="c5M9S0"><font color="#000090">"@DATABASE_USER@"</font></span><span
class="c18YC0"><br>
<font color="#d00020">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;password=</font></span><span
class="c5M9S0"><font color="#000090">"@DATABASE_PASSWORD@"</font></span><span
class="c18YC0"><br>
<font color="#d00020">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;testOnBorrow=</font></span><span
class="c5M9S0"><font color="#000090">"true"</font></span><span
class="c18YC0"><br>
<font color="#d00020">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;validationQuery=</font></span><span
class="c5M9S0"><font color="#000090">"@DATABASE_HEALTH_SQL@"</font></span><span
class="c18YC0"><br>
<font color="#d00020">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;maxActive=</font></span><span
class="c5M9S0"><font color="#000090">"@DATABASE_POOL_MAX_CONNECTIONS@"</font></span><span
class="c18YC0"><br>
<font color="#d00020">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;maxIdle=</font></span><span
class="c5M9S0"><font color="#000090">"@DATABASE_POOL_MIN_CONNECTIONS@"</font></span><span
class="c18YC0"><br>
<font color="#d00020">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;maxWait=</font></span><span
class="c5M9S0"><font color="#000090">"10000"</font></span><span
class="c18YC0"><br>
<font color="#d00020">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;poolingStatements=</font></span><span
class="c5M9S0"><font color="#000090">"true"</font></span><span
class="c18YC0"><font color="#d00020">/</font></span><span
class="c9Y6TC"><font color="#0000ff">&gt;</font></span> </div>
<div
style="font-size: 12pt; font-family: times new roman,new york,times,serif;"><br>
p.s. My schedule hasn't allowed me to follow the Castor project for
several months now, but I'm hoping to soon!</div>
<div
style="font-size: 12pt; font-family: times new roman,new york,times,serif;">&nbsp;</div>
<div style="font-size: 13px; font-family: arial,helvetica,sans-serif;"><font
face="Tahoma" size="2">
<hr size="1"><b><span style="font-weight: bold;">From:</span></b>
Werner Guttmann <a class="moz-txt-link-rfc2396E" href="mailto:***@codehaus.org">&lt;***@codehaus.org&gt;</a><br>
<b><span style="font-weight: bold;">To:</span></b>
<a class="moz-txt-link-abbreviated" href="mailto:***@castor.codehaus.org">***@castor.codehaus.org</a><br>
<b><span style="font-weight: bold;">Sent:</span></b> Saturday, May
16, 2009 1:08:14 PM<br>
<b><span style="font-weight: bold;">Subject:</span></b> Re:
[castor-dev] Performance aspect of refactoring of SQL engine<br>
</font><br>
Hi Ralf,<br>
<br>
most good JDBC drivers these days support prepared statement pooling.<br>
With that feature enabled, you will get most benefits for free. In other<br>
words, there's non need (in my view) to deal with this at the code
level.<br>
<br>
Regards<br>
Werner<br>
<br>
Ralf Joachim wrote:<br>
&gt; Hi all,<br>
&gt; <br>
&gt; as you may already know we have a GSoC student working at
refactoring<br>
&gt; SQL engine of Castor. While planing next improvement steps I had
also<br>
&gt; been thinking about possibilities to improve performance of
execution of<br>
&gt; SQL statements. I got one idea on how to improve performance but
this<br>
&gt; would require quite some refactoring work. As I do not know how
big the<br>
&gt; improvement could be, I like to hear your opinoins if it is worth
to<br>
&gt; spend time in this refactoring.<br>
&gt; <br>
&gt; <br>
&gt; Current codebase of Castor works as follows:<br>
&gt; <br>
&gt; When a JDO configuration is loaded or used for the first time it
creates<br>
&gt; SQL strings with parameters for every entity of the
configiguration and<br>
&gt; every standard database operation executed for this entities.
Standard<br>
&gt; operations in this context are those that get executed by a call to<br>
&gt; create(), load(), update() or remove() methods on a Database
instance.<br>
&gt; <br>
&gt; When one of the operations from above get called Castor every time<br>
&gt; prepares a java.sql.PreparedStatement, binds the required
parameters and<br>
&gt; executes the prepared statement.<br>
&gt; <br>
&gt; <br>
&gt; Looking at best practis for prepared statements it is said that a<br>
&gt; application should prepare a statement only once per connection as
you<br>
&gt; could reuse the prepared statement to bind different parameters and<br>
&gt; execute it multiple times. As I expect that some database drivers
and<br>
&gt; about all database servers do such optimisations themself, my
question<br>
&gt; is, how big would be the performance improvement we could expect
when<br>
&gt; reusing prepared statements.<br>
&gt; <br>
&gt; As we need to prepare statements for every connection in any case,
the<br>
&gt; same prepared statement can only be reused within a single
transaction.<br>
&gt; According to this an application that uses an own transaction
would not<br>
&gt; gain any performance improvement. In the worst case we could get a
very<br>
&gt; small performance penulty by the code needed to handle creation and<br>
&gt; closing of prepared statements in this case.<br>
&gt; <br>
&gt; On the other hand are there quite some applications that execute<br>
&gt; hundreds or thoutheds of operations within one transaction. If all
those<br>
&gt; operations lead to the execution of the same SQL statement with<br>
&gt; different parameters we should get a performance improvement
according<br>
&gt; to the best practis mentioned above.<br>
&gt; <br>
&gt; <br>
&gt; Do anyone have experience with this? Any opinions?<br>
&gt; <br>
&gt; Regards<br>
&gt; Ralf<br>
&gt; <br>
<br>
---------------------------------------------------------------------<br>
To unsubscribe from this list, please visit:<br>
<br>
&nbsp; &nbsp; <a class="moz-txt-link-freetext" href="http://xircles.codehaus.org/manage_email">http://xircles.codehaus.org/manage_email</a><br>
<br>
<br>
</div>
</div>
</blockquote>
<br>
<pre class="moz-signature" cols="72">--

Syscon Ingenieurb&uuml;ro f&uuml;r Me&szlig;- und Datentechnik GmbH
Ralf Joachim
Raiffeisenstra&szlig;e 11
72127 Kusterdingen
Germany

Tel. [phone]
Mobil: [phone]
Fax [phone]

Internet: <a class="moz-txt-link-abbreviated" href="http://www.syscon.eu">www.syscon.eu</a>
E-Mail: <a class="moz-txt-link-abbreviated" href="mailto:***@syscon.eu">***@syscon.eu</a>

Sitz der Gesellschaft: D-72127 Kusterdingen
Registereintrag: Amtsgericht Stuttgart, HRB 382295
Gesch&auml;ftsleitung: Jens Joachim, Ralf Joachim
</pre>
</body>
</html>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

[Werner Guttmann]

Hi Ralf,

Post by 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.

Sure. I am just trying to share what I know.

Post by Ralf Joachim
Does one of you know a source that compares the different possibilities
to execute statements with JDBC?

Not really, to be honest.

Post by Ralf Joachim
Regards
Ralf

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.
auth="Container"
type="javax.sql.DataSource"
testOnBorrow="true"
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!
------------------------------------------------------------------------
*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

Post by Ralf Joachim
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.
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

---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

--
Syscon Ingenieurbüro für Meß- und Datentechnik GmbH
Ralf Joachim
Raiffeisenstraße 11
72127 Kusterdingen
Germany
Tel. [phone]
Mobil: [phone]
Fax [phone]
Internet: www.syscon.eu
Sitz der Gesellschaft: D-72127 Kusterdingen
Registereintrag: Amtsgericht Stuttgart, HRB 382295
Geschäftsleitung: Jens Joachim, Ralf Joachim
--------------------------------------------------------------------- To
http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email