Getting values from ResultSet is slow (vs. Perl/DBI)

From: Mike Linksvayer <ml_at_justintime.com>
Date: 1997/12/22
Message-ID: <349F0E59.22E3BD92@justintime.com>#1/1

I have the following code running with Oracle's thin/type 4 JDBC driver against Oracle 7.2, everything running on an Ultra 1 with JDK 1.1.4:  

   String select = "select 13 columns from several tables...";    Statement s = conn.createStatement();    ResultSet r = s.executeQuery(select);    while (r.next()) {

      for (int i = 1; i <= 13; i++) {
         r.getString(i);
      }

   }  

Typically it takes 7 seconds to create the ResultSet, and an additional four to do an empty while(r.next()) {}, and another additional 7 (18 total) to do ResultSet.getString(int) on each row. (It takes 28 seconds total if I do ResultSet.getString(String) on each row).  

I've also tried running this with SunSoft's JDK 1.1.3 that comes bundled with Solaris 2.6 (includes a JIT) on another Ultra 1 and didn't see much difference.  

I also had high hopes for changing the number of rows JDBC fetches at a time with the following (default for this driver is 10, I tried 1,10,100,300,1000, and 3000):  

      ((oracle.jdbc.driver.OracleStatement)s).setRowPrefetch(int);  

With higher values it takes more time to create the ResultSet, and looping through the empty while above takes very little time (these basically cancel out). The amount of time taken to actually retrieve the column values doesn't change much, which makes me question what exactly is being prefetched.  

Finally I tried specifying that I'd be trying to get strings with the following code:  

   for (int i = 1; i <= 13; i++) {

      ((oracle.jdbc.driver.OracleStatement)s).defineColumnType(i,Types.VARCHAR);    }  

This caused the ResultSet.getString(int) on each row portion to drop from 7 seconds to 2 seconds (for 13 total). This seems to indicate that the column type was being checked again for each row!  

Running on the same machine against the same database the following Perl/DBI/DBD::Oracle code does much better:  

   $select = "select 13 columns from several tables...";
   $cursor = $dbh->prepare($select);
   $cursor->execute;

   while (@row = $cursor->fetchrow) {}  

Typically it takes 6 seconds to execute the query (equivalent to creating a ResultSet). Looping through the cursor AND putting the columns from each row into an array only takes an additional second.  

The query used in both of the above tests produces 3000 rows. Saving the results (e.g. putting them in an array of arrays) has negligible overhead in both Java and Perl.  

In both cases, it is taking 6 or 7 seconds for the database to finish executing its query (this is also how long it takes in sqlplus, as expected).  

Why does Perl loop through the cursor and retrieve the results so much faster? I can only think of these possibilities...  

• Perl is retrieving whole rows at a time, Java a column at a time. I don't see anything like ResultSet.getArray(String[]) in JDBC.
• ResultSet.getString() returns a new String, which is expensive.
• One or more of Java, Sun's VM, JDBC, and Oracle's driver are slow.

So... Anyone have any suggestions as to how I can speed up getting results out of JDBC? In summary, here's what I tried:  

• Use column indices instead of names with ResultSet.get*() methods (obvious)
• Tell the driver what data type you will request of each column (is there a generic way to do this in JDBC?)
• Tell the driver how many rows to fetch at a time (this didn't help me, but I would be interested to know if there is a generic way to do this in JDBC).

Thanks,

--
Mike Linksvayer     Just in Time Solutions, Inc.   tel +1 415 553 6408
ml@justintime.com   http://www.justintime.com/     fax +1 415 553 6499

Received on Mon Dec 22 1997 - 00:00:00 CST