• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Issue when using 2 entity managers to manage one entity from one database to another

 
Alfred Couder
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.


    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) '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...?



     
    Rahul Babbar
    Ranch Hand
    Posts: 210
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    I bet you knew this was coming..but isn't it easier to just do it using Oracle utlities like Import and Export?
     
    Alfred Couder
    Greenhorn
    Posts: 6
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Rahul Babbar wrote:I bet you knew this was coming..but isn't it easier to just do it using Oracle utilities like Import and Export?


    Indeed, I cannot say that you've surprised me here.

    Thanks for answering Rahul.

    Import/Export can as well imply off line database while 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 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"
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic