• Oracle Odbc Driver Kit

Jun 13, 2013 - In the ODBC Driver Setup dialog box, increase the Prefetch Count value. The ODBC driver buffer size is too small. Between frequency of queries to the database and amount of memory required for each fetch operation.

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. 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. 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?

Oracle Odbc Driver Kit

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.