-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: Hibernate Performance Problem
PostPosted: Thu Mar 25, 2010 11:47 am 
Beginner
Beginner

Joined: Tue Nov 03, 2009 9:38 am
Posts: 24
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.


Top
 Profile  
 
 Post subject: Re: Hibernate Performance Problem
PostPosted: Thu Mar 25, 2010 2:01 pm 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
What is your flush mode? If it is AUTO it can have a large impact on performance if your session contains a lot of entities. Use MANUAL or COMMIT flush mode and everything should be sent to the database at the end instead. Or make sure that you clear() the session at regular intervals.

The root cause is that for each flush event Hibernate will dirty-check all entities in the session and this can take some time and is usually not needed in this scenario.


Top
 Profile  
 
 Post subject: Re: Hibernate Performance Problem
PostPosted: Fri Mar 26, 2010 5:35 am 
Beginner
Beginner

Joined: Tue Nov 03, 2009 9:38 am
Posts: 24
Thanks for your reply.
This really helped a lot.

I changed the flush mode to MANUAL and issued a flush() and clear() in regular intervals.
We thus could speed up our import from 25 minutes to 8 minutes and get rid of the linear degradation.

I guess I should read the documentation more thoroughly.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.