Re: jdbc performance in Solaris

From: Joe Smith <nospam_at_nospam.com>
Date: Thu, 20 Jan 2005 11:29:53 +0100
Message-ID: <41ef889a$0$6588$8fcfb975@news.wanadoo.fr>

Another long mail coming!

> So I understand the case, is the DBMS the same in both cases, and the
comparsion
> is between JDBC clients on MS or Sun? WHich JDBC driver are you using?
(Thin or
> OCI-based)?
> If the DBMS is not the same, is it remote in both cases, or local to
the
> client machine in both cases?

Here are the results using the programs and table listed below, always in localhost. I've run every test 3 times, to get an average. The Sqlplus version mesures are not very precise, as they use seconds instead of milliseconds. What I call autocommit in Solaris is just a commit after every insertion.

Windows

---

SqlplusTest, autocommit off: 11111, 5882, 7143 ==> average of 8000 insertions / second
SqlplusTest, autocommit on: 4545, 2381, 4167 ==> average of 3700 insertions / second
Ratio autocommit: 8000/ 3700 = 2.16

JdbcTest, autocommit off: 1422, 1538, 1531 ==> average of 1497 ins / second JdbcTest, autocommit on: 765, 745, 740 ==> average of 750 ins / second Ratio autocommit: 1497 / 750 ~ 2

Solaris

---

SqlplusTest, autocommit off: 500, 455, 500 ==> average of 485 insertions / second
SqlplusTest, autocommit on: 278, 313, 278 ==> average of 289 insertions / second
Ratio autocommit: 1.68

JdbcTest, autocommit off: 402, 311, 312 ==> average of 341 insertions / second
JdbcTest, autocommit on: 41, 39, 40 ==> average of 40 insertions / second Ratio autocommit: 8.53

In the real program, there will be a connection pool, so this is not such a big issue. We will, however, use the autocommit option, as our transactions are very simple (sort of a log per operation). This started more as a curiosity, to know if you had had similar experiences.

Thanks everybody

---

create table test(
 id varchar2(30),
 a number(14),
 b number(14),
 c varchar2(12),

 d number(3),
 e number(3),
 f number(3),
 g number(3),

 h varchar2(20),
 primary key(id));

---

CREATE OR REPLACE PROCEDURE SqlplusTest
  (inscount IN INTEGER, autocommit IN INTEGER) AS   today VARCHAR2(10);
  startDate DATE;
  endDate DATE;
  secondsDiff NUMBER(10);
  rate NUMBER(5);
BEGIN
DBMS_OUTPUT.PUT_LINE('Inserting '||inscount||' records'); SELECT TO_CHAR(sysdate,'HH24:MI:SS') INTO today FROM DUAL; DBMS_OUTPUT.PUT_LINE('Starting at:'||today); SELECT sysdate INTO startDate FROM DUAL; FOR i IN 1 .. inscount LOOP

   INSERT INTO test values (i||'@'||today,

         1123456789012, 1234567890123, 'aaaaaaa',
         1, 1, 1, 1, 'AAAAAAAAAAAA');
   IF autocommit = 1 THEN
     COMMIT;

   END IF;
END LOOP;
IF autocommit <> 1 THEN

   COMMIT;
END IF;
SELECT sysdate INTO endDate FROM DUAL;
SELECT TO_CHAR(sysdate,'HH24:MI:SS') INTO today FROM DUAL; SELECT (endDate - startDate)*24*60*60 INTO secondsDiff FROM DUAL; DBMS_OUTPUT.PUT_LINE('End of procedure:'||today||' in '||secondsDiff||' seconds');
IF secondsDiff = 0 THEN
  secondsDiff := 1;
END IF;
rate := inscount/secondsDiff;
DBMS_OUTPUT.PUT_LINE('Insertion rate: '||rate||' insertions/second'); END SqlplusTest;
/

---

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.Date;

public class JdbcTest {

 private Connection getConnection() throws Exception {   Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();   return DriverManager.getConnection(
    "jdbc:oracle:thin:@127.0.0.1:1521:TEST", "user", "pwd");  }

 private void test(int number, boolean autocommit) throws Exception {   long t0 = System.currentTimeMillis();
  SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");   String suffix = sdf.format(new Date(t0));   System.out.println(suffix);
  String sql = "INSERT INTO test " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";   Connection cnx = getConnection();
  cnx.setAutoCommit(autocommit);
  System.out.println("Starting " + number + " insertions");   PreparedStatement statement = cnx.prepareStatement(sql);   for (int j = 0; j < number; j++) {
   int i = 1;
   String id = String.valueOf(j) + suffix;

   statement.setObject(i++, id);
   statement.setObject(i++, new Long(1123456789012L));
   statement.setObject(i++, new Long(1234567890123L));
   statement.setObject(i++, "aaaaaaa");
   statement.setObject(i++, new Integer(1));
   statement.setObject(i++, new Integer(1));
   statement.setObject(i++, new Integer(1));
   statement.setObject(i++, new Integer(1));
   statement.setObject(i++, "AAAAAAAAAAAA");
   statement.executeUpdate();

  }
  statement.close();
  if (!autocommit)
   cnx.commit();
  long t1 = System.currentTimeMillis();
  long elapsed = t1 - t0;
  System.out.println(number + " insertions in " + elapsed + " ms="     + ((float) number * 1000 / elapsed) + " ins/sec");   cnx.close();
 }

 public static void main(String[] args) throws Exception {   int number = 1000;
  boolean autocommit = false;
  if (args.length > 1) {
   try {
    number = Integer.parseInt(args[0]);
   } catch (Exception e) {
   }
  }
  try {
   int ac = Integer.parseInt(args[1]);
   if (ac == 1)
    autocommit = true;
  } catch (Exception ex) {
  }
  System.out.println("Start:" + new Date(System.currentTimeMillis()));   new JdbcTest().test(number, autocommit);   System.out.println("End:" + new Date(System.currentTimeMillis()));  }
} Received on Thu Jan 20 2005 - 04:29:53 CST