Hibernate 3.3.1.GA
JBOSS 4.3.0.GA
Oracle 10g, Driver ojdbc14.jar version 10.2.0.4.0

Hi,

I'm developing a batch process application requiring to transfer from one instance of Oracle to another instance of Oracle, respectively 'Prod' and 'Arch'.
so I need to

- load entities from Prod
- persist them in Arch
- remove them from Prod

Basically my problem is how do I load one entity, persist it in one data source, remove it from another datasource WITHOUT MERGING?

With the biggest load of data to move :

- selecting and copying the set of entities take 5 minutes
- but merging and deleting takes 25 minutes.

I'm trying to have some archiving service/agent running in the background of applications.
Going full JDBC is also an option, and going down to stocked procedure would probably be the fastest way.

However, I rather try benefiting from object relational mapping in terms of reusable business object.
So, I want to believe that there is a way with JTA, Hibernate, to avoid the performance issue that I'm having with this merging step.

all in all, I haven't that many posts about dealing with multi base environment. It must have been considered and experimented before to manipulating important amount of data between two databases and more.

Please someone, say "yes"

In detail:

JBOSS: 4.3.0.GA
Hibernate : 3.3.1.GA

The total process involves :

- 50 tables among which are tables with millions of rows
- one entity per existing table

So I have

- two XA datasources, one for each database, 'Prod' and 'Arch'.
- two persistence Units.
- two entity managers
- 50 entity beans
- 50 services, one for each entity
- 1 main implementation in service

The process, step by step :

- instantiating one entity manager for Prod : emProd
- instantiating one entity manager for Arch: emArch
- from Prod, selecting in the first table one record : emProd.findEntity()
- returns one main entity ME1
- from Prod, in the rest of the 49 tables, one select per table to match data linked to this ME1 : emProd.findEntity1LinkedtoME1()
- returns collections of entities
- persisting all those entities in Arch using : emArch.persist(<entity>)
- because persisting detaches my entities I have to merge them : emProd.merge(<entity>)
- before calling for deletion : emProd.remove()

And this has to be wrong somehow. it works but it looks insane. why? because what happens is that for each loaded entity, the merge will call a Select from respective database, ending up a huge lots of queries.
Meaning, what happes is :

- selecting concerned entities = 50 SELECTs
- copying to Archive = (number of entities) x INSERTs
- merging each entity = (number of entities) x SELECT Entity FROM TableX WHERE PK = Entity.pk'
- deleting = (number of entities) x DELETE

So, is there anyway to avoid the "merging" phase without coming down to use native queries to delete data using the entities still up to date, even if detached...?