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 Export ResultSet to pipe delimited file Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Export ResultSet to pipe delimited file" Watch "Export ResultSet to pipe delimited file" New topic
Author

Export ResultSet to pipe delimited file

Balasubramaniam Muthusamy
Ranch Hand

Joined: Nov 30, 2010
Posts: 51
Hello Everyone,
I have been running multiple queries using JDBC and most of them returning more than 50 million records. As of I have been looping through ResultSet object and writing the records to file one by one. Though the query is completed in 1 hour, for packaging it is taking more than 5 hours.

Is there anyway we can dump the whole ResultSet into text file to reduce the packaging hours? Thank you so much in advance

Thanks
Bala
Abraham Moyo
Greenhorn

Joined: Feb 01, 2008
Posts: 7
Please elaborate on what the statement below means? Do you mean that you ran the query it took 1 Hour to return a result set, and then you are manipulating the data using java to write it to file and it takes 5 hours?

Quote:
Though the query is completed in 1 hour, for packaging it is taking more than 5 hours.

Balasubramaniam Muthusamy
Ranch Hand

Joined: Nov 30, 2010
Posts: 51
yes correct. Reading records one by one and writing into text file. Thank you
Sudheer Bhat
Ranch Hand

Joined: Feb 22, 2011
Posts: 75
I don't know if there are any API's to bulk read from the resultset. But my guess is that probably there isn't one. Because when the statement returns you the resultset, the DB would have fetched only first 'n' rows. I don't think any DB would fetch all eligible rows in one single shot (especially when the potential output of the row is very high). Oracle decides the first n rows by means of a parameter called array fetch size. Usually the next fetch will happen when needed.
Balasubramaniam Muthusamy
Ranch Hand

Joined: Nov 30, 2010
Posts: 51
Thanks for the update. is there anyway we can reduce the package creation time?
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
Hi Balasubramaniam,

I really doubt exporting 50M rows to a flat file with JDBC is a good idea. What is the purpose of this? If it is done by a end user, it is painful for him to wait for it.
If I were you according to underlying technology I would try to find a native tool which is closer to data and database to export data into a flat file. Then any other other application may use it.

What is your database software and environment specifications?

Regards,
Fatih.
Balasubramaniam Muthusamy
Ranch Hand

Joined: Nov 30, 2010
Posts: 51
Thank you so much

OS: Unix
Database: Teradata
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
I really know nothing about teradata, may be some other rancher can help you.

Good luck,
Fatih.
Balasubramaniam Muthusamy
Ranch Hand

Joined: Nov 30, 2010
Posts: 51
Thank you.. do you any idea for Oracle? Even that would help me
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Export ResultSet to pipe delimited file