aspose file tools*
The moose likes JDBC and the fly likes how to fetch 50 million records from a database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to fetch 50 million records from a database" Watch "how to fetch 50 million records from a database" New topic
Author

how to fetch 50 million records from a database

saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
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 ???


A = HARDWORK B = LUCK/FATE If C=(A+B) then C=SUCCESSFUL IN LIFE else C=FAILURE IN LIFE
SCJP 1.4
karthikeyan Chockalingam
Ranch Hand

Joined: Sep 06, 2003
Posts: 259
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 ]

http://www.skillassert.com


saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
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

Joined: Sep 06, 2003
Posts: 259
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

Joined: Jul 15, 2005
Posts: 67
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

Joined: Apr 16, 2005
Posts: 689
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

Joined: Apr 14, 2004
Posts: 10336

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?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
sinasi susam
Ranch Hand

Joined: Jul 15, 2005
Posts: 67
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

Joined: Feb 24, 2006
Posts: 6662
    
    5

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 ?


SCJP 6 articles - SCJP 5/6 mock exams - More SCJP Mocks
sinasi susam
Ranch Hand

Joined: Jul 15, 2005
Posts: 67
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

Joined: Apr 16, 2005
Posts: 689
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
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: how to fetch 50 million records from a database