• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Devaka Cooray
  • Tim Cooke
Sheriffs:
  • Rob Spoor
  • Liutauras Vilda
  • paul wheaton
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Mikalai Zaikin
  • Carey Brown
  • Piet Souris
Bartenders:
  • Stephan van Hulst

Transfer Huge data using Java

 
Ranch Hand
Posts: 44
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
author & internet detective
Posts: 41988
911
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Antany,
ARe you allowed to use database import/export?
 
Antany Vasanth
Ranch Hand
Posts: 44
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Jeanne,

I am not allowed to use database import/export.

Thanks,
Antany
 
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 41988
911
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Self destruct mode activated. Instructions for deactivation encoded in this tiny ad.
We need your help - Coderanch server fundraiser
https://coderanch.com/wiki/782867/Coderanch-server-fundraiser
reply
    Bookmark Topic Watch Topic
  • New Topic