Populating the cachedRowSet

[Anupreet Arora]

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.

 
 
getConnection();
preparedStatement = connection.prepareStatement("My very complex query joining 14 tables");
//The following line which executes the query takes 4-5 secs
//which is essentially the time taken by the query
resultSet = preparedStatement.executeQuery();
 
//Strangely, populating the result set takes 15 secs
//which is causing the maximum pain in terms of response time.
[B]cachedRowSet.populate(resultSet); [/B]  
 

It will be great if anybody has experiences similar problem before or knows what is going on here. Any inputs are greatly appreciated.

Many Thanks
Anupreet

[Scott Johnson]

Here are a few things to check:

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.

[Anupreet Arora]

Thanks for the response Scott.

Have tried a number of things. Changing the database shared_pool_size and cursor_space_for_time parameters seem to be helping the most here.