N+1 selects on left join fetch...why?

deanhiller · **Joined:** Thu Sep 16, 2004 4:56 pm **Posts:** 80

My HQL is
select a from RootTpvtrans a
left join fetch a.tpv
left join fetch a.lastCall
where a = a.parent
order by a.tpvstartdt

I have eager fetch=LAZY on all the fields in RootTpvtrans.

The SQL that hibernate ran during the query is

Code:

Feb 17, 2010 12:34:11 PM net.sf.log4jdbc.Slf4jSpyLogDelegator sqlOccured
INFO: select roottpvtra0_.ID as ID42_0_, tpvtrans1_.tpvid as tpvid44_1_, tpvtrans2_.tpvid as tpvid44_2_, 
roottpvtra0_.LASTCALL as LASTCALL42_0_, roottpvtra0_.roottpvid as roottpvid42_0_, roottpvtra0_.tpvid 
as tpvid42_0_, roottpvtra0_.tpvstartdt as tpvstartdt42_0_, tpvtrans1_.ACCOUNTNO as ACCOUNTNO44_1_, 
tpvtrans1_.admincomments as admincom2_44_1_, tpvtrans1_.ani as ani44_1_, tpvtrans1_.auditedby 
as auditedby44_1_, tpvtrans1_.auditeddt as auditeddt44_1_, tpvtrans1_.auditedfl as auditedfl44_1_, 
tpvtrans1_.btn as btn44_1_, tpvtrans1_.businessname as business8_44_1_, tpvtrans1_.callbacklineno 
as callback9_44_1_, tpvtrans1_.CALLFLOWID as CALLFLOWID44_1_, tpvtrans1_.calltypecd as calltypecd44_1_, 
tpvtrans1_.checkedby as checkedby44_1_, tpvtrans1_.checkeddt as checkeddt44_1_, tpvtrans1_.checkedfl 
as checkedfl44_1_, tpvtrans1_.completeaddress as complet14_44_1_, tpvtrans1_.ctiid as ctiid44_1_, 
tpvtrans1_.decisionmakerfl as decisio16_44_1_, tpvtrans1_.dispositioncd as disposi17_44_1_, 
tpvtrans1_.dnis as dnis44_1_, tpvtrans1_.firstname as firstname44_1_, tpvtrans1_.fullaudiofl 
as fullaud20_44_1_, tpvtrans1_.fullname as fullname44_1_, tpvtrans1_.groupid as groupid44_1_, 
tpvtrans1_.LANGUAGECD as LANGUAGECD44_1_, tpvtrans1_.lastname as lastname44_1_, tpvtrans1_.lastupdateddt 
as lastupd24_44_1_, tpvtrans1_.lineno as lineno44_1_, tpvtrans1_.middleinitial as middlei26_44_1_, 
tpvtrans1_.OPERATORID as OPERATORID44_1_, tpvtrans1_.operatorstatuscd as operato27_44_1_, tpvtrans1_.pendcallbackstatus 
as pendcal28_44_1_, tpvtrans1_.primaryaddress1 as primary29_44_1_, tpvtrans1_.primaryaddress2 
as primary30_44_1_, tpvtrans1_.primarycity as primary31_44_1_, tpvtrans1_.primarystate as primary32_44_1_, 
tpvtrans1_.primaryzip as primaryzip44_1_, tpvtrans1_.REASONCDOBJID as REASONC63_44_1_, tpvtrans1_.repeat 
as repeat44_1_, tpvtrans1_.responseduration as respons35_44_1_, tpvtrans1_.reviewedby as reviewedby44_1_, 
tpvtrans1_.revieweddt as revieweddt44_1_, tpvtrans1_.reviewedfl as reviewedfl44_1_, tpvtrans1_.reviewercomments 
as reviewe39_44_1_, tpvtrans1_.salespersonid as salespe40_44_1_, tpvtrans1_.scriptVersion as 
scriptV64_44_1_, tpvtrans1_.secondaryaddress1 as seconda41_44_1_, tpvtrans1_.secondaryaddress2 
as seconda42_44_1_, tpvtrans1_.secondarycity as seconda43_44_1_, tpvtrans1_.secondaryfirstname 
as seconda44_44_1_, tpvtrans1_.secondaryfullname as seconda45_44_1_, tpvtrans1_.secondarylastname 
as seconda46_44_1_, tpvtrans1_.secondarystate as seconda47_44_1_, tpvtrans1_.secondaryzip as 
seconda48_44_1_, tpvtrans1_.SUBACCOUNTNO as SUBACCO65_44_1_, tpvtrans1_.testcallfl as testcallfl44_1_, 
tpvtrans1_.timezonecd as timezonecd44_1_, tpvtrans1_.title as title44_1_, tpvtrans1_.tpvduration 
as tpvdura52_44_1_, tpvtrans1_.tpvstartdt as tpvstartdt44_1_, tpvtrans1_.transcribedby as transcr54_44_1_, 
tpvtrans1_.transcribeddt as transcr55_44_1_, tpvtrans1_.transcribedfl as transcr56_44_1_, tpvtrans1_.transid 
as transid44_1_, tpvtrans1_.verifiedfl as verifiedfl44_1_, tpvtrans2_.ACCOUNTNO as ACCOUNTNO44_2_, 
tpvtrans2_.admincomments as admincom2_44_2_, tpvtrans2_.ani as ani44_2_, tpvtrans2_.auditedby 
as auditedby44_2_, tpvtrans2_.auditeddt as auditeddt44_2_, tpvtrans2_.auditedfl as auditedfl44_2_, 
tpvtrans2_.btn as btn44_2_, tpvtrans2_.businessname as business8_44_2_, tpvtrans2_.callbacklineno 
as callback9_44_2_, tpvtrans2_.CALLFLOWID as CALLFLOWID44_2_, tpvtrans2_.calltypecd as calltypecd44_2_, 
tpvtrans2_.checkedby as checkedby44_2_, tpvtrans2_.checkeddt as checkeddt44_2_, tpvtrans2_.checkedfl 
as checkedfl44_2_, tpvtrans2_.completeaddress as complet14_44_2_, tpvtrans2_.ctiid as ctiid44_2_, 
tpvtrans2_.decisionmakerfl as decisio16_44_2_, tpvtrans2_.dispositioncd as disposi17_44_2_, 
tpvtrans2_.dnis as dnis44_2_, tpvtrans2_.firstname as firstname44_2_, tpvtrans2_.fullaudiofl 
as fullaud20_44_2_, tpvtrans2_.fullname as fullname44_2_, tpvtrans2_.groupid as groupid44_2_, 
tpvtrans2_.LANGUAGECD as LANGUAGECD44_2_, tpvtrans2_.lastname as lastname44_2_, tpvtrans2_.lastupdateddt 
as lastupd24_44_2_, tpvtrans2_.lineno as lineno44_2_, tpvtrans2_.middleinitial as middlei26_44_2_, 
tpvtrans2_.OPERATORID as OPERATORID44_2_, tpvtrans2_.operatorstatuscd as operato27_44_2_, tpvtrans2_.pendcallbackstatus 
as pendcal28_44_2_, tpvtrans2_.primaryaddress1 as primary29_44_2_, tpvtrans2_.primaryaddress2 
as primary30_44_2_, tpvtrans2_.primarycity as primary31_44_2_, tpvtrans2_.primarystate as primary32_44_2_, 
tpvtrans2_.primaryzip as primaryzip44_2_, tpvtrans2_.REASONCDOBJID as REASONC63_44_2_, tpvtrans2_.repeat 
as repeat44_2_, tpvtrans2_.responseduration as respons35_44_2_, tpvtrans2_.reviewedby as reviewedby44_2_, 
tpvtrans2_.revieweddt as revieweddt44_2_, tpvtrans2_.reviewedfl as reviewedfl44_2_, tpvtrans2_.reviewercomments 
as reviewe39_44_2_, tpvtrans2_.salespersonid as salespe40_44_2_, tpvtrans2_.scriptVersion as 
scriptV64_44_2_, tpvtrans2_.secondaryaddress1 as seconda41_44_2_, tpvtrans2_.secondaryaddress2 
as seconda42_44_2_, tpvtrans2_.secondarycity as seconda43_44_2_, tpvtrans2_.secondaryfirstname 
as seconda44_44_2_, tpvtrans2_.secondaryfullname as seconda45_44_2_, tpvtrans2_.secondarylastname 
as seconda46_44_2_, tpvtrans2_.secondarystate as seconda47_44_2_, tpvtrans2_.secondaryzip as 
seconda48_44_2_, tpvtrans2_.SUBACCOUNTNO as SUBACCO65_44_2_, tpvtrans2_.testcallfl as testcallfl44_2_, 
tpvtrans2_.timezonecd as timezonecd44_2_, tpvtrans2_.title as title44_2_, tpvtrans2_.tpvduration 
as tpvdura52_44_2_, tpvtrans2_.tpvstartdt as tpvstartdt44_2_, tpvtrans2_.transcribedby as transcr54_44_2_, 
tpvtrans2_.transcribeddt as transcr55_44_2_, tpvtrans2_.transcribedfl as transcr56_44_2_, tpvtrans2_.transid 
as transid44_2_, tpvtrans2_.verifiedfl as verifiedfl44_2_ from VOICELOG.RootTpvtrans roottpvtra0_ 
left outer join VOICELOG.Tpvtrans tpvtrans1_ on roottpvtra0_.tpvid=tpvtrans1_.tpvid left outer 
join VOICELOG.Tpvtrans tpvtrans2_ on roottpvtra0_.LASTCALL=tpvtrans2_.tpvid where roottpvtra0_.ID=roottpvtra0_.roottpvid 
order by roottpvtra0_.tpvstartdt limit 5 
Feb 17, 2010 12:34:11 PM net.sf.log4jdbc.Slf4jSpyLogDelegator sqlOccured
INFO: select roottpvtra0_.ID as ID42_0_, roottpvtra0_.LASTCALL as LASTCALL42_0_, roottpvtra0_.roottpvid 
as roottpvid42_0_, roottpvtra0_.tpvid as tpvid42_0_, roottpvtra0_.tpvstartdt as tpvstartdt42_0_ 
from VOICELOG.RootTpvtrans roottpvtra0_ where roottpvtra0_.tpvid='1234567890123456' 
Feb 17, 2010 12:34:11 PM net.sf.log4jdbc.Slf4jSpyLogDelegator sqlOccured
INFO: select roottpvtra0_.ID as ID42_0_, roottpvtra0_.LASTCALL as LASTCALL42_0_, roottpvtra0_.roottpvid 
as roottpvid42_0_, roottpvtra0_.tpvid as tpvid42_0_, roottpvtra0_.tpvstartdt as tpvstartdt42_0_ 
from VOICELOG.RootTpvtrans roottpvtra0_ where roottpvtra0_.tpvid='123456789012345633333' 
Feb 17, 2010 12:34:11 PM net.sf.log4jdbc.Slf4jSpyLogDelegator sqlOccured
INFO: select roottpvtra0_.ID as ID42_0_, roottpvtra0_.LASTCALL as LASTCALL42_0_, roottpvtra0_.roottpvid 
as roottpvid42_0_, roottpvtra0_.tpvid as tpvid42_0_, roottpvtra0_.tpvstartdt as tpvstartdt42_0_ 
from VOICELOG.RootTpvtrans roottpvtra0_ where roottpvtra0_.tpvid='99999999999999' 
Feb 17, 2010 12:34:11 PM net.sf.log4jdbc.Slf4jSpyLogDelegator sqlOccured
INFO: select roottpvtra0_.ID as ID42_0_, roottpvtra0_.LASTCALL as LASTCALL42_0_, roottpvtra0_.roottpvid 
as roottpvid42_0_, roottpvtra0_.tpvid as tpvid42_0_, roottpvtra0_.tpvstartdt as tpvstartdt42_0_ 
from VOICELOG.RootTpvtrans roottpvtra0_ where roottpvtra0_.tpvid='5432154321543210' 
Feb 17, 2010 12:34:11 PM net.sf.log4jdbc.Slf4jSpyLogDelegator sqlOccured
INFO: select roottpvtra0_.ID as ID42_0_, roottpvtra0_.LASTCALL as LASTCALL42_0_, roottpvtra0_.roottpvid 
as roottpvid42_0_, roottpvtra0_.tpvid as tpvid42_0_, roottpvtra0_.tpvstartdt as tpvstartdt42_0_ 
from VOICELOG.RootTpvtrans roottpvtra0_ where roottpvtra0_.tpvid='543215432154321033333' 
Feb 17, 2010 12:34:11 PM net.sf.log4jdbc.Slf4jSpyLogDelegator sqlOccured
INFO: select roottpvtra0_.ID as ID42_0_, roottpvtra0_.LASTCALL as LASTCALL42_0_, roottpvtra0_.roottpvid 
as roottpvid42_0_, roottpvtra0_.tpvid as tpvid42_0_, roottpvtra0_.tpvstartdt as tpvstartdt42_0_ 
from VOICELOG.RootTpvtrans roottpvtra0_ where roottpvtra0_.tpvid='5678956789567895' 
Feb 17, 2010 12:34:11 PM net.sf.log4jdbc.Slf4jSpyLogDelegator sqlOccured
INFO: select roottpvtra0_.ID as ID42_0_, roottpvtra0_.LASTCALL as LASTCALL42_0_, roottpvtra0_.roottpvid 
as roottpvid42_0_, roottpvtra0_.tpvid as tpvid42_0_, roottpvtra0_.tpvstartdt as tpvstartdt42_0_ 
from VOICELOG.RootTpvtrans roottpvtra0_ where roottpvtra0_.tpvid='567895678956789533333' 
Feb 17, 2010 12:34:11 PM net.sf.log4jdbc.Slf4jSpyLogDelegator sqlOccured
INFO: select roottpvtra0_.ID as ID42_0_, roottpvtra0_.LASTCALL as LASTCALL42_0_, roottpvtra0_.roottpvid 
as roottpvid42_0_, roottpvtra0_.tpvid as tpvid42_0_, roottpvtra0_.tpvstartdt as tpvstartdt42_0_ 
from VOICELOG.RootTpvtrans roottpvtra0_ where roottpvtra0_.tpvid='987659876598' 
Feb 17, 2010 12:34:11 PM net.sf.log4jdbc.Slf4jSpyLogDelegator sqlOccured
INFO: select roottpvtra0_.ID as ID42_0_, roottpvtra0_.LASTCALL as LASTCALL42_0_, roottpvtra0_.roottpvid 
as roottpvid42_0_, roottpvtra0_.tpvid as tpvid42_0_, roottpvtra0_.tpvstartdt as tpvstartdt42_0_ 
from VOICELOG.RootTpvtrans roottpvtra0_ where roottpvtra0_.tpvid='98765987659833333'

