Hi,

 

You really can’t compare Derby against Oracle.

 

They are two different beasts.

 

You can’t embed Oracle in your application. You can embed Derby.

You spend $$$$ on an Oracle solution. You spend $ on Derby. TANSTAAFL!

 

Oracle has a paid support staff of developers. Derby? Their support staff is paid by IBM and SUN. Ooops! IBM stopped supporting Cloudscape, no? Or else community members support Derby outside of their $Dayjob.

 

Oracle big, written in C/C++ etc.

Derby? 100% Java, small.

 

If you want a better comparison, write a Java Stored Procedure in Oracle and then compare the time. Even then you will have differences that will effect your performance.

Oracle can take advantage of partitioning database tables, certain caching, and of course query optimization will vary.

 

I’m not sure if  Informix’s Standard Engine supported SPL, but if it did, it would be a closer comparison, although SE is C based and should perform faster.

 

HTH

 

-G


From: vodarus vodarus [mailto:vodarus@gmail.com]
Sent: Wednesday, June 11, 2008 3:31 AM
To: derby-user@db.apache.org
Subject: Speed of using Derby DB

 

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.