While debugging an outage at wikis.sun.com I noticed that the code in HibernateUserManager#addMembership generates some extremely inefficient queries that were giving our db and network hard time:
membership = dGroup.getLocalMembers();
if (membership == null)
membership = new HashSet();
The last line of the code translates to:
DEBUG 2008-07-02 11:35:43,801 [service-j2ee-3] BatcherImpl:log - select localmembe0_.groupid as groupid__, localmembe0_.userid as userid__, defaulthib1_.id as id0_, defaulthib1_.name as name0_, defaulthib1_.password as password0_, defaulthib1_.email as email0_, defaulthib1_.created as created0_, defaulthib1_.fullname as fullname0_ from local_members localmembe0_ inner join users defaulthib1_ on localmembe0_.userid=defaulthib1_.id where localmembe0_.groupid=?
DEBUG 2008-07-02 11:35:43,806 [service-j2ee-3] BatcherImpl:log - insert into local_members (groupid, userid) values (?, ?)
Which means retrieve*all* members of a given group and then insert the user to the db.
If you run this query on our db with 25k users in a group, you run into some really big problems. By that I mean that the query can easily run several minutes and affect the overall db performance.
The code should be rewritten so that the uniqueness constraint is checked by a SELECT and if no dupe is found the INSERT can follow, otherwise this code will never scale.