I am facing the following problem. I execute a query, which is large and complex, it takes a 4 to 5 seconds to execute and returns around 20 rows. But while populating the cachedrowset, takes more than the time it takes to execute the query. The database is Oracle 9i.
It will be great if anybody has experiences similar problem before or knows what is going on here. Any inputs are greatly appreciated.
1. Can you eliminate some columns or rows from your result set? 2. Call Statement.setFetchSize() before running the query. 3. Set the result set type on your statement to ResultSet.TYPE_FORWARD_ONLY. 4. Have your network engineer check for network congestion that may be slowing down retrieving the data.
I'm not an Oracle expert, but it could be that the JDBC driver is returning from the execute() method as soon as one or more rows is available for the result set. Oracle continues to run the query and generate results in the background. You are calling cachedRowSet.populate() which tries to read all of the rows, but they aren't all available yet so populate() waits for rest of the results.
You could test this theory by running the sql in sqlplus. If it's true, it will take about 15-20 seconds to return all rows.