This week's book giveaway is in the Clojure forum.
We're giving away four copies of Clojure in Action and have Amit Rathore and Francis Avila on-line!
See this thread for details.
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Transfer Huge data using Java

 
Antany Vasanth
Ranch Hand
Posts: 44
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

I have a requirement of transferring huge amount of data (nearly 10 million records) from one database to another database using Java Program. I have done the same by creating connections with the two DB and queried the data from the source db and then inserted the data into destination db by iterating the result set. But It's taking huge time to transfer the data. Is there any way to do the transfer process quickly?

Note :I am using Java 6.

Thanks in Advance,
Antany.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33696
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Antany,
ARe you allowed to use database import/export?
 
Antany Vasanth
Ranch Hand
Posts: 44
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Jeanne,

I am not allowed to use database import/export.

Thanks,
Antany
 
Gregg Bolinger
GenRocket Founder
Ranch Hand
Posts: 15302
6
Chrome IntelliJ IDE Mac OS X
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So I know that you can do an INSERT/SELECT in a single statement. So something like:



However, I'm not sure how that would work using JDBC except for the fact that with MYSQL if the databases are on the same server and share the same credentials, it would work. Not sure about other databases though.

Aside from that, you'll probably just have to do your select and then batch the results. And it isn't going to be fast.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have used Gregg's solution and it worked well in MySQL, not sure how it would work in other databases.
Another option may be to use the incomplete-but-free DbTamer (in my signature) as this is one of the problems it is designed to manage.
Try Gregg's first as something that runs completely within the DB will be orders of magnitude faster.
 
Fatih Keles
Ranch Hand
Posts: 182
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When data size is huge I would prefer vendor specific solutions in first place if it is possible. Export/import, Database links, ODI etc.
But eventually your requirements will setup the solution base! What are the source and target database vendors,versions? Does the process need to be applied against changing databases, or in other words will it be generic?

Regards,

Fatih.
 
Antany Vasanth
Ranch Hand
Posts: 44
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Fatih,

Thanks for your reply,

I have to transfer data from SAS Dataset into Oracle tables. Due to some security restrictions we are not allowed to connect to oracle database from SAS Server to upload data. Hence we decided to use Java program to transfer the data, which leads to performance issue.

Thanks,
Antany
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33696
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Antany Vasanth wrote: we decided to use Java program to transfer the data, which leads to performance issue.

The performance issue is likely the network/data transfer rather than the Java program itself. Is there anything you can do to get one of the network hops out of the picture or make the data more compressed before transferring it to your Java program?
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic