Hibernate: bad performance when removing element from many-to-many relation.

Question

I have to Classes (UserSet and User) which have a many-to-many relation (i.e. every user can belong to some UserSets). In the database there is a UserSet Table, a User Table and an 'in between' Table (UsersToUserSets). Now, if I want to remove a user from a UserSet by doing

 userSet.getUsers().remove(user);
 session.flush()

Hibernate first fetches all users belonging to userSet and then removes the one user and updates the 'inbetween' table.

As there may be thousands of users belonging to a UserSet this is very bad for the performance. Is there a way to avoid that all of the users are fetched?

The interesting parts of the mapping files look like this:

<class name="...UserSet"> 
...
 <set name="users" table="T_UM_USERS2USER_SETS">
  <key column="FK_USER_SET_ID" />
  <many-to-many column="FK_USER_ID"
   class="...User" />
 </set>
...
</class>

<class name="...User"> 
...
 <set name="userSets" table="T_UM_USERS2USER_SETS" inverse="true">
  <key column="FK_USER_ID" />
  <many-to-many column="FK_USER_SET_ID" class="...UserSet"  />
 </set>
</class>

Answer 1

All users for a particular UserSet are fetched because you're calling userSet.getUsers().remove(user). Performing any operation on a lazy collection causes collection to be fetched. What you can do is:

1) If userSets cardinality is lower than that of users (e.g. given user would only belong to few userSets) you can switch the inverse end of this relationship and invoke user.getUserSets().remove(userSet) - I'm assuming here you want to remove the association only and not the actual entity.

OR

2) You can define a named SQL query to delete the association row from T_UM_USERS2USER_SETS table and execute it.