• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Export ResultSet to pipe delimited file

 
Balasubramaniam Muthusamy
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes correct. Reading records one by one and writing into text file. Thank you
 
Sudheer Bhat
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the update. is there anyway we can reduce the package creation time?
 
Fatih Keles
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you so much

OS: Unix
Database: Teradata
 
Fatih Keles
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I really know nothing about teradata, may be some other rancher can help you.

Good luck,
Fatih.
 
Balasubramaniam Muthusamy
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you.. do you any idea for Oracle? Even that would help me
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic