wood burning stoves 2.0*
The moose likes JDBC and the fly likes Fetching and Manipulating Huge data from the database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Fetching and Manipulating Huge data from the database" Watch "Fetching and Manipulating Huge data from the database" New topic
Author

Fetching and Manipulating Huge data from the database

Shreyas Varun
Greenhorn

Joined: Oct 31, 2009
Posts: 3
Hi all,

I have a scenario in which I have to fetch 60-80K records from one schema, process them and then insert those into 5 different tables in another schema.

My question is how can I do these as "batches" or "sets of 1000s".

Two alternatives I was thinking about:

1. Read all the 80K objects and store them in a Collection. Then from the collection iterate through the collection and perform batch inserts in batches of 1000/5000 if possible. The disadvantage is this whole 80K will remain in the memory although processing is happening in batches.

2. If possible from the database itself read as batches of 1000/5000, so that only 5000 objects are being worked on at one time. I am thinking that in this case only 5K will remain in memory at a time.

But I dont know how to say get me these 80K records from Oracle in batches of 1000/5000.

Could you all please provide your thoughts ?

Is option 1 better or option 2 ?
If option 2 please provide ideas on how will I fetch records as batches.

Thankyou all
Shreyas
Rahul P Kumar
Ranch Hand

Joined: Sep 26, 2009
Posts: 188
create two or more threads(you have to find it how many are optimum), get 5k or so objects at one time and process them.
Shreyas Varun
Greenhorn

Joined: Oct 31, 2009
Posts: 3
Rahul,

Could you please elaborate ?
1. How will I get 5000 objects from the db at one time efficiently ?

Thanks for the suggestion for threading too. I will look into that as well.

But first please tell me how will I get 5K from db one time and then next time the next 5K and soon.

Thanks
Shreyas Varun
Greenhorn

Joined: Oct 31, 2009
Posts: 3
Okay,
How about if I use this query to fetch 1000 records at a time from the db.

SQL query:


and then in the java code I do this

1. select count(*) from Table_a and store in a variable



Using this I am stating give me the objects between 1 and 1000 first
then 1001 and 2001 like that

Will that work ?

Thanks
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Fetching and Manipulating Huge data from the database
 
Similar Threads
Display Table on Page with Limited Number of Rows
Pagination jsp/java
Which approach is better ?
Performance tuning for Oracle CLOBs with a remote database
Having memory problems creating excel file using POI API.