This week's book giveaway is in the OCAJP 8 forum.
We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes Pagination for data transformation Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of OCA Java SE 8 Programmer I Study Guide this week in the OCAJP 8 forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Pagination for data transformation" Watch "Pagination for data transformation" New topic

Pagination for data transformation

Suchak Jani
Ranch Hand

Joined: Jan 24, 2002
Posts: 70
I have a secnario in which i have the following.
1. A number of users(6 million or more)
2. A lot of data per user.(10 tables and each table containing around 25 rows)
Now i need to per user i need to transform data for all the rows.
The problem: I want to read 50 users at a time and loop trough the transformations. Then read 50 more users and od the same till i do them all.
I am confused as to what to use.
1. Srcollable Result Set: Will this along with cursors do the job, meaning will it only read 50 users at a time and do the transformations, and then read the next 50 users .....
2. Or Should i use setFetchSize , Will that only read 50 rows at a time...
I od know that this is best done in sql but the client is hell bent to doing this through a java program.
Suchak Jani
Peter den Haan
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
I don't see what a scrollable ResultSet will buy you, as you can just stream straight through the users you're processing -- no need to hop back and forth through your ResultSet.
Not sure if setting the FetchSize would buy you a lot, but it's worth experimenting with.
Are you planning to select the data for all users, and committing your transaction every 50 users, holding the ResultSets open over the commit (assuming a JDBC 3.0 driver)? Or are you selecting 50 users at a time?
Whatever you do, use batch updates.
- Peter
[ May 21, 2003: Message edited by: Peter den Haan ]
I agree. Here's the link:
subject: Pagination for data transformation
It's not a secret anymore!