Export ResultSet to pipe delimited file

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

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.

yes correct. Reading records one by one and writing into text file. Thank you

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.

Thanks for the update. is there anyway we can reduce the package creation time?

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.

Thank you so much

OS: Unix
Database: Teradata

I really know nothing about teradata, may be some other rancher can help you.

Good luck,
Fatih.

Thank you.. do you any idea for Oracle? Even that would help me