We recently upgraded from Hibernate 3.2.4 to 4.1.2, and from XML mappings to annotations. I noticed that the generated SQL for Criteria queries includes many more join tables than it used to. Most of the joins come from many-to-one mappings, which seem to be eager by default. The result is the same, but the extra joins are a problem because MySQL takes longer to run the queries. Has the default fetch strategy changed from Hibernate 3 to 4, or between XML mappings and annotations?
Also, the same query in HQL doesn't generate any joins for ManyToOne mappings at all.
Here's an example of a Criteria query in our code:
Code:
List<PrescribedOrder> prescribedOrders = HibernateUtil
.createCriteria(hsession, PrescribedOrder.class).createAlias("curriculum", "c")
.createAlias("c.keywords", "k").add(Restrictions.in("k.keyword", keywords))
.add(Restrictions.eq("visit", selectedVisit)).addOrder(Order.desc("rxDate")).list();
The generated SQL in Hibernate 3.2.4:
Code:
select [some stuff]
from CUR_PrescribedOrder this_
inner join CUR_CurriculumTemplate c1_ on this_._curriculumId=c1_.curriculumTemplateId
left outer join CON_Page page5_ on c1_._forwardPageId=page5_.pageId
left outer join CON_GameTargetPage page5_1_ on page5_.pageId=page5_1_.gameTargetPageId
left outer join CON_ContentPage page5_2_ on page5_.pageId=page5_2_.contentPageId
left outer join CON_VideoMenuPage page5_3_ on page5_.pageId=page5_3_.videoMenuPageId
left outer join CON_AdminMenuPage page5_4_ on page5_.pageId=page5_4_.adminMenuPageId
left outer join CON_MenuPage page5_5_ on page5_.pageId=page5_5_.menuPageId
left outer join CON_ReferencePage page5_6_ on page5_.pageId=page5_6_.referencePageId
left outer join CON_MediaTargetPage page5_7_ on page5_.pageId=page5_7_.mediaTargetPageId
left outer join CON_CurriculumTargetPage page5_8_ on page5_.pageId=page5_8_.curriculumTargetPageId
inner join CMN_CurriculumKeywords keywords6_ on c1_.curriculumTemplateId=keywords6_._curriculumTemplateId
inner join CMN_Keyword k2_ on keywords6_._keyword=k2_.keyword where k2_.keyword in (?) and this_._visitId=? order by this_.rxDate desc
The generated SQL in Hibernate 4.1.2:
Code:
select [some stuff]
from CUR_PrescribedOrder this_
inner join CUR_CurriculumTemplate c1_ on this_._curriculumId=c1_.curriculumTemplateId
left outer join CMN_Category category5_ on c1_._categoryId=category5_.categoryId
left outer join CUR_EduUnitTemplate educationu6_ on c1_._entryUnitId=educationu6_.educationUnitTemplateId
left outer join CUR_EduUnitTemplate educationu7_ on c1_._exitUnitId=educationu7_.educationUnitTemplateId
left outer join CON_Page page8_ on c1_._forwardPageId=page8_.pageId
left outer join CON_GameTargetPage page8_1_ on page8_.pageId=page8_1_.gameTargetPageId
left outer join CON_VideoMenuPage page8_2_ on page8_.pageId=page8_2_.videoMenuPageId
left outer join CON_CalendarTargetPage page8_3_ on page8_.pageId=page8_3_.calendarTargetPageId
left outer join CON_AdminMenuPage page8_4_ on page8_.pageId=page8_4_.adminMenuPageId
left outer join CON_ReferencePage page8_5_ on page8_.pageId=page8_5_.referencePageId
left outer join CON_ContentPage page8_6_ on page8_.pageId=page8_6_.contentPageId
left outer join CON_CurriculumTargetPage page8_7_ on page8_.pageId=page8_7_.curriculumTargetPageId
left outer join CON_MenuPage page8_8_ on page8_.pageId=page8_8_.menuPageId
left outer join CON_MediaTargetPage page8_9_ on page8_.pageId=page8_9_.mediaTargetPageId
inner join CMN_CurriculumKeywords keywords9_ on c1_.curriculumTemplateId=keywords9_._curriculumTemplateId
inner join CMN_Keyword k2_ on keywords9_._keyword=k2_.keyword
left outer join CMN_LanguageString languagest11_ on c1_._longDescriptionId=languagest11_.languageStringId
left outer join CMN_LanguageString languagest12_ on c1_._titleId=languagest12_.languageStringId
left outer join DEV_HL7LocationAlias hl7locatio13_ on this_._locationAliasId=hl7locatio13_.locationAliasId
left outer join CMN_HL7LocationAliasAttributes attributes14_ on hl7locatio13_.locationAliasId=attributes14_._loactionAliasId
left outer join CMN_Attribute attribute15_ on attributes14_._attributeId=attribute15_.attributeId
left outer join DEV_UserLocation userlocati16_ on hl7locatio13_._userLocationId=userlocati16_.userLocationId
left outer join CUR_OrderSet orderset17_ on this_._orderSetId=orderset17_.orderSetId
left outer join CMN_LanguageString languagest18_ on orderset17_._displayNameId=languagest18_.languageStringId
left outer join CUR_OrderSetDefinition ordersetde19_ on orderset17_._definitionId=ordersetde19_.definitionId
left outer join USR_Visit visit20_ on orderset17_._visitId=visit20_.visitId
left outer join USR_User user21_ on this_._prescriberId=user21_.userId
left outer join USR_Patient user21_1_ on user21_.userId=user21_1_._userId
left outer join USR_Admin user21_2_ on user21_.userId=user21_2_._userId
left outer join USR_Caregiver user21_3_ on user21_.userId=user21_3_._userId
left outer join CMN_UserAttributes attributes22_ on user21_.userId=attributes22_._userId
left outer join CMN_Attribute attribute23_ on attributes22_._attributeId=attribute23_.attributeId
left outer join CMN_GrantedPermission grantedper24_ on user21_.userId=grantedper24_._userId
left outer join CMN_GrantedRole grantedrol25_ on user21_.userId=grantedrol25_._userId
left outer join USR_Visit visit26_ on user21_1_._currentVisitId=visit26_.visitId
left outer join USR_Address address27_ on user21_1_._homeAddressId=address27_.addressId
left outer join DEV_HL7LocationAlias hl7locatio28_ on user21_2_._assumedlocationAliasId=hl7locatio28_.locationAliasId
left outer join CMN_LanguageString languagest29_ on this_._titleId=languagest29_.languageStringId
left outer join USR_Visit visit30_ on this_._visitId=visit30_.visitId
left outer join DEV_HL7LocationAlias hl7locatio31_ on visit30_._currentAlias=hl7locatio31_.locationAliasId
left outer join USR_Patient patient32_ on visit30_._patientId=patient32_._userId
left outer join USR_User patient32_1_ on patient32_._userId=patient32_1_.userId
left outer join WHT_VisitWhiteboardData visitwhite33_ on visit30_.visitId=visitwhite33_.visitId
where k2_.keyword in (?) and this_._visitId=? order by grantedper24_.startDate, this_.rxDate desc
That's 13 vs. 44 joined tables. Note that the 2nd query is ordering by CMN_GrantedPermission.startDate, even though that doesn't appear anywhere in the Criteria query. What could be going on here?
Thanks,
Brianna
Login
FAQ
Search