File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC 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
Bookmark "Pagination for data transformation" Watch "Pagination for data transformation" New topic
Author

Pagination for data transformation

Suchak Jani
Ranch Hand

Joined: Jan 24, 2002
Posts: 68
Team,
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.
regards
Suchak Jani
Peter den Haan
author
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: http://aspose.com/file-tools
 
subject: Pagination for data transformation