2 Replies Latest reply on Nov 19, 2002 2:05 AM by Jeremy Boynes

    select count(*) on each CMP entity bean creation

    Arno Huetter Newbie

      Hello everybody,

      I enabled SQL tracing, and discovered something confusing: each time I create a CMP entity bean, just before the SQL insert, JBoss executes the following select statement:

      SELECT COUNT(*) FROM ... WHERE id=?

      is the primary key field. I create my own primary keys in memory using a GUID-like algorithm, without having to access the database. It looks like JBoss is trying to figure out whether the primary key exists or not.

      This might be the reason for some performance issues I experienced on entity bean creation. Does anybody know the reason for this statement, and how I might be able to prevent it?

      Thank you!

      Kind regards,
      Arno Huetter

        • 1. Re: select count(*) on each CMP entity bean creation
          Horia Muntean Newbie

          Probably this is the way the JBOSS CMP engine is detecting PK existence. And yes, I agree, this could be a performance issue with CMP. Did u tested this behaviour with other app servers? Anyway, you can post your comment on the mailing list 'cause you might some interesting answers there. And by the way I don't know how u can prevent it.

          • 2. Re: select count(*) on each CMP entity bean creation
            Jeremy Boynes Newbie

            seven is right, it does this check to return a DuplicateKeyException from create(). Assuming you have an index on the PK the performance impact should be small. If you don't have an index on the pk your performance will be really bad anyway (as every load will be a table scan).

            There is a patch out there to skip this select which should give a marginal improvement at the cost of less friendly failure modes. The main cost of create though is doing the insert statement.