File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
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
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!