Subselect when fetching parallel collections

rje · **Joined:** Fri Feb 20, 2009 1:38 pm **Posts:** 2

Hibernate version: Hibernate for EJB3, on JBoss, with SQL Server.

I'm facing a problem similar to that in the JPWH book (page 588-589): our "User" entity retrieves several collections in a many-to-many relationship, and as a result we're getting multiple left outer joins, i.e. a Cartesian product.

The book suggests using a subselect strategy, but I've not been able to make that work. For instance, where the original code is like this:

Code:

   @ManyToMany(fetch=FetchType.EAGER, cascade=CascadeType.MERGE)
   @JoinTable(name="fe_user_to_fe_internal_group", 
      joinColumns = @JoinColumn(name = "fe_user_id", referencedColumnName = "id"), 
      inverseJoinColumns = @JoinColumn(name = "fe_internal_group_id", referencedColumnName = "id"))
   public Set<InternalGroup> getInternalGroups() {
      return internalGroups;
   }

   @ManyToMany(fetch=FetchType.EAGER, cascade=CascadeType.MERGE)
   @JoinTable(name="fe_user_to_fe_webos_menu_item",
      joinColumns=@JoinColumn(name="fe_user_id"),
      inverseJoinColumns=@JoinColumn(name="fe_webos_menu_item_id"))
   public Set<WebosMenuItem> getMenuItems() {
      return this.menuItems;
   }

...I've tried to mitigate the problem by inserting a Hibernate annotation:

Code:

   @ManyToMany(fetch=FetchType.EAGER, cascade=CascadeType.MERGE)
   @org.hibernate.annotations.Fetch(value = org.hibernate.annotations.FetchMode.SUBSELECT)
   @JoinTable(name="fe_user_to_fe_internal_group", 
      joinColumns = @JoinColumn(name = "fe_user_id", referencedColumnName = "id"), 
      inverseJoinColumns = @JoinColumn(name = "fe_internal_group_id", referencedColumnName = "id"))
   public Set<InternalGroup> getInternalGroups() {
      return internalGroups;
   }

   @ManyToMany(fetch=FetchType.EAGER, cascade=CascadeType.MERGE)
   @org.hibernate.annotations.Fetch(value = org.hibernate.annotations.FetchMode.SUBSELECT)
   @JoinTable(name="fe_user_to_fe_webos_menu_item",
      joinColumns=@JoinColumn(name="fe_user_id"),
      inverseJoinColumns=@JoinColumn(name="fe_webos_menu_item_id"))
   public Set<WebosMenuItem> getMenuItems() {
      return this.menuItems;
   }

This doesn't seem to be helping, though. What am I doing wrong?

mmerder · **Posted:** Mon Feb 23, 2009 8:52 am

What is your problem? do you get an exception like "cannot fetch multiple bags"? Consider using @IndexColumn or @CollectionId

rje · **Joined:** Fri Feb 20, 2009 1:38 pm **Posts:** 2

I think we've solved the problem.

No exceptions are thrown -- it's a performance issue, with an ever-larger Cartesian product being selected from SQL.

It turns out to be caused by how our system components all work together. In order to fool SQL Server into actually creating subselects, we not only need to use the "subselect" annotation, but we also need to set our ManyToMany fetches to "LAZY". The SQL Server profiler seems to confirm that this in fact causes SQL Server to grudgingly revert to subselects instead of a passel of outer joins.