11 Replies Latest reply on Sep 23, 2009 4:35 AM by rhills

    Quick, efficient pattern in SEAM for bulk updates and inserts?

    rhills

      Hi All,


      Does anybody have an efficient Seam pattern to do bulk updates/inserts of a number of entities?


      My process updates around 2000 entities and needs to look at each one (ie the logic can't be done via a single SQL update).  I need to be able to execute the set of updates as a single transaction, ie if anything fails during the process, all updates are rolled back.


      Everything I've tried so far is either:



      • quick, but won't roll back; or

      • agonisingly slow.



      I know how to do this via Hibernate, but everything I read about Seam seems to argue against using the Hibernate session directly.


      TIA,

        • 1. Re: Quick, efficient pattern in SEAM for bulk updates and inserts?
          christian.bauer

          Rob Hills wrote on Aug 01, 2008 04:40:


          but everything I read about Seam seems to argue against using the Hibernate session directly.



          Does not.

          • 2. Re: Quick, efficient pattern in SEAM for bulk updates and inserts?

            You can always get access to a Hibernate Session object from an EntityManager instance through the standard EntityManager.getDelegate() method. This is a JPA specification feature.


              org.hibernate.Session session = (Session)entityManager.getDelegate();
            

            • 3. Re: Quick, efficient pattern in SEAM for bulk updates and inserts?
              rhills

              Hi Francisco,


              Thanks for the reply.


              In fact, that's exactly what we did for this process, here's some pseudo(ish) code illustrating how we did it (in case it helps anyone else in future):


                Query query = em.createQuery("update Course c set c.lastUpdated = :pLastUpdated where c.title = :pTitleSought");
                Transaction trans = ((Session)em.getDelegate()).getTransaction;
                try {
                  trans.begin();
                  for ( ... iterate over some stuff ...) {
                    query.setParam("pTitleSought", titleSought);
                    query.setParam("lastUpdated", new Date());
                    query.executeUpdate();
                  }
                  trans.commit();
                } catch (Throwable t) {
                  // do stuff with t
                  trans.rollback();
                }
              



              This was quite familiar and easy enough to do, but it doesn't feel very Seamish.  I wasted much time trying to find a Seamish way of doing this that didn't take huge amounts of time.  For the record, I tried a number of different methods of doing the updates by getting the entity, modifying it and merging it back.  The quickest of these took 23 minutes for the same updates to the same 2500 records, compared with a little over a minute using the above technique!


              In an ideal world, I'd like to see Seam apply Conversation settings to the query.executeUpdate() method so, for example, if you set the current conversation's flushMode to MANUAL, repeated calls to executeUpdate() wouldn't commit until you executed flush() on your entity manager.


              What do others think?

              • 4. Re: Quick, efficient pattern in SEAM for bulk updates and inserts?
                rhills

                Hi Christian,



                Christian Bauer wrote on Aug 01, 2008 11:12:


                Does not.


                Thanks for the reply.  I guess you're saying that not everything that's written about Seam says or implies that it's not OK to use the Hibernate session, so that's good to know.


                As mentioned in my other post on this thread, we're now using the session to get a transaction so we can group up calls to our query.executeUpdate() and commit or rollback as a batch.


                However, to me it seems unintuitive that Seam doesn't control this behind the scenes when you set FlushModeType to MANUAL.


                I spent quite a bit of time hunting through Seam and Hibernate source trying to find exactly which code was being executed when I called Query.executeUpdate() so I could see exactly what was being done transactionally.  Unfortunately, I ran out of time before I could track this down.


                If others agree it would be good for Seam to include .executeUpdate() in the transaction control applied by its Conversation FlushModeType, I'll research it a bit more and put up a JIRA.

                • 5. Re: Quick, efficient pattern in SEAM for bulk updates and inserts?

                  I think your solution is good for a small update of a few thousand rows, especially if the performance meets you needs.


                  I think I would have gone a different direction, creating a message driven bean and using JDBC with addBatch()/executeBatch().


                  Seam is a great tool, but it isn't suited for every problem you are going to run into.  Sometimes the more direct approach can give you better performance.  The good news is that integrating Seam with the rest of the Java world is super easy.

                  • 6. Re: Quick, efficient pattern in SEAM for bulk updates and inserts?
                    rhills

                    Hi David,



                    David Holbrook wrote on Aug 03, 2008 01:45:


                    I think your solution is good for a small update of a few thousand rows, especially if the performance meets you needs.

                    I think I would have gone a different direction, creating a message driven bean and using JDBC with addBatch()/executeBatch().


                    OK, I understand the logic for using the JDBC batch processes. However, I've not used message driven beans before, what's the benefit of using one of those in this situation?



                    Seam is a great tool, but it isn't suited for every problem you are going to run into.  Sometimes the more direct approach can give you better performance.  The good news is that integrating Seam with the rest of the Java world is super easy.


                    I agree that you can't expect a framework to do everything, but nevertheless I think it would enhance Seam if it included methods to support efficient batch-type processes.

                    • 7. Re: Quick, efficient pattern in SEAM for bulk updates and inserts?

                      OK, I understand the logic for using the JDBC batch processes. However, I've not used message driven beans before, what's the benefit of using one of those in this situation?

                      MDB's let you execute code asynchronously.  I was thinking that if the batch update was long-running it would be preferable to run it in the background.  Users start to thrash around and do all sorts of unexpended stuff when the UI isn't responsive.



                      I agree that you can't expect a framework to do everything, but nevertheless I think it would enhance Seam if it included methods to support efficient batch-type processes.

                      I agree there is a place for some batch support somewhere, but I think I would prefer it in JPA or as an extention to JPA rather than in Seam.

                      • 8. Re: Quick, efficient pattern in SEAM for bulk updates and inserts?
                        rhills

                        Hi David,



                        David Holbrook wrote on Aug 03, 2008 04:43:


                        MDB's let you execute code asynchronously.  I was thinking that if the batch update was long-running it would be preferable to run it in the background.  Users start to thrash around and do all sorts of unexpended stuff when the UI isn't responsive.


                        Thanks for that explanation.  In our case, they'll just have to thrash I think.  The particular process I'm working on is an administrative process anyway, so it won't be the great unwashed that will be thrashing.



                        I agree there is a place for some batch support somewhere, but I think I would prefer it in JPA or as an extention to JPA rather than in Seam.


                        Yes, a potential issue I thought of after my previous reply is that if we use JDBC batch processing, we lose our DB independence.  That said, the SQL for this process is pretty generic and our app is unlikely to change DB, but we elected to go with Hibernate from the start so I'm not sure we'll make an exception for this.


                        However, it occurs to me this process may eventually have to handle 20K records or more. so we may have to go there yet.

                        • 9. Re: Quick, efficient pattern in SEAM for bulk updates and inserts?
                          pmuir

                          Seam simply asks Hibernate to set manual flush mode, so ask on the Hibernate forum about how/why it is applied to Hibernate session methods.


                          You can also get at the transaction using Transaction.instance().

                          • 10. Re: Quick, efficient pattern in SEAM for bulk updates and inserts?
                            gimpy21

                            Did you manage to come across a viable solution Rob?

                            • 11. Re: Quick, efficient pattern in SEAM for bulk updates and inserts?
                              rhills

                              As I mentioned in this post above, we used the existing transaction to group updates.


                              I've not work on this project since late last year, so I'm not sure if any other solution has been found in the meantime.


                              HTH,