• 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 all forums
this forum made possible by our volunteer staff, including ...
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
  • Devaka Cooray
  • Jeanne Boyarsky
  • Knute Snortum
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Ganesh Patekar
  • Stephan van Hulst
  • Pete Letkeman
  • Carey Brown
  • Tim Holloway
  • Ron McLeod
  • Vijitha Kumara

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

Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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

    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
    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"
    Don't get me started about those stupid light bulbs.
    • Post Reply Bookmark Topic Watch Topic
    • New Topic
    Boost this thread!