Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Fetching huge dataset with JDBC

 
nitinram agarwal
Ranch Hand
Posts: 90
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,
I have a legacy code which uses JDBC for database connection and calls some procedure which returns dataset. Each row consists of around 30 columns and when the dataset is around 300K rows, my program is failing with Out of memory.

After some analysis, I found that the current program is not able to handle more than 150K records (it runs out of memory). On some basic performance tuning and code clean up, I made this number to 160K (meaning program is failing after fetching 160K rows).

however I am not able to tune it further. The code is usual in nature that it has a ResultSet object to fetch rows.
I am trying to find a way to nullify a specific resultSet row once it is processed but did not find anything in the API. I am trying further to see if there is anything else can be done but feel that if I am able to set the current resultSet position to null after it is processed then there should be some more improvement.

Can anyone please suggest if there is a way to do this? I am trying in parallel to find if there is some alternative using Spring JDBC as well (but this requires more development effort and some code reengineering so getting approval for this approach will be time consuming).

Regards
 
Knute Snortum
Bartender
Pie
Posts: 2898
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Moved to the JDBC forum.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why are you reading 300k (or even 160k) records into your Java code? Bulk data processing should be done in the database if possible, so are you sure this task needs to be done in Java, or could you use SQL instead?
 
nitinram agarwal
Ranch Hand
Posts: 90
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,
It is a legacy application and does the following
1. against some passed in crtieria, invokes the stored procedure
2. pass on the returned data to other application.

I am not fully aware of the end to end flow and application reengineering is definitely not an option as of now (due to overall complexity of the application + effort rerquired for reengineering which requires some budget approval etc).

For this, I am looking for a solution in the existing application.
 
Paul Clapham
Sheriff
Posts: 21567
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Don't read the entire ResultSet into memory all at once. If your application only processes one row at a time (which you didn't say) then there's no point in doing that. First of all make sure your ResultSet is forward-only and not scroll-sensitive (i.e. specify TYPE_FORWARD_ONLY when creating the Statement which produces it), and if you're using a JDBC driver which defaults to reading the whole ResultSet into memory then call the setFetchSize() method to tell it to read only so many records at a time.
 
Dave Tolls
Ranch Hand
Posts: 2099
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
nitinram agarwal wrote:
2. pass on the returned data to other application.


How does it pass this on?
Where is this other application?

Does this other app need all 30 columns?

nitinram agarwal wrote:
For this, I am looking for a solution in the existing application.


There may not be one.
If the other application is somewhere completely different and is expecting a complete set of data (ie all 300K rows) then you are up a creek without a paddle...you have hit the limit of your application, as currently designed.

In essence you have an app designed to work with up to a bucketful of data (max), and are now asking how to squeeze two buckets into it.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic