aspose file tools*
The moose likes JDBC and the fly likes Transfer Huge data using Java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Transfer Huge data using Java" Watch "Transfer Huge data using Java" New topic
Author

Transfer Huge data using Java

Antany Vasanth
Ranch Hand

Joined: Jan 28, 2009
Posts: 43
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

Joined: May 26, 2003
Posts: 30938
    
158

Antany,
ARe you allowed to use database import/export?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Antany Vasanth
Ranch Hand

Joined: Jan 28, 2009
Posts: 43
Hi Jeanne,

I am not allowed to use database import/export.

Thanks,
Antany
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

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.


GenRocket - Experts at Building Test Data
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

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

Joined: Sep 01, 2005
Posts: 182
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

Joined: Jan 28, 2009
Posts: 43
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

Joined: May 26, 2003
Posts: 30938
    
158

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?
 
wood burning stoves
 
subject: Transfer Huge data using Java