Recently while creating several tens of thousands of entities I noticed a general increase in the time taken to add an entity. The time is roughly proportional to the number of rows in the table.
The offending code, less the comments and logging, looks as follows : AttributeLocal attribute = attributeHome.findByNameAndType(attributeData.getName(), "ld"); AttributeInstanceLocal attributeInstance = attributeInstanceHome.create(attributeInstanceData); attributeInstance.setAttribute(attribute); parcel.getAttributeInstances().add(attributeInstance); The relationships involved are Attribute (1) <- (M) AttributeInstance [ via foreign key on the attributeinstance table called attribute_id] Parcel (1) -> (M) AttributeInstance [ via a link table ] Now the jboss server log included below shows the sql query "SELECT AttributeInstance_id FROM ATTRIBUTEINSTANCE WHERE (attribute_id=?)". In this case it takes 210 ms but this time is proportional to the number of attributeinstances already related to this attribute. This query does in fact typically return thousands of rows. I can see no good reason for this select to be executed at all - or have I missed something ? 2002-09-27 16:32:02,360 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Sequence.findByPrimaryKey] Executing SQL: SELECT name FROM SEQUENCE WHERE name=? 2002-09-27 16:32:02,370 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Sequence] Executing SQL: SELECT nextVal FROM SEQUENCE WHERE (name=?) 2002-09-27 16:32:02,370 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Sequence] Executing SQL: SELECT nextVal FROM SEQUENCE WHERE (name=?) 2002-09-27 16:32:02,380 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreEntityCommand.Sequence] Executing SQL: UPDATE SEQUENCE SET nextVal=? WHERE name=? 2002-09-27 16:32:02,380 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreEntityCommand.Sequence] Rows affected = 1 2002-09-27 16:32:02,380 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.AttributeInstance] Create: pk=80936 2002-09-27 16:32:02,380 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.AttributeInstance] Executing SQL: SELECT COUNT(*) FROM ATTRIBUTEINSTANCE WHERE AttributeInstance_id=? 2002-09-27 16:32:02,380 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.AttributeInstance] Executing SQL: INSERT INTO ATTRIBUTEINSTANCE (AttributeInstance_id, value, deprecated, userstamp, timestamp, attribute_id) VALUES (?, ?, ?, ?, ?, ?) 2002-09-27 16:32:02,390 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.AttributeInstance] Rows affected = 1 2002-09-27 16:32:02,390 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Attribute] Executing SQL: SELECT type, unit, data_type, description, name, deprecated, userstamp, timestamp FROM ATTRIBUTE WHERE (attribute_id=?) 2002-09-27 16:32:02,390 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.Attribute] Executing SQL: SELECT AttributeInstance_id FROM ATTRIBUTEINSTANCE WHERE (attribute_id=?) 2002-09-27 16:32:02,600 INFO [STDOUT] LOAD--2 attribute instance add 240 milliseconds 2002-09-27 16:32:02,600 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreEntityCommand.AttributeInstance] Executing SQL: UPDATE ATTRIBUTEINSTANCE SET attribute_id=? WHERE AttributeInstance_id=? 2002-09-27 16:32:02,600 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreEntityCommand.AttributeInstance] Rows affected = 1 I'm happy to dig deeper into this problem but am hoping that someone can give me a few pointers as to what might be going on here. -- Bobby Abraham Orlando, FL [EMAIL PROTECTED] ------------------------------------------------------- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
