File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes handling a larg size result set Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "handling a larg size result set" Watch "handling a larg size result set" New topic
Author

handling a larg size result set

Hanna Habashy
Ranch Hand

Joined: Aug 20, 2003
Posts: 532
hi all:
How can I handle a larg size result set? Lets say over 1000 records. I am using sql2k, and it doesn't support RowSet. Currently, I am using transfer object stratgy, but with that many objects stored in the user session, alot of memory resources is lost. Is there any other stratgies?
thank you


SCJD 1.4<br />SCJP 1.4<br />-----------------------------------<br />"With regard to excellence, it is not enough to know, but we must try to have and use it.<br />" Aristotle
Brian K Swingle
Ranch Hand

Joined: Jun 20, 2003
Posts: 39
I would try making your SQL statement more exact. Try to let you database do all the work and just return the record you want. Instead of looping through the result set. If thats what you doing. If you can post your code i might be able to be more help full
Best Regards,
Brian
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61756
    
  67

And why do you feel you need to store the data in the user session (I assume we are talking about a web app here)? You really haven't given us enough context on how the data is used to be able to make concrete recommedations.
[ April 13, 2004: Message edited by: Bear Bibeault ]

[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Hanna Habashy
Ranch Hand

Joined: Aug 20, 2003
Posts: 532
thank you all for your reply. I will try to explain in a simple example what I mean.
Consider a database table that contains ISP providers. Each provider has: first name, last name, id, cost, ...ext. Lets say each record contains about 5 fields. Now, assuming someone want to bring and be able to look at all this data at a time. The result set will contain all the ISP providers, which is very larg. I want to display the initial information to the user like the name of the provider in a jsp page, also, the user should be able to shoose one or more provider to display all the detaild information.
My approch was to encapsulate each ISP provider record in a transfer object and add it to a list. I mentain the list in a user session, so that the user can nevigate throgh the ISP provider records without having to go back to the database.
I am looking for a standerd design, and not just a solution for a problem.
thanks again
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61756
    
  67

If the data is the same no matter who is logged into the system, then storing the cached DB data in the application scope would be better than in each user's session.
In the case of 1000 records, it's a trade-off. Do you want to use up the memory so that DB access is limited, or save the memory and take the DB access hit?
If the dataset gets really big, then unless you have Google's server farm, you are better off taking the DB hit.
Taking a step back, I would say that large data sets are actually useless to the user (can they really assimilate that much data?) and you are better off allowing then to specify what subsets of the data they actually want to see.
[ April 13, 2004: Message edited by: Bear Bibeault ]
Brian K Swingle
Ranch Hand

Joined: Jun 20, 2003
Posts: 39
Yea I completely agree with Bear on this. He said it all.
Hanna Habashy
Ranch Hand

Joined: Aug 20, 2003
Posts: 532
Thanks guys
I totaly agree with you guys. But, becuase I have some bosses in my job, I can't do what I see appropriate. Our DBA doesn't want frequent access to the database, and my project manager wants to give the user the ability to do any search the user want. No one want to compermise or understand.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: handling a larg size result set