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?
Joined: Sep 06, 2003
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)
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.
Joined: Apr 16, 2005
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
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?
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?
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 ?