You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-dev@db.apache.org by Chris Felaco <cf...@netscape.net> on 2003/01/15 22:39:29 UTC
Re: [PATCH] Fix Torque doUpdate method to not perform SELECT beforeUPDATE
Martin Poeschl <mp...@marmot.at> wrote:
>any performane test results you can share with us??
>
>if we really can boost performace that way i'm +1 for the change .. if
>we just win 1% i don't think the changes are worth the work needed
>
Yes indeed. First I'll describe the environment - Sun hardware (UltraSPARC-IIi) running Solaris 2.7. DB is Oracle 8.1.6. JVM = Sun JDK 1.2.2. Application server and DB are located on separate similarly configured machines on a 100 Mbit LAN with average traffic. The system this is running on has not been tuned and is generally considered to be underperforming. When the tests were run, the app-server and the DB were both mostly idle. But none of that really matters...
I did 2 performance tests. The first was using doUpdate to modify 1 column in a large dataset (2698 out 2716 rows in a moderately sized table). Using the original Village method the update took an average of 37.5 seconds while the JDBC version (my code) averaged 2.1 seconds. In this case, the cost of using the SELECT and then an iterative UPDATE skyrockets the larger the result set is. The DB is forced to use more rollback and temp space because of the cursor acting on the very records that are being updated. I've run into problems with large queries such as this in Oracle that resulted in crashing the DB.
The second test is more of a real-world usage test. I simply took 1 record from the same table, selected an OM object, and then updated 1 column/property in the object and saved it 1000 times.
for i in range(1, 1000):
cust.lastProdPur = BigDecimal(i)
cust.save()
In this case I averaged 27 seconds using the old code and 17.5 seconds using the new code, for a savings of 35%.
It should be noted that the second test probably produces better results for the Village code than it would in a real world scenario, because the DB will probably be able to cache the record that is being updated repeatedly, thereby saving time on the SELECT. Another difference between this test and the other is that in this test, all the columns in the record are being updated, while in the first test, only 1 column is being updated. Also, I'm obtaining a connection from the pool for every operation, which is going to add fixed time to the test. If you subtract out the fixed cost time, the savings percentage will go up much higher in the second test.
But to me, a 30% boost is well worth the risk of the change, so I have no interest in refining the tests. In fact on my system, a servlet that was doing inserts and updates on 5 different tables improved by 27%, even though much of the time is spent doing inserts
- Chris
__________________________________________________________________
The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp
Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/
Re: [PATCH] Fix Torque doUpdate method to not perform SELECT beforeUPDATE
Posted by Martin Poeschl <mp...@marmot.at>.
sounds like we have to act here ...
it would be good to add performance test to the runtime tests in the
torque repo .. so everybody can see the changes ...
basically we just need to add a repo for the performace tests and add
some junit tests to do insert, updates, delete, ...
maybe we find a way to generate a report which we can add to the docs ....
patches are welcome ;-)
martin
Chris Felaco wrote:
>Martin Poeschl <mp...@marmot.at> wrote:
>
>
>
>>any performane test results you can share with us??
>>
>>if we really can boost performace that way i'm +1 for the change .. if
>>we just win 1% i don't think the changes are worth the work needed
>>
>>
>>
>
>Yes indeed. First I'll describe the environment - Sun hardware (UltraSPARC-IIi) running Solaris 2.7. DB is Oracle 8.1.6. JVM = Sun JDK 1.2.2. Application server and DB are located on separate similarly configured machines on a 100 Mbit LAN with average traffic. The system this is running on has not been tuned and is generally considered to be underperforming. When the tests were run, the app-server and the DB were both mostly idle. But none of that really matters...
>
>I did 2 performance tests. The first was using doUpdate to modify 1 column in a large dataset (2698 out 2716 rows in a moderately sized table). Using the original Village method the update took an average of 37.5 seconds while the JDBC version (my code) averaged 2.1 seconds. In this case, the cost of using the SELECT and then an iterative UPDATE skyrockets the larger the result set is. The DB is forced to use more rollback and temp space because of the cursor acting on the very records that are being updated. I've run into problems with large queries such as this in Oracle that resulted in crashing the DB.
>
>The second test is more of a real-world usage test. I simply took 1 record from the same table, selected an OM object, and then updated 1 column/property in the object and saved it 1000 times.
>
> for i in range(1, 1000):
> cust.lastProdPur = BigDecimal(i)
> cust.save()
>
>In this case I averaged 27 seconds using the old code and 17.5 seconds using the new code, for a savings of 35%.
>
>It should be noted that the second test probably produces better results for the Village code than it would in a real world scenario, because the DB will probably be able to cache the record that is being updated repeatedly, thereby saving time on the SELECT. Another difference between this test and the other is that in this test, all the columns in the record are being updated, while in the first test, only 1 column is being updated. Also, I'm obtaining a connection from the pool for every operation, which is going to add fixed time to the test. If you subtract out the fixed cost time, the savings percentage will go up much higher in the second test.
>
>But to me, a 30% boost is well worth the risk of the change, so I have no interest in refining the tests. In fact on my system, a servlet that was doing inserts and updates on 5 different tables improved by 27%, even though much of the time is spent doing inserts
>
>- Chris
>
>
>__________________________________________________________________
>The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp
>
>Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/
>
>--
>To unsubscribe, e-mail: <ma...@jakarta.apache.org>
>For additional commands, e-mail: <ma...@jakarta.apache.org>
>
>
>