how to set fetch size for jdbc odbc driver

Question

Hi,

Im using sun.jdbc.odbc.JdbcOdbcDriver to connect to an oracle database.I know I would be probably be better off using the thin driver but I want the app to work without specifying the db server name and port no.My connection string is like jdbc:odbc:DSN.

The queries that I execute in my application may return millons of rows.All the data is critical so I cannot limit them within the query.My concern is for my java app running into memory issues.

When I check the fetch size of the statement it is set to 1.This seems extremely sub-optimal(A query retrieving 45K rows took abt 13 mins)to me and I would like to have a fetch size of atleast 500 so as to improve performance.

My understanding is that when my query is executed(I'm using Statement) the statement object points to the results on the database which I iterate using a ResultSet. The resultSet will hit the database to fetch n no of rows (where n is fetch size) each time I do a resultSet.next(). Is this interpretation correct? If so does it mean that my app will never face any out of memory issues until my fetch size is so large that the JVM gets swamped?

When I do a stmt.setFetchSize() after creating a statement I get an invalid fetch size sql exception.I am able to avoid this exception if I set the stmt.setMaxRows() to a larger value than the fetch size.But
1. I dont want my results to be limited to the MaxRows value.
2. Tried setting max rows to a huge value and tried with fetch size of 500 but saw no improvement in time taken.

Please help me figure out how I can set a valid fetch size and get some improvement.Any other optimization suggestions for the same driver would be appreciated.

Thanks,
Fell

Answer 1

Use JDBC. There is no advantage in using ODBC bridge. In PreparedStatement or CallableStatement you can call setFetchSize() to restrict the rowset size.

Answer 2

I haven't used a JDBC-ODBC bridge in quite a few years, but I would expect that the ODBC driver's fetch size would be controlling. What ODBC driver are you using and what version of the ODBC driver are you using? What is the fetch size specified in the DSN?

As a separate issue, I would seriously question your decision to use a JDBC-ODBC bridge in this day and age. A JDBC driver can use a TNS alias rather than explicitly specifying a host and port which is no harder to configure than an ODBC DSN. And getting rid of the requirement to install, configure, and maintain an ODBC driver and DSN vastly improves performance an maintainability.

Answer 3

Are you sure the fetch size makes any difference in Sun's ODBC-JDBC driver? We tried it a few years ago with Java 5. The value is set, even validated but never used. I suspect it's still the case.