db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From cfel...@netscape.net (Chris Felaco)
Subject Re: [PATCH] Fix Torque doUpdate method to not perform SELECT beforeUPDATE
Date Wed, 15 Jan 2003 21:39:29 GMT
Martin Poeschl <mpoeschl@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)

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/

View raw message