db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "vodarus vodarus" <voda...@gmail.com>
Subject Speed of using Derby DB
Date Wed, 11 Jun 2008 08:31:26 GMT
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.

Mime
View raw message