I have currently significant performance problems with a proliferation if SQL-Statements executed by hibernate (V3.5.1) during a request. So I tried to dive into the details to understand the details of eager-fetching.
Basically the problem seems to stem from the problem that we are using a matching entity that refers to two matching profile entities. It seems that hibernate only eager fetches on of the profiles with a join, but not both. The other is loaded by a select.
[MatchingData] ---- searchingProfile--> [ProfileData] ---- searchData --> [SearchData]
|
+- potentialMatch ---> [ProfileData] ---- searchData --> [SearchData]
All associations are marked by FetchMode.Join and @ManyToOne(@OneToOne(optional=false, fetch=FetchType.EAGER, cascade=...)
I have set up a simple example, to test various ways with HQL and Criterieas to fetch MatchingData via hibernate. Unfortunatelly the boiler plate code is too large, to post it her in detail. I can provide it on demand.
The test was
System.out.println("-------------------- this should be a single SQL statement ? ------------------------");
List<MatchingData> result1 = hibernateTemplate.find(
"from MatchingData");
openSession.clear();
System.out.println("-------------------- this should be a single SQL statement ? ------------------------");
List<MatchingData> result2 = hibernateTemplate.find(
"from MatchingData where potentialMatch.firstName!=null");
openSession.clear();
System.out.println("-------------------- this should be a single SQL statement ? ------------------------");
Criteria matchingDataCriteria1 = openSession.createCriteria(MatchingData.class);
matchingDataCriteria1.createCriteria("potentialMatch").add(Restrictions.eq("firstName", FIRSTNAME_TEST_PM));
matchingDataCriteria1.list();
openSession.clear();
System.out.println("-------------------- this is a a single SQL statement ? ------------------------");
Criteria matchingDataCriteria2 = openSession.createCriteria(MatchingData.class);
matchingDataCriteria2.list();
openSession.clear();
With 2 MatchingData and 3 involved ProfileData in the data base the outcome is
-------------------- this should be a single SQL statement ? ------------------------
Hibernate: select matchingda0_.id as id3_, matchingda0_.potentialMatch as potentia2_3_, matchingda0_.searchingProfile as searchin3_3_ from MatchingData matchingda0_
Hibernate: select profiledat0_.id as id0_3_, profiledat0_.firstName as firstName0_3_, profiledat0_.kontaktSparkasse_BLZ as kontaktS5_0_3_, profiledat0_.lastName as lastName0_3_, profiledat0_.profileType as profileT4_0_3_, profiledat0_.searchData_id as searchData6_0_3_, institutsd1_.BLZ as BLZ2_0_, institutsd1_.name as name2_0_, institutsd1_.parentHub_BLZ as parentHub3_2_0_, institutsd2_.BLZ as BLZ2_1_, institutsd2_.name as name2_1_, institutsd2_.parentHub_BLZ as parentHub3_2_1_, searchdata3_.id as id1_2_, searchdata3_.fakeContent as fakeCont2_1_2_ from ProfileData profiledat0_ inner join InstitutsData institutsd1_ on profiledat0_.kontaktSparkasse_BLZ=institutsd1_.BLZ left outer join InstitutsData institutsd2_ on institutsd1_.parentHub_BLZ=institutsd2_.BLZ left outer join SearchData searchdata3_ on profiledat0_.searchData_id=searchdata3_.id where profiledat0_.id=?
Hibernate: select profiledat0_.id as id0_3_, profiledat0_.firstName as firstName0_3_, profiledat0_.kontaktSparkasse_BLZ as kontaktS5_0_3_, profiledat0_.lastName as lastName0_3_, profiledat0_.profileType as profileT4_0_3_, profiledat0_.searchData_id as searchData6_0_3_, institutsd1_.BLZ as BLZ2_0_, institutsd1_.name as name2_0_, institutsd1_.parentHub_BLZ as parentHub3_2_0_, institutsd2_.BLZ as BLZ2_1_, institutsd2_.name as name2_1_, institutsd2_.parentHub_BLZ as parentHub3_2_1_, searchdata3_.id as id1_2_, searchdata3_.fakeContent as fakeCont2_1_2_ from ProfileData profiledat0_ inner join InstitutsData institutsd1_ on profiledat0_.kontaktSparkasse_BLZ=institutsd1_.BLZ left outer join InstitutsData institutsd2_ on institutsd1_.parentHub_BLZ=institutsd2_.BLZ left outer join SearchData searchdata3_ on profiledat0_.searchData_id=searchdata3_.id where profiledat0_.id=?
Hibernate: select profiledat0_.id as id0_3_, profiledat0_.firstName as firstName0_3_, profiledat0_.kontaktSparkasse_BLZ as kontaktS5_0_3_, profiledat0_.lastName as lastName0_3_, profiledat0_.profileType as profileT4_0_3_, profiledat0_.searchData_id as searchData6_0_3_, institutsd1_.BLZ as BLZ2_0_, institutsd1_.name as name2_0_, institutsd1_.parentHub_BLZ as parentHub3_2_0_, institutsd2_.BLZ as BLZ2_1_, institutsd2_.name as name2_1_, institutsd2_.parentHub_BLZ as parentHub3_2_1_, searchdata3_.id as id1_2_, searchdata3_.fakeContent as fakeCont2_1_2_ from ProfileData profiledat0_ inner join InstitutsData institutsd1_ on profiledat0_.kontaktSparkasse_BLZ=institutsd1_.BLZ left outer join InstitutsData institutsd2_ on institutsd1_.parentHub_BLZ=institutsd2_.BLZ left outer join SearchData searchdata3_ on profiledat0_.searchData_id=searchdata3_.id where profiledat0_.id=?
-------------------- this should be a single SQL statement ? ------------------------
Hibernate: select matchingda0_.id as id3_, matchingda0_.potentialMatch as potentia2_3_, matchingda0_.searchingProfile as searchin3_3_ from MatchingData matchingda0_ cross join ProfileData profiledat1_ where matchingda0_.potentialMatch=profiledat1_.id and (profiledat1_.firstName is not null)
Hibernate: select profiledat0_.id as id0_3_, profiledat0_.firstName as firstName0_3_, profiledat0_.kontaktSparkasse_BLZ as kontaktS5_0_3_, profiledat0_.lastName as lastName0_3_, profiledat0_.profileType as profileT4_0_3_, profiledat0_.searchData_id as searchData6_0_3_, institutsd1_.BLZ as BLZ2_0_, institutsd1_.name as name2_0_, institutsd1_.parentHub_BLZ as parentHub3_2_0_, institutsd2_.BLZ as BLZ2_1_, institutsd2_.name as name2_1_, institutsd2_.parentHub_BLZ as parentHub3_2_1_, searchdata3_.id as id1_2_, searchdata3_.fakeContent as fakeCont2_1_2_ from ProfileData profiledat0_ inner join InstitutsData institutsd1_ on profiledat0_.kontaktSparkasse_BLZ=institutsd1_.BLZ left outer join InstitutsData institutsd2_ on institutsd1_.parentHub_BLZ=institutsd2_.BLZ left outer join SearchData searchdata3_ on profiledat0_.searchData_id=searchdata3_.id where profiledat0_.id=?
Hibernate: select profiledat0_.id as id0_3_, profiledat0_.firstName as firstName0_3_, profiledat0_.kontaktSparkasse_BLZ as kontaktS5_0_3_, profiledat0_.lastName as lastName0_3_, profiledat0_.profileType as profileT4_0_3_, profiledat0_.searchData_id as searchData6_0_3_, institutsd1_.BLZ as BLZ2_0_, institutsd1_.name as name2_0_, institutsd1_.parentHub_BLZ as parentHub3_2_0_, institutsd2_.BLZ as BLZ2_1_, institutsd2_.name as name2_1_, institutsd2_.parentHub_BLZ as parentHub3_2_1_, searchdata3_.id as id1_2_, searchdata3_.fakeContent as fakeCont2_1_2_ from ProfileData profiledat0_ inner join InstitutsData institutsd1_ on profiledat0_.kontaktSparkasse_BLZ=institutsd1_.BLZ left outer join InstitutsData institutsd2_ on institutsd1_.parentHub_BLZ=institutsd2_.BLZ left outer join SearchData searchdata3_ on profiledat0_.searchData_id=searchdata3_.id where profiledat0_.id=?
Hibernate: select profiledat0_.id as id0_3_, profiledat0_.firstName as firstName0_3_, profiledat0_.kontaktSparkasse_BLZ as kontaktS5_0_3_, profiledat0_.lastName as lastName0_3_, profiledat0_.profileType as profileT4_0_3_, profiledat0_.searchData_id as searchData6_0_3_, institutsd1_.BLZ as BLZ2_0_, institutsd1_.name as name2_0_, institutsd1_.parentHub_BLZ as parentHub3_2_0_, institutsd2_.BLZ as BLZ2_1_, institutsd2_.name as name2_1_, institutsd2_.parentHub_BLZ as parentHub3_2_1_, searchdata3_.id as id1_2_, searchdata3_.fakeContent as fakeCont2_1_2_ from ProfileData profiledat0_ inner join InstitutsData institutsd1_ on profiledat0_.kontaktSparkasse_BLZ=institutsd1_.BLZ left outer join InstitutsData institutsd2_ on institutsd1_.parentHub_BLZ=institutsd2_.BLZ left outer join SearchData searchdata3_ on profiledat0_.searchData_id=searchdata3_.id where profiledat0_.id=?
-------------------- this should be a single SQL statement ? ------------------------
Hibernate: select this_.id as id3_5_, this_.potentialMatch as potentia2_3_5_, this_.searchingProfile as searchin3_3_5_, profiledat1_.id as id0_0_, profiledat1_.firstName as firstName0_0_, profiledat1_.kontaktSparkasse_BLZ as kontaktS5_0_0_, profiledat1_.lastName as lastName0_0_, profiledat1_.profileType as profileT4_0_0_, profiledat1_.searchData_id as searchData6_0_0_, institutsd4_.BLZ as BLZ2_1_, institutsd4_.name as name2_1_, institutsd4_.parentHub_BLZ as parentHub3_2_1_, institutsd5_.BLZ as BLZ2_2_, institutsd5_.name as name2_2_, institutsd5_.parentHub_BLZ as parentHub3_2_2_, searchdata6_.id as id1_3_, searchdata6_.fakeContent as fakeCont2_1_3_, profiledat7_.id as id0_4_, profiledat7_.firstName as firstName0_4_, profiledat7_.kontaktSparkasse_BLZ as kontaktS5_0_4_, profiledat7_.lastName as lastName0_4_, profiledat7_.profileType as profileT4_0_4_, profiledat7_.searchData_id as searchData6_0_4_ from MatchingData this_ inner join ProfileData profiledat1_ on this_.potentialMatch=profiledat1_.id left outer join InstitutsData institutsd4_ on profiledat1_.kontaktSparkasse_BLZ=institutsd4_.BLZ left outer join InstitutsData institutsd5_ on institutsd4_.parentHub_BLZ=institutsd5_.BLZ left outer join SearchData searchdata6_ on profiledat1_.searchData_id=searchdata6_.id inner join ProfileData profiledat7_ on this_.searchingProfile=profiledat7_.id where profiledat1_.firstName=?
Hibernate: select institutsd0_.BLZ as BLZ2_1_, institutsd0_.name as name2_1_, institutsd0_.parentHub_BLZ as parentHub3_2_1_, institutsd1_.BLZ as BLZ2_0_, institutsd1_.name as name2_0_, institutsd1_.parentHub_BLZ as parentHub3_2_0_ from InstitutsData institutsd0_ left outer join InstitutsData institutsd1_ on institutsd0_.parentHub_BLZ=institutsd1_.BLZ where institutsd0_.BLZ=?
Hibernate: select searchdata0_.id as id1_0_, searchdata0_.fakeContent as fakeCont2_1_0_ from SearchData searchdata0_ where searchdata0_.id=?
Hibernate: select searchdata0_.id as id1_0_, searchdata0_.fakeContent as fakeCont2_1_0_ from SearchData searchdata0_ where searchdata0_.id=?
-------------------- this should be a a single SQL statement ? ------------------------
Hibernate: select this_.id as id3_5_, this_.potentialMatch as potentia2_3_5_, this_.searchingProfile as searchin3_3_5_, profiledat2_.id as id0_0_, profiledat2_.firstName as firstName0_0_, profiledat2_.kontaktSparkasse_BLZ as kontaktS5_0_0_, profiledat2_.lastName as lastName0_0_, profiledat2_.profileType as profileT4_0_0_, profiledat2_.searchData_id as searchData6_0_0_, institutsd3_.BLZ as BLZ2_1_, institutsd3_.name as name2_1_, institutsd3_.parentHub_BLZ as parentHub3_2_1_, institutsd4_.BLZ as BLZ2_2_, institutsd4_.name as name2_2_, institutsd4_.parentHub_BLZ as parentHub3_2_2_, searchdata5_.id as id1_3_, searchdata5_.fakeContent as fakeCont2_1_3_, profiledat6_.id as id0_4_, profiledat6_.firstName as firstName0_4_, profiledat6_.kontaktSparkasse_BLZ as kontaktS5_0_4_, profiledat6_.lastName as lastName0_4_, profiledat6_.profileType as profileT4_0_4_, profiledat6_.searchData_id as searchData6_0_4_ from MatchingData this_ inner join ProfileData profiledat2_ on this_.potentialMatch=profiledat2_.id left outer join InstitutsData institutsd3_ on profiledat2_.kontaktSparkasse_BLZ=institutsd3_.BLZ left outer join InstitutsData institutsd4_ on institutsd3_.parentHub_BLZ=institutsd4_.BLZ left outer join SearchData searchdata5_ on profiledat2_.searchData_id=searchdata5_.id inner join ProfileData profiledat6_ on this_.searchingProfile=profiledat6_.id
Hibernate: select institutsd0_.BLZ as BLZ2_1_, institutsd0_.name as name2_1_, institutsd0_.parentHub_BLZ as parentHub3_2_1_, institutsd1_.BLZ as BLZ2_0_, institutsd1_.name as name2_0_, institutsd1_.parentHub_BLZ as parentHub3_2_0_ from InstitutsData institutsd0_ left outer join InstitutsData institutsd1_ on institutsd0_.parentHub_BLZ=institutsd1_.BLZ where institutsd0_.BLZ=?
Hibernate: select searchdata0_.id as id1_0_, searchdata0_.fakeContent as fakeCont2_1_0_ from SearchData searchdata0_ where searchdata0_.id=?
Hibernate: select searchdata0_.id as id1_0_, searchdata0_.fakeContent as fakeCont2_1_0_ from SearchData searchdata0_ where searchdata0_.id=?
As I understand: The HQL find does not do an eager fetching by itself, but loads the ProfileData (and further data) by individual selects. Of course I could add some Eager Fetching Hints, but this is quite troublesome in a complex data structure.
The Criteria Query manage to load one side (the potentialMatch) with eager loading in one statement together with its SearchData. The other side (searchingProfile) is also fetched, but subordinate entities (SearchData and other elements) are then fetched by explicit selects.
Question: Is there an elegant way to ensure loading of data in one swift?
