select count(*) on each CMP entity bean creation

[arno.huetter]

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

[seven]

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.

[jboynes]

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.