Why do I get N+1 select queries when lazy loading

Question

I expect to get only one sql query, but I run into the N+1 select trap. I don't really understand why. Here is is the problem in detail:

I have an entity "PlayerRef":

@Entity
@Table(name = "player_ref")
public class PlayerRef {

  //constructor etc...

  @OptimisticLock(excluded = true)
  @LazyCollection(LazyCollectionOption.TRUE)
  @ManyToMany(fetch = FetchType.LAZY, mappedBy = "playerRefs")
  public Set<Player> getPlayers() {
    return players;
  }

}

And a class Player:

@Entity
@Table(name = "player")
public class Player {

  //constructor etc...

  @OptimisticLock(excluded = true)
  @LazyCollection(LazyCollectionOption.TRUE)
  @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name="cr_player_ref_player", 
      joinColumns = {
        @JoinColumn(name="player_id", unique = true)           
      }
      ,inverseJoinColumns = {
        @JoinColumn(name="player_ref_id")
      }     
    )
  private Set<PlayerRef> getPlayerRefs() {
    return this.playerRefs;
  }
}

Now, in my program I use the following HQL query to get all playerRef entities:

Query playerRefQ = session.createQuery("select playerRef from PlayerRef playerRef ")
  .setReadOnly(true); 
playerRefQ.setParameter("sport", sport);
@SuppressWarnings("unchecked")
List<PlayerRef> allPlayerRefs = playerRefQ.list();

This results in the N+1 Select statements:

1)

select
    playerref0_.id as id1_21_0_,
    ... 
from
    player_ref playerref0_ 

N times)

select
    players0_.player_ref_id as player_r2_21_0_,
    players0_.player_id as player_i1_34_0_,
    player1_.id as id1_19_1_,
    ...
from
    cr_player_ref_player players0_ 
inner join
    betdata.player player1_ 
        on players0_.player_id=player1_.id 
where
    players0_.player_ref_id=?

This is (again) very unexpected, since I thought the collection is lazy loaded and the set of players of each playerRef should be a hibernate-proxy.

Anyone knows how I can really only load the playerRef entities without also loading the associated players? For my use case I need all playerRefs but not the associated players.

In an earlier question of mine it was suggested, that somehow the toString() method of playerRef or Player could be overwritten in a way that references the associated entities. This is not the case. The N+1 queries happen right on accessing the list of all playerRef entities.

Notes:

1. This is a follow up question to Why does this result in non-lazy fetching and N+1 select statements?

2. A similar question also without solution is here: How to lazy load a many-to-many collection in hibernate?

Answer 1

You need to play with Batch size and the Fetch mode in order to solve that problem here is the reference link that will tell you how can you overcome the N+1 select issue

Hope this will help

http://www.mkyong.com/hibernate/hibernate-fetching-strategies-examples/