Improving Hibernete performance in many-to-many relationship

Question

I have two tables connected with many-to-many relationship. Database is set on another server and I see really big performance problem when I'm trying to get informations about one of the records if these informations include total count of the second table.

First bean:

package dbaccess.beans.newsletter;

import java.sql.Timestamp;
import java.util.HashSet;
import java.util.Set;

import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.SequenceGenerator;
import javax.persistence.Column;
import javax.persistence.Table;

import dbaccess.beans.RegisteredUser;

@Entity
@Table(name="NEWSLETTER_LIST")
public class NewsletterList {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "G1")
    @SequenceGenerator(name = "G1", sequenceName = "NEWSLETTER_LIST_SEQ", allocationSize = 1, initialValue= 1)
    @Column(name = "LIST_ID", unique = true, nullable = false)
    private Long listID;

    @Column(name = "LIST_NAME", nullable = false, length = 50)
    private String listName;

    @ManyToMany(fetch = FetchType.LAZY, cascade = {})
    @JoinTable(name = "NEWSLETTERLISTS_USERS", joinColumns = { 
            @JoinColumn(name = "LIST_ID", nullable = false) }, 
            inverseJoinColumns = { @JoinColumn(name = "USER_ID", nullable = false) })
    private Set<RegisteredUser> users = new HashSet<RegisteredUser>(0);

    public Long getListID() {
        return listID;
    }

    public void setListID(Long listID) {
        this.listID = listID;
    }

    public Set<RegisteredUser> getUsers() {
        return users;
    }

    public void setUsers(Set<RegisteredUser> users) {
        this.users = users;
    }

}

Second bean:

package dbaccess.beans;

import java.sql.Timestamp;
import java.util.HashSet;
import java.util.Set;

import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
import javax.persistence.SequenceGenerator;
import javax.persistence.Column;
import javax.persistence.Table;

import dbaccess.beans.newsletter.NewsletterList;

@Entity
@Table(name="USER")
public class RegisteredUser {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "G1")
    @SequenceGenerator(name = "G1", sequenceName = "USER_SEQ", allocationSize = 1, initialValue= 1)
    @Column(name = "USER_ID", unique = true, nullable = false)
    private Long usrID;

    @Column(name = "GIVENNAME", length = 20)
    private String usrGivenName;

    @Column(name = "FAMILYNAME", length = 20)
    private String usrFamilyName;

    @ManyToMany(fetch = FetchType.EAGER, mappedBy = "users", cascade = {})
    public Set<NewsletterList> newsletterList = new HashSet<NewsletterList>();

    public Long getUsrID() {
        return usrID;
    }

    public void setUsrID(Long usrID) {
        this.usrID = usrID;
    }

    public String getUsrGivenName() {
        return usrGivenName;
    }

    public void setUsrGivenName(String usrGivenName) {
        this.usrGivenName = usrGivenName;
    }

    public String getUsrFamilyName() {
        return usrFamilyName;
    }

    public void setUsrFamilyName(String usrFamilyName) {
        this.usrFamilyName = usrFamilyName;
    }

    public Set<NewsletterList> getNewsletterList() {
        return newsletterList;
    }

    public void setNewsletterList(Set<NewsletterList> newsletterList) {
        this.newsletterList = newsletterList;
    }

    @Override
    public String toString() {
        return "RegisteredUser[usrID=" + usrID + ", usrGivenName=" + usrGivenName + ", usrFamilyName=" + usrFamilyName + "]";
    }

}

And the problem is when I try to execute this piece of code:

session = dbService.getSessionFactory().openSession();
Criteria c = session.createCriteria(NewsletterList.class);
c.add(Restrictions.eq("listID", listID));
List<NewsletterList> newsletterList = (List<NewsletterList>) c.list();

//below is most expensive
newsletterList.get(0).getUsers().size()

Is there any way to improve this performance? Thanks in advance.

PS When I have approx. 70 users in one list, request to above code takes approx 5-6 seconds!

Answer 1

newsletterList.get(0).getUsers().size() makes Hibernate load all the users registered to the newsletter, only to get the number of registered users.

Use an ad hoc HQL query to count the number of registered users:

select count(user.usrID) from RegisteredUser user
inner join user.newsletterList newsLetter
where newsLetter.listID = :listId

Note that 5-6 seconds to execute the above code is way too much, though. You probably need to check if there is an index placed on the join columns of the join table.

Also note that you could simply use session.get(NewsLetter.class, listId) to get the list by ID.

And finally, everything would be easier and more readable if your IDs were all named id.

Answer 2

You might want to try using the Hibernate Criteria API to query for the number of users on a specific newsletter. It would look roughly like this:

Criteria crit = session.createCriteria(RegisteredUser.class);
crit.setProjection(Projections.rowCount());
crit.add(Restrictions.eq("listID", listID));
return (Long) crit.uniqueResult();