3 Replies Latest reply on Dec 6, 2003 7:41 PM by Surga Moin

    Iterate collection, doing bulk update -> strange SQL!!!

    grisimsop Newbie

      I perform a findAll on an entity and iterate over the returned collection (about 10.000 entries).

      All i wanna do is to make an bulk update on one attribute on every bean.
      When i take a look in the server.log it seems to me, that on every iteration of the
      collection jboss performs a "big" select with hundred of times "OR (PK_FIELD=?)" in the generated statement which looks like this (IMPOBID is the primary-key of the table):

      [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.CIZusatztexte] Executing SQL: SELECT IMPOBID,ZTOBID, ISBN, QUELLE, TYP, DATENTYP, INHALT, KURZBESCHREIBUNG, QUELLENNACHWEIS, KNONUMMER, DATUM, CREATED, MODIFIED, USERID FROM CIZUSATZTEXTE WHERE (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR
      (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR
      (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR (IMPOBID=?) OR
      [...] and so on...

      After this jboss is doing the update for the single row.
      My method in the session-bean looks like this:

      /**
      * @ejb.interface-method
      * view-type="remote"
      */
      public void bulkGenerateUUIDForZusatztexte()
      {
      try
      {
      CIZusatztexteLocalHome zusatztexteLocalHome =
      CIZusatztexteUtil.getLocalHome();
      Collection cRecords = zusatztexteLocalHome.findAll();
      CIZusatztexteServiceLocalHome serviceLocalHome =
      CIZusatztexteServiceUtil.getLocalHome();
      int cnt=0;
      for (Iterator iter = cRecords.iterator(); iter.hasNext();)
      {
      cnt++;
      CIZusatztexteLocal zusatztexteLocal =
      (CIZusatztexteLocal) iter.next();
      zusatztexteLocal.setZtObid(cnt);
      }
      }
      catch (NamingException e)
      {
      // TODO Auto-generated catch block
      e.printStackTrace();
      }
      catch (FinderException e)
      {
      // TODO Auto-generated catch block
      e.printStackTrace();
      }
      }

      What am i missing???
      Can anybody help me out what's going wrong???

        • 1. Re: Iterate collection, doing bulk update -> strange SQL!!!
          Jaime Salvador Newbie

          Your problem is that the iteration is not on a transaction.

          If you put all your calls to UPDATE methods (setXXX) into a one transaction (the finder adn the iteration), the SQL generated are very small and iteration over the collection is very fast.

          • 2. Re: Iterate collection, doing bulk update -> strange SQL!!!
            Stephen Coy Novice

            In my experience, this trivial operation is surprisinglly difficult to code and get right in practice. Jaime is correct in that you need some transaction management.

            However, you need to realise that if you attempt to perform this update in a single transaction your application is likely to lock up until it is finished (maybe a minute or two?). Every entity in that collection will get sucked into the transaction - so every other client that tries to access it will block on a transaction lock until it is complete. Is a minute too long to wait for a web page?

            I think that it is acknowledged that CMP entity beans are not good at bulk operations like this, mainly because the container overhead results in the operation taking four or more times longer that it should. You could consider using direct JDBC as an alternative for this operation.

            In the interim, I have used the following pattern:

            1. Define a method in, say, a session bean that has a trx attribute of "NotSupported";

            2. Define a special setter method in the entity bean with an implementation that just invokes the abstract setter and give it a trx attribute of "RequiresNew";

            3. Assign the finder method of the entity a trx attribute of "RequiresNew".

            4. from the session bean, invoke the finder which should return all entities in a single transaction.

            5. Iterate over the collection, invoking the special setter method on the entity, one trx at a time.

            This will take a little longer than performing it in a single trx, but your system will remain usable while it it executing.

            Steve Coy


            • 3. Re: Iterate collection, doing bulk update -> strange SQL!!!
              Surga Moin Newbie

              I need more info on this issue;
              can you explain it in detail or could you point to some chapters in the jboss documentation or j2ee specs which i need to look to understand this issue.
              TIA
              Moin.