Hi,
We have a performance problem with Hibernate 3.3.2 which we aren't able to figure out completely:
We have a user import which either creates or updates users.
The performance of this import seems to depend on the number of entries in the database.
However, we have at most 7000 Users in our database, which normally is near to nothing.
When I import new users the time needed per record linearly grows (from approx. 16ms for user 1 to approx. 400ms for user 6000).
When I update users the time needed depends on the number of users in the database.
True, updating 6000-7000 users in one transaction will cause some overhead, but even if I split the users into batches of 500 or 1000 the performance still degrades linearly.
The queries themselves seem to execute at constant speed, so the database itself doesn't seem to be the problem.
I'd appreciate any hint on where I could look for optimizations.
Thanks in advance,
Thomas
P.S.: here's some code in case you want to look for errors. I can post more if needed.
The queries executed for inserting one user:
Code:
//insert the user's address
insert into t_address (c_creationDate, c_creatorUid, c_deleted, c_deleterUid, c_deletionDate, c_modificationDate, c_modifierUid, c_version, c_address1, c_address2, c_city, c_comment, c_contactperson, c_country, c_email, c_fax, c_homepage, c_mobile, c_name1, c_name2, c_phone, c_pobox, c_poboxzipcode, c_semcountryuid, c_streetNumber, c_type, c_zipcode, c_uid) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
//insert the user itself
insert into t_user (c_creationDate, c_creatorUid, c_deleted, c_deleterUid, c_deletionDate, c_modificationDate, c_modifierUid, c_version, c_addressUid, c_areacode, c_bonusPoints, c_email, c_fieldstaffuid, c_firstname, c_gender, c_language, c_lastname, c_lockedByCustomer, c_lockedByProvider, c_nonewsletter, c_password, c_passwordInitial, c_role, c_title, c_username, c_verkaufshausuid, c_uid) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
//update the user's password
update t_user set c_creationDate=?, c_creatorUid=?, c_deleted=?, c_deleterUid=?, c_deletionDate=?, c_modificationDate=?, c_modifierUid=?, c_version=?, c_addressUid=?, c_areacode=?, c_bonusPoints=?, c_email=?, c_fieldstaffuid=?, c_firstname=?, c_gender=?, c_language=?, c_lastname=?, c_lockedByCustomer=?, c_lockedByProvider=?, c_nonewsletter=?, c_password=?, c_passwordInitial=?, c_role=?, c_title=?, c_username=?, c_verkaufshausuid=? where c_uid=? and c_version=?
//update the version due to update of association?
update t_user set c_creationDate=?, c_creatorUid=?, c_deleted=?, c_deleterUid=?, c_deletionDate=?, c_modificationDate=?, c_modifierUid=?, c_version=?, c_addressUid=?, c_areacode=?, c_bonusPoints=?, c_email=?, c_fieldstaffuid=?, c_firstname=?, c_gender=?, c_language=?, c_lastname=?, c_lockedByCustomer=?, c_lockedByProvider=?, c_nonewsletter=?, c_password=?, c_passwordInitial=?, c_role=?, c_title=?, c_username=?, c_verkaufshausuid=? where c_uid=? and c_version=?
//associate a profile with the new user
insert into t_userprofile (c_userUid, c_profileUid) values (?, ?)
//select the sum of bonus point changes the user currently has (this is needed for the update and might be skipped for inserts)
2010-03-24 11:41:19,457 DEBUG [org.hibernate.SQL (WuM)] select SUM(accountent0_.c_value) as col_0_0_ from t_account accountent0_ where accountent0_.c_useruid=? limit ?
//add a history entry for the address
insert into t_documentaddress (c_creationDate, c_creatorUid, c_deleted, c_deleterUid, c_deletionDate, c_modificationDate, c_modifierUid, c_version, c_address1, c_address2, c_city, c_comment, c_contactperson, c_country, c_email, c_fax, c_homepage, c_mobile, c_name1, c_name2, c_phone, c_pobox, c_poboxzipcode, c_semcountryuid, c_streetNumber, c_type, c_zipcode, c_uid) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
//add a history entry for the user
insert into h_user (c_creationDate, c_creatorUid, c_deleted, c_deleterUid, c_deletionDate, c_modificationDate, c_modifierUid, c_version, c_addressUid, c_areacode, c_bonusPoints, c_bonusPointsDiff, c_email, c_firstname, c_gender, c_language, c_lastname, c_lockedByCustomer, c_lockedByProvider, c_password, c_passwordInitial, c_refuid, c_role, c_title, c_username, c_uid) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
//update the bonus points for the user?
update t_user set c_creationDate=?, c_creatorUid=?, c_deleted=?, c_deleterUid=?, c_deletionDate=?, c_modificationDate=?, c_modifierUid=?, c_version=?, c_addressUid=?, c_areacode=?, c_bonusPoints=?, c_email=?, c_fieldstaffuid=?, c_firstname=?, c_gender=?, c_language=?, c_lastname=?, c_lockedByCustomer=?, c_lockedByProvider=?, c_nonewsletter=?, c_password=?, c_passwordInitial=?, c_role=?, c_title=?, c_username=?, c_verkaufshausuid=? where c_uid=? and c_version=?
//select the sum of bonus point changes (needed for the update, might be skipped for inserts)
select SUM(accountent0_.c_value) as col_0_0_ from t_account accountent0_ where accountent0_.c_useruid=? limit ?
The queries for updating a user are almost the same, except that the first inserts are updates in that case, the password change isn't necessary and before associating a new profile a select is issued to see if the change is necessary.
I also already looked for missing indices on tables, so those should not be the problem.
Here's the User entity:
Code:
@Entity
@Table(name=UserSQL.USER_TABLE)
//Achtung: bei Änderungen hier ggf. auch HUserEntity anpassen!
public class UserEntity extends BaseEntity implements IUserEntity, IHasContextEntity
{
private static final long serialVersionUID = 1L;
@Column ( name = UserSQL.USERNAME_COLUMN )
private String username;
@Column ( name = UserSQL.PASSWORD_COLUMN )
private String password;
@Column ( name = UserSQL.PASSWORD_INITIAL_COLUMN )
private boolean passwordInitial;
@Column ( name = UserSQL.FIRSTNAME_COLUMN )
private String firstName;
@Column ( name = UserSQL.LASTNAME_COLUMN )
private String lastName;
@Column ( name = UserSQL.GENDER_COLUMN, nullable = false, length = 1 )
private String gender;
@Column ( name = UserSQL.TITLE_COLUMN )
private String title;
@Column ( name = UserSQL.EMAIL_COLUMN )
private String email;
@Column ( name = UserSQL.LANGUAGE_COLUMN )
private String language;
@Column ( name = UserSQL.BONUSPOINTS_COLUMN )
private Long bonusPoints;
@Column ( name = UserSQL.ROLE_COLUMN )
private long role;
@Column ( name = UserSQL.LOCKED_BY_PROVIDER_COLUMN )
private Boolean lockedByProvider;
@Column ( name = UserSQL.LOCKED_BY_CUSTOMER_COLUMN )
private Boolean lockedByCustomer;
@Column ( name = UserSQL.AREACODE_COLUMN )
private String areaCode;
@Column ( name = UserSQL.NONEWSLETTER_COLUMN )
private Boolean noNewsletter;
@ManyToMany(targetEntity = ProfileEntity.class, fetch = FetchType.LAZY)
@JoinTable(name = UserSQL.USER_PROFILES_TABLE,
joinColumns = { @JoinColumn(name = UserSQL.USER_ID_COLUMN) },
inverseJoinColumns = {@JoinColumn(name = UserSQL.PROFILE_COLUMN) })
@LazyCollection(value = LazyCollectionOption.EXTRA)
private List<IProfileEntity> profiles;
@ManyToMany(targetEntity = RightEntity.class, fetch = FetchType.LAZY)
@JoinTable(name = UserSQL.USER_RIGHTS_TABLE,
joinColumns = { @JoinColumn(name = UserSQL.USER_ID_COLUMN) },
inverseJoinColumns = {@JoinColumn(name = UserSQL.RIGHT_COLUMN) })
private List<IRightEntity> rights;
@ManyToMany(targetEntity = UserGroupEntity.class, fetch = FetchType.LAZY)
@JoinTable(name = UserSQL.USER_USERGROUPS_TABLE,
joinColumns = { @JoinColumn(name = UserSQL.USER_ID_COLUMN) },
inverseJoinColumns = {@JoinColumn(name = UserSQL.USERGROUP_COLUMN) })
private List<IUserGroupEntity> userGroups;
@OneToOne(targetEntity = AddressEntity.class, fetch = FetchType.LAZY)
@JoinColumn(name = UserSQL.ADDRESS_COLUMN)
private IAddressEntity address;
@ManyToOne ( fetch = FetchType.LAZY )
@JoinColumn ( name = UserSQL.FIELDSTAFF_COLUMN )
private FieldStaffEntity fieldStaff;
@ManyToOne ( fetch = FetchType.LAZY )
@JoinColumn ( name = UserSQL.VERKAUFSHAUS_COLUMN )
private VerkaufshausEntity verkaufshaus;
//getters and setters are trivial and ommitted for brevity
}
And here's the relevant import code:
Code:
private void doImportUser( final UserTO pCaller, final List<ValFullUserTO> pUserList,
final ILanguageType pLanguageType, final Map<Long, FullUserTO> tNewUserMap,
final Map<Long, String> passwordMap, final Map<Long, FullUserTO> tExistingUserMap,
final Map<String, String> allValidationErrors )
{
validationService.fillMetaDataToDynamicValidatableFields( pUserList );
Map<String, Long> tUsernameUidMap = userDAO.getUsernameUidMap();
ResourceBundle tBundle = TranslationFactory.getTranslator( TranslatorNames.VALIDATION_TRANSLATOR ).getBundle(
pLanguageType.getLocale() );
IProfileEntity tProfileEntity = profileDAO.getProfileEntityByName( IAuthorizationInitService.USER_ROLE_PROFILE_NAME );
for( ValFullUserTO valUser : pUserList )
{
try
{
long tStart = System.currentTimeMillis();
Map<String, String> validationErrors = valUser.validate( tBundle );
if( validationErrors.isEmpty() )
{
FullUserTO fullUser = valUser.getOrigTO();
String newPassword = importUser( pCaller, fullUser, tUsernameUidMap, tProfileEntity );
if( newPassword != null )
{
tNewUserMap.put( fullUser.getUid(), fullUser );
passwordMap.put( fullUser.getUid(), newPassword );
}
else
{
tExistingUserMap.put( fullUser.getUid(), fullUser );
}
}
else
{
allValidationErrors.putAll( validationErrors );
}
long tDur = System.currentTimeMillis() - tStart;
LOG.info( "import of one user took " + tDur + " ms" );
}
catch( UserAlreadyExistsException e )
{
//mit nächstem User weitermachen
LOG.error( "User existiert bereits: " + valUser.toString(), e );
}
}
}
Code:
private String importUser( final UserTO pCaller, final FullUserTO pUserTO, final Map<String, Long> pUsernameUidMap, IProfileEntity pProfileEntity )
throws UserAlreadyExistsException
{
String tNewPassword = null;
Long tUserUid = pUserTO.getUid();
Long tExisitingUserUid = pUsernameUidMap.get( pUserTO.getUsername() );
Long tNewBonusPoints = pUserTO.getBonusPoints();
if( tUserUid == null )
{
if( tExisitingUserUid != null )
{
throw new UserAlreadyExistsException();
}
// create new user
LOG.debug( "creating user " + pUserTO.getUsername() + " in context " + pUserTO.getContext() );
long tCreatedUserUid = userDAO.createUser( pUserTO, false, pCaller );
pUserTO.setUid( tCreatedUserUid );
// create the first account entry for the user if necessary
accountDAO.createAccountEntryForGutschrift( pUserTO, tNewBonusPoints );
// generate a new password
tNewPassword = createNewPasswordImpl( pUserTO, false );
}
else
{
if( tExisitingUserUid != null && !tUserUid.equals( tExisitingUserUid ) )
{
throw new UserAlreadyExistsException();
}
// creates a new account entry for the user and updates the user's bonus points
accountDAO.createAccountEntryForGutschrift( pUserTO, tNewBonusPoints );
pUserTO.setBonusPoints( PriceUtil.roundHalfUp(
accountDAO.getBonusPointSumForUser( tUserUid ) ).longValue() );
// updates the user, fills the data from the transfer object into the entity
LOG.debug( "updating user " + pUserTO.getUsername() + " in context " + pUserTO.getContext() );
userDAO.updateUser( pUserTO, pCaller );
}
// create the archive/history entry, simply fills the HUser entity and persists it
hUserDAO.archivateUser( pUserTO, tNewBonusPoints, pCaller, true );
if( pProfileEntity != null )
{
IUserEntity tUserEntity = userDAO.getUserEntityByUid( pUserTO.getUid() );
//only update the profile if it is not already set
if(tUserEntity.getProfiles() == null || !tUserEntity.getProfiles().contains( pProfileEntity ))
{
tUserEntity.setProfiles( CollectionUtil.asArrayList( pProfileEntity ) );
}
}
return tNewPassword;
}
In case you ask: I timed the individual parts of importUser() but couldn't find a hot spot. Performance degraded almost linearly in all parts.
Login
FAQ
Search
