Background The CARDINALTY command in ESQL is a very expensive command to run because it must parse the complete message in order to count the number of entries. Very often developers will try and use CARDINALITY to move a big structure from one format to another. It is easy to use and very logical, but there is a big performance price to pay.

On my test machine, the following code will take on average 164 seconds to process 100 000 loops of 242 records each:

BEGIN
      DECLARE RecPointer INTEGER 1;
      DECLARE accountDetRecCount INTEGER CARDINALITY(InputRoot.XMLNSC.ESB_INTERNAL.ESB_DATA.payload.AccountDetail.AccountDetailRecord[]);

      WHILE accountDetRecCount >= RecPointer DO
            SET OutputRoot.XML.AccountDetail.AccountDetailRecord[RecPointer] =
                InputRoot.XMLNSC.ESB_INTERNAL.ESB_DATA.payload.AccountDetail.AccountDetailRecord[RecPointer];
            SET RecPointer = RecPointer + 1 ;
      END WHILE;

      RETURN TRUE;
END;

Suggested solution Replace CARDINALITY use with REFERENCE variable use.  On my test machine the following code will take on average 33 seconds to process 100 000 loops of 242 records each:

BEGIN
      DECLARE pPointer REFERENCE TO InputRoot.XMLNSC.ESB_INTERNAL.ESB_DATA.payload.AccountDetail.AccountDetailRecord;

      WHILE LASTMOVE(pPointer) DO
            CREATE LASTCHILD of OutputRoot.XMLNSC.AccountDetail FROM pPointer;
            MOVE pPointer NEXTSIBLING;
      END WHILE;

      RETURN TRUE;
END;

Conclusion Use REFERENCE variables instead of CARDINALITY where you loop through the data/records.

Comments are closed.