Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to fetch 50 million records from a database

 
saikrishna cinux
Ranch Hand
Posts: 689
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i neeed to display 50 million records to the user in a jsp page
so how can i do it?
which is the preferable way to do it in a multi user (scalabl) environment ???
 
karthikeyan Chockalingam
Ranch Hand
Posts: 259
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If the result set from the query execution is large consider using strategies such as CachedRowSet strategy, Read Only RowSet strategy, or RowSet Wrapper List strategy.

Also CachedRowSet sample impl at
http://www.onjava.com/pub/a/onjava/2004/06/23/cachedrowset.html
[ September 20, 2006: Message edited by: karthi keyan ]
 
saikrishna cinux
Ranch Hand
Posts: 689
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
is there any otherway to do it?
or otherwise can i make use of connection pooling for fetching 50 million records?
will it improve performance over resultset and rowset?
 
karthikeyan Chockalingam
Ranch Hand
Posts: 259
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
IMO Connection pooling is only to manage connections to improve scalability.

The idea is to fetch part of the query result at a given time (not entire 50 million records) and show it to the user (Lets say 100 records per page). This will occupy less memory when compared to 50 million records.(Anycase the user will not view all the 50 million recs)
 
sinasi susam
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can not show all datas at once.And from my point of view its no sense eighter.
Will the user check each of datas of 50 millions? No.

Paging may help you.
Show results page by page.and put links for pages at bottom.when user clicks a page link then show next pages.

you may need a session scope javabean to store the resultset.

good luck.
 
saikrishna cinux
Ranch Hand
Posts: 689
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes, you are partially correct . but here i am not trying to show all the 50 million records from the databse. First i am select all the 50 million records in a vector from that i need to show random 100 records to the user in each jsp page
so for fetching 50 million records i need to use vector or arraylist so will arraylist or vector holds all the 50 million rcords? or should i go for other technologies hey, my requirement is like this ,i cannot do anything for this ,because i need to select random 100 from 50 million records this is mandatory
advanced thanks for giving best solutions
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If the requirement is set in stone check your database documentation for way to limit results and select random records. For example in SQL Server you can do this:

or in Oracle you can do this:

Selecting all 50 million records into a Collection then randomly getting results from the Collection is unlikely to work. How much memory will you need to initialize a Collection of 50 million objects? How long will it take to return 50 million records from the database? Will the user be prepared to wait for your page to load while it gets all 50 million records?
 
sinasi susam
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why do you still need another object while ResultSet is doing the same thing for you?

set the fetch size of ResultSet to 100 and show whenever ,whatever you want..

ResultSets takes data from db at amount of fetch size.When came to the end of the fetch It goes to db and fetcs another 100 of data.
Why It doesnt take all datas?

Thats because the memory would not be enough for that...Otherwise, They (SUN) would have given that skill(fetch all at once) to ResultSet too.

Lets think like this ; Lets say you have a RAM of 500 KB and you selected something from db (in any language, not only with jdbc),and lets say that table with huge amounth of data contains 1GB data in the disc.How do you think to grab it to RAM? its 500 KB?

Vector, Arraylist or No Collection can afford it.

nice day.
 
Deepak Bala
Bartender
Posts: 6663
5
Firefox Browser Linux MyEclipse IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Lets say you have a RAM of 500 KB


I am sure you meant 512MB.

First up i wouldnt use a Vector as my choice for a data structure. Secondly there is no need to fetch 50 million records all at the same time. Even if you did want to show the user 100 random ones at a time you could may be pick 1000 records and cache them, then span these over 10 pages.

What kind of requirement justifies pulling 50 million records from a database ?
 
sinasi susam
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Alright MB, i might have been in habit of writing it.Because of using periodically here ,in daily life.

Thank you
 
saikrishna cinux
Ranch Hand
Posts: 689
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes, i want all the records to be selected first and from that data i want to select random of 100 in each page.

any way thanks for your wonderful solutions
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic