3 Replies Latest reply on Oct 27, 2003 11:39 AM by Marius Kotsbak

    SELECT COUNT(*) before every INSERT?  why?

    Ramin Akhbari Newbie

      I've just successfully deployed an entity bean and ran some tests against it. It's working, but when I look at the server.log, I'm seeing what appears to be un-necessary calls right before a create:

      2003-08-08 11:16:55,573 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.Realm] Create: pk=2199139893
      2003-08-08 11:16:55,573 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.Realm] Executing SQL: SELECT COUNT(*) FROM Realm WHERE guid=?
      2003-08-08 11:16:55,583 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.Realm] [Realm.ejbCreate] Executing SQL: INSERT INTO Realm (guid, version, parent_guid, name, title, locale_language, locale_country, locale_variant, create_date, deleted) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      2003-08-08 11:16:55,583 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.Realm] Rows affected = 1

      JBoss appears to be doing a SELECT COUNT(*) before every INSERT. This is quite a bit of overhead for every insert call, isn't it? What's the purpose? Is it because my "guid" field is a PK and it's just trying to make sure that it doesn't really exist to begin with?

      I verified the JBoss CMP source code and sure enough, the call to insert the entity has this in it:

      if(entityExists(pk)) {
      throw new DuplicateKeyException("Entity with primary key " + pk + " already exists");
      }
      insertEntity(ctx);

      The call to entityExists(pk) has the SELECT COUNT (*) in it.

      Can someone enlighten me as to why JBoss is doing it like this?

      Thanks.

      RA