my dog learned polymorphism
The moose likes JDBC and Relational Databases and the fly likes Populating the cachedRowSet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Populating the cachedRowSet" Watch "Populating the cachedRowSet" New topic

Populating the cachedRowSet

Anupreet Arora
Ranch Hand

Joined: Jun 17, 2003
Posts: 81
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.

Many Thanks
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
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
Ranch Hand

Joined: Jun 17, 2003
Posts: 81
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.
I agree. Here's the link:
subject: Populating the cachedRowSet
It's not a secret anymore!