• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

Retrieving huge ResultSet in chunks

 
Sheriff
Posts: 10445
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
We have a J2EE application deployed on Jboss. We have a requirement wherein, we have to execute a query to retrieve a huge ResultSet. This can run into millions of records. Naturally, we have already seen the OutOfMemory error. Is there any way(API) through which i can retrieve the ResultSet in chunks?
 
Jaikiran Pai
Sheriff
Posts: 10445
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Was thinking about various approaches on handling this, one thing that came to my mind was converting the table rows into a xml file. Is it possible to do this(i.e. without using the ResultSet object, to avoid memory related problems).

Any other suggestions are also welcome.
Thank you
 
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
How can your client process millions of records? I suspect it can't, so you do not need to obtain so much. One option is to do filtering of the client's request.
 
Jaikiran Pai
Sheriff
Posts: 10445
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Actually, this is a asynchronous activity and not visible to the end user(on the UI). After retrieving this data, we need to write it out to a file
 
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


There's probably some drivers that will buffer eveything anyway, but that should work on all the major databases (Oracle, SQL Server, postgres, mysql, db2, etc, etc)

If by "retrieve the ResultSet in chunks", you mean some way to explicitly limit the size of ResultSet to a fixed size and repeat execution to get back a ResultSet that will give you different rows each time, then no.

[ March 22, 2006: Message edited by: stu derby ]
[ March 22, 2006: Message edited by: stu derby ]
 
Author
Posts: 50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by jaikiran pai:
Hi,
We have a J2EE application deployed on Jboss. We have a requirement wherein, we have to execute a query to retrieve a huge ResultSet. This can run into millions of records. Naturally, we have already seen the OutOfMemory error. Is there any way(API) through which i can retrieve the ResultSet in chunks?



Which DB? In Oracle, you could set the fetch size and it won't run out of memory (assuming you use the Oracle supplied JDBC drivers.)
 
Ranch Hand
Posts: 381
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
As StuDerby alludes set up your cursor as type forward only. Many driver implementations for scrollable cursors do scrolling by loading all the rows into memory.

So don't do that. Before playing with fetch size or any other settings with large result sets make sure your cursor is forward only first.
 
Jaikiran Pai
Sheriff
Posts: 10445
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Which DB?


We are using Teradata.

First of all, thank you "Stu Derby" for proposing a solution.

If by "retrieve the ResultSet in chunks", you mean some way to explicitly limit the size of ResultSet to a fixed size and repeat execution to get back a ResultSet that will give you different rows each time, then no.



Well, thats exactly, what i wanted. Suppose it had 10000 rows and i set a fetch size of 500. One first invocation, i need to get the 1-500 rows then on second invocation 501-1000 rows and so on, which i think is not possible with the approach mentioned by "Stu Derby".
 
R. M. Menon
Author
Posts: 50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by jaikiran pai:


Well, thats exactly, what i wanted. Suppose it had 10000 rows and i set a fetch size of 500. One first invocation, i need to get the 1-500 rows then on second invocation 501-1000 rows and so on, which i think is not possible with the approach mentioned by "Stu Derby".



That is what fetch size does. It fetches in chunks that you specify. i don't see where the problem is.
 
WHAT is your favorite color? Blue, no yellow, ahhhhhhh! Tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic