Discussion Locked

JDBC 2 performance issue with Oracle 8i

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi !

We are using JDBC 2.0 to connect to Oracle 8.1.7.The web server is JRUN 3.1.

Both DB Server (Oracle) and Web server (JRUN) are hosted on physically different machines.

We are using JDBC type 4 driver (that is, classes12.zip).

We have queries which fetch CLOB data from Oracle. Some times the queries return more than
3000 rows. We are concerned about the response time for fetching this data.

The Question is, when the ResultSet object is fetched from the Database, does it go all
the way to the DB server every time for each rs.next() and fetch one row at a time, OR
it fetches all rows in one go and iterates only through ResultSet object in the middle tier
(that is Web Server) ?

If for each rs.next(), it goes all the way to DB server and brings back one row, then is there
any parameter we can set in JDBC or in JRUN so that it will fetch the entire result set in bulk
instead of making a return trip to DB server for each row ?

If you are interested enough, Our code is somewhat like this:

Connection con; // Connection Object
Compound objMatchComp; // Compound Entity object

String query = "";

PreparedStatement ppdStmt = con.prepareStatement(query);

ppdStmt.setLong(1,lSearchId);
ppdStmt.setString(2,strUserId);

try{

ResultSet rsCompounds = ppdStmt.executeQuery(query);
ArrayList arraylstMatchComps = new ArrayList();
while (rsCompounds.next()) {
objMatchComp = new Compound();
objMatchComp.setCompoundId(rsCompounds.getLong(1));
Clob clob = rsCompounds.getClob(2);
long i=1;
int j=(int)clob.length();
String struc = clob.getSubString(i,j);
objMatchComp.setStructure(struc);
}
catch(Exception e){
}

4 replies

by jay eckles

I'm pretty sure that the complete result of the query is stored in the ResultSet. One way to check this would be to execute the query, then close the connection to the database, then try rs.next() and see if you can still get at the information. Another way would be to examine the network traffice between the database and web servers; perform one query that will fetch one row, then perform your query that fetches 3000 rows, and compare the traffic. You'll want to do this in a cleanroom environment to get meaningful results.

Good luck.

by ckeruhi

Well there is a setfetchsize method for the resultset object wherein the fetch size can be set, default being 0, that is one row at a time.
Thanks for the suggestions.

by okujava

try the following:
((OracleStatement)stmt).setRowPrefetch (100);

that will set the prefetch blocksize to 100.

cheers

s.okujava

by ckeruhi

We have tried out the above suggestion. It did not seem to work. Thanks anyway.

Any more suggestions please ?

This discussion is closed to new replies.