db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Speed of using Derby DB
Date Wed, 11 Jun 2008 23:40:25 GMT
vodarus vodarus wrote:
> Hello.
> 
> I tried to use Derby and compare it with Oracle. I thought that Derby 
> can have the same performance as Oracle on easy procedures.
> 
> Purpose of bench-mark test: use Derby as local db and get better 
> performance for local data-manipulations.
> 
> DB schema:
> 
> create table TESTBIG
> (
>     CLIENT       int not null,
>     ORDER_ID     int not null,
>     ORDER_AMOUNT int not null
> );
> 
> alter table TESTBIG add constraint TESTBIG_PK primary key (CLIENT, 
> ORDER_ID);
>    
> create table TESTTOTALS
> (
>     CLIENT       int not null,
>     CLIENT_TOTAL int
> );
> 
> alter table TESTTOTALS add constraint TESTTOTALS_PK primary key (CLIENT);
> 
> We populating TESTBIG table with 1 000 000 rows, then stored procedure 
> calculates TESTTOTAL: CLIENT_TOTAL is SUMM of all ORDER_AMOUNT for that 
> CLIENT.
> 
> i wrote stored procedure for Derby in Java language:
> 
> static public void calculateTotal() {
> 
>         int totalAmount = 0;
>         int lastClient = 0;
> 
>         try {
>             Connection connection = 
> DriverManager.getConnection("jdbc:default:connection");
>             Statement s = 
> connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
> ResultSet.CONCUR_READ_ONLY);
>             ResultSet rs = s.executeQuery("SELECT CLIENT, ORDER_ID, 
> ORDER_AMOUNT FROM TESTBIG");
> 
>             PreparedStatement updateData = 
> connection.prepareStatement("UPDATE testtotals SET " +
>                     " client_total = client_total + ? " +
>                     " WHERE client = ?");
>             PreparedStatement insertData = 
> connection.prepareStatement("INSERT INTO testtotals " +
>                     " (client, client_total) " +
>                     " VALUES (?, ?) ");
> 
>             while (rs.next()) {
>                 int client = rs.getInt(1);
>                 int order_amount = rs.getInt(3);
> 
>                 if (lastClient == 0) {
>                     lastClient = client;
>                     totalAmount = 0;
>                 }
> 
>                 if (lastClient != client) {
>                    
>                     // System.out.println("MERGE amount" + lastClient + 
> ":" + totalAmount);
>                     updateData.setInt(1, totalAmount);
>                     updateData.setInt(2, lastClient);
>                     int sqlRowCount = updateData.executeUpdate();
> 
>                     if (sqlRowCount == 0) {
>                         insertData.setInt(1, lastClient);
>                         insertData.setInt(2, totalAmount);
>                         sqlRowCount = insertData.executeUpdate();
>                     }
> 
>                     lastClient = client;
>                     totalAmount = order_amount;
>                 } else {
>                     totalAmount = totalAmount + order_amount;
>                 }
>             }
> 
>             updateData.setInt(1, totalAmount);
>             updateData.setInt(2, lastClient);
>             int sqlRowCount = updateData.executeUpdate();
> 
>             if (sqlRowCount == 0) {
>                 insertData.setInt(1, lastClient);
>                 insertData.setInt(2, totalAmount);
>                 sqlRowCount = insertData.executeUpdate();
>             }
> 
> 
>             rs.close();
>             s.close();
>             connection.commit();
>             connection.close();
>         } catch (SQLException ex) {
>             
> Logger.getLogger(CalculateTotal.class.getName()).log(Level.SEVERE, null, 
> ex);
>         }
>     }
> 
> 
> it takes up to 12 seconds to calculate TESTTOTALS. Oracle PL/SQL 
> procedure with the same algorithm need 1,5 second.
> 
> *How can i improve performance? Or Derby is so slow because of Java / 
> JVM issues???*
> 
> Thanks

For derby the default is autocommit=true, which means it will do a 
synchronous log commit write for every statement.  My reading of the app 
and the
call to commit at the end seems to indicate you want to only commit
at the end.  Depending on the data distribution (ie. how many times you
call executeUpdate this may be a big issue.  When you run the test do
you see a lot of idle time or is 1 cpu at 100% for 12-14 secs? The 
suggestion for using aggregates seems like a better way to do the app.

What kind of page cache does oracle have when you compare?  Are you 
interested in performance of a query when no page is in cache or when
all pages are in cache?  This size table is bigger than the default
derby page cache (1000 pages) so it basically is going to be an I/O test 
of how fast
stuff can be read from disk each time.  If you want to try a cached
test try setting derby page cache bigger - probably 10,000 pages will
fit that table (probably less - didn't do exact math).


Mime
View raw message