My RootTpvtrans.java is

Code:

@Entity
@Table(name = "RootTpvtrans")
public class RootTpvtrans implements Serializable {
   private static final long serialVersionUID = 1L;

   @Column(name = "ID")
   @Id
   @GeneratedValue(generator = "rootTpvSeq")
   @GenericGenerator(name = "rootTpvSeq", strategy = "org.hibernate.id.enhanced.SequenceStyleGenerator", parameters = {
         @Parameter(name = "sequence_name", value = "voicelog.rootTpvSeq"),
         @Parameter(name = "increment_size", value = "20"),
         @Parameter(name = "optimizer", value = "net.voicelog.oracleworkaround.FixedPoolOptimizer") })   
   private Long id;

   @Column(name="tpvstartdt")
   private Date tpvstartdt;

   @NotNull
   @ManyToOne(fetch=FetchType.LAZY)
   @JoinColumn(name = "roottpvid")
   private RootTpvtrans parent;

   @NotNull
   @OneToOne(fetch=FetchType.LAZY)
   @JoinColumn(name = "tpvid")
   private Tpvtrans tpv;

   @OneToMany(mappedBy="parent")
   @OrderBy("tpvstartdt")
   private List<RootTpvtrans> children = new ArrayList<RootTpvtrans>();
   
