12 Replies Latest reply on Jun 25, 2003 1:43 PM by Nigel Birch

    Useful tip to improve CMP performance

    deepak3000 Newbie

      Hi,

      Our system is a heavy insert/update oriented system. We were getting low peformance and noted that most of the time was being spent in select count(*) from ...!!!!!! When I looked into the code, JDBCBeanExistsCommand was the one who was doing it all. A call to this was being made for every insert too!!! This was being done by JDBCCreateEntityCommand.

      To improve performance we should
      1. The JDBCBeanExistsCommand should do
      select id from table instead of select count(*).
      2. JDBCCreateEntityCommand should stop calling JDBCBeanExistsCommand. Instead it should just attempt an insert and if a failure occurs, throw the exception. I understand there might be a problem in throwing the specific DuplicateKeyException but who cares.
      Below is a sample of the times taken
      SELECT COUNT(*) FROM ENRICHMENTINFO WHERE ID=:1 => TIME TAKEN => 1172343750
      INSERT INTO ENRICHMENTINFO (ID, elsrID, fieldName, sourceID, priority) VALUES (:1, :2, :3, :4, :5) => TIME TAKEN => 65000000

      I will be making these changes for myself. I shall give these changes if required.

      regards
      Naveen