   //This will be the last call and could be itself as well...
   //NOTE: nullable because only the roots have a lastCall tied to them
   @OneToOne(fetch=FetchType.LAZY)
   @JoinColumn(name="LASTCALL")
   private Tpvtrans lastCall;

My TpvTrans is

Code:

@org.hibernate.annotations.Entity(dynamicUpdate=true)
@Entity
public class Tpvtrans implements Serializable, Cloneable {

   @OneToOne(mappedBy="tpv", fetch=FetchType.LAZY)
   private RootTpvtrans rootTpvtrans;

I really don't see why this needs N+1 selects...it is very odd.

deanhiller · **Joined:** Thu Sep 16, 2004 4:56 pm **Posts:** 80

hmmm, trimming down to one left join fails on either of those as well....I thought I had it working with at least one...I must have done someething to screw that up. I keep getting 1+N selects here.

deanhiller · **Joined:** Thu Sep 16, 2004 4:56 pm **Posts:** 80

well, I got to the bottom of it. In this case, hibernate appears to be a bit broken for some odd reason. I took the cat example, and it worked fine. I then made the relation onetoOne(closer to my code) and it worked fine. Then finally, when I flipped the JoinColumn to be in the Cat table instead of the Kitten table(since one to one it can be in either table), and you HAVE to HAVE the right dataset too!!!, then this stops working and it creates a 1+N query.

ie. dataset I used was 2 cats each with a kitten(and my join is nullable=false so a Cat has to have a kitten) and I added two kittens without Cats(I know this is all backwards but I was concentrating on keeping the HQL the same throughout the process. The only change I had to make was from

from Cat as cat left join cat.kittens
to
from Cat as cat left join cat.kitten

It finally broke though when cat had the joincolumn instead of kittens.....argggh...even though the 1st sql that runs gets all kittens and cats needed, hibernate still reruns the other queries.

Here is the entities ...(and the dataset I use via persisting using hibernate), and named query is in the annotation....

Code:

@Entity
@Table(schema = "VL_AGENT")
@NamedQuery(name = "findCats", query = "SELECT s FROM Cat s inner join fetch s.kitten")
public class Cat implements Serializable {

   private static final long serialVersionUID = 1L;

   @Column(name = "SCRIPTMODELID")
   @Id()
    @GeneratedValue(generator = "scriptModelSeq")
    @GenericGenerator( name = "scriptModelSeq",
            strategy = "org.hibernate.id.enhanced.SequenceStyleGenerator",
            parameters = {
            @Parameter(name = "sequence_name", value = "vl_agent.scriptModelSeq")
            , @Parameter(name = "increment_size", value = "20")
            , @Parameter(name = "optimizer", value = "net.voicelog.oracleworkaround.FixedPoolOptimizer")
    }
    )   
   private Long id;
   
   @Column(name="ScriptModelName", nullable = false, unique = true)
   private String name;

   @OneToOne(fetch=FetchType.LAZY)
   @JoinColumn(name="KITTENID")
   private Kitten kitten;

   public Long getId() {
      return id;
   }

   public void setId(Long id) {
      this.id = id;
   }

   public String getName() {
      return name;
   }

   public void setName(String name) {
      this.name = name;
   }

   public Kitten getKitten() {
      return kitten;
   }

   public void setKitten(Kitten kitten) {
      this.kitten = kitten;
   }
   
}

@Entity
@Table(schema = "VL_AGENT")
public class Kitten implements Serializable {

   private static final long serialVersionUID = 1L;

   @Column(name = "QUESTIONID")
   @Id()
    @GeneratedValue(generator = "questionSeq")
    @GenericGenerator( name = "questionSeq",
            strategy = "org.hibernate.id.enhanced.SequenceStyleGenerator",
            parameters = {
            @Parameter(name = "sequence_name", value = "vl_agent.questionSeq")
            , @Parameter(name = "increment_size", value = "20")
            , @Parameter(name = "optimizer", value = "net.voicelog.oracleworkaround.FixedPoolOptimizer")
    }
    )   
   private Long id;

   @Column(name = "QUESTIONNAME", nullable = false)
   @Length(min = 1, max = 64)
   private String name;

   @OneToOne(mappedBy="kitten", fetch = FetchType.LAZY)
   private Cat cat;

   public Long getId() {
      return id;
   }

   public void setId(Long id) {
      this.id = id;
   }

   public String getName() {
      return name;
   }

   public void setName(String name) {
      this.name = name;
   }

   public Cat getCat() {
      return cat;
   }

   public void setCat(Cat script) {
      this.cat = script;
   }

}


//initialization code to populate your database....
      Kitten kit1 = new Kitten();
      kit1.setName("declan");
      
      Kitten kit2 = new Kitten();
      kit2.setName("catalina");
      
      entityManager.persist(kit1);
      entityManager.persist(kit2);
      
      Cat cat = new Cat();
      cat.setName("dean");
      cat.setKitten(kit1);
      kit1.setCat(cat);      
      entityManager.persist(cat);
      
      Cat cat2 = new Cat();
      cat2.setName("Angela");
      cat2.setKitten(kit2);
      kit2.setCat(cat2);
      entityManager.persist(cat2);
      
      Kitten kit3 = new Kitten();
      kit3.setName("dummy");
      
      Kitten kit4 = new Kitten();
      kit4.setName("another");

      entityManager.persist(kit3);
      entityManager.persist(kit4);

and the 1+N queries are these....
Feb 18, 2010 8:47:43 AM net.sf.log4jdbc.Slf4jSpyLogDelegator sqlOccured
INFO: select cat0_.SCRIPTMODELID as SCRIPTMO1_66_0_, kitten1_.QUESTIONID as QUESTIONID44_1_, cat0_.KITTENID
as KITTENID66_0_, cat0_.ScriptModelName as ScriptMo2_66_0_, kitten1_.QUESTIONNAME as QUESTION2_44_1_
from VL_AGENT.Cat cat0_ inner join VL_AGENT.Kitten kitten1_ on cat0_.KITTENID=kitten1_.QUESTIONID

Feb 18, 2010 8:47:43 AM net.sf.log4jdbc.Slf4jSpyLogDelegator sqlOccured
INFO: select cat0_.SCRIPTMODELID as SCRIPTMO1_66_0_, cat0_.KITTENID as KITTENID66_0_, cat0_.ScriptModelName
as ScriptMo2_66_0_ from VL_AGENT.Cat cat0_ where cat0_.KITTENID=1
Feb 18, 2010 8:47:43 AM net.sf.log4jdbc.Slf4jSpyLogDelegator sqlOccured
INFO: select cat0_.SCRIPTMODELID as SCRIPTMO1_66_0_, cat0_.KITTENID as KITTENID66_0_, cat0_.ScriptModelName
as ScriptMo2_66_0_ from VL_AGENT.Cat cat0_ where cat0_.KITTENID=2

where ids 1 and 2 are of the kittens that have a cat. The other Kittens that do not have a cat are not looked up. As you can see, the first SQL already looks up kitten 1 and 2 data but for some reason hibernate thinks it needs to reget all that data with another N select statements which kills out program of 100 page results.

thanks,
Dean