I've recently had issues with performance with getting a list of Objects which has as one of it's properties a Set of other objects. Essentially, our Banner class has a Set of bannerClickHistory objects which is a one-to-many mapping. When i go and grab a list of banners, i would also retrieve a set of bannerclickhistory objects. The problem i've encountered, is that over time as the number of rows grows in the Banner table with associated bannerclickhistory, it was taking almost up to 5 minutes to grab a collection of even 100 banner objects. I've turned on the show_sql = true flag, and now have noticed that the Hibernate SQL seems to generate an HSQL statement for getting the bannerclickhistory for every banner object (parent) that is fetched (resulting in 99 additional child calls). I thought that setting the fetch="join" in the parent .hbm file would do the trick to narrow down the number of queries by doing a join but it doesn't seem to do what i was exepecting. Is there something I'm missing or is my interpretation of this fetch="join" not correct? Thanks for any help. I have a sample of the mapping file and the execution code down below.

Hibernate version: 3.2.0

Name and version of the database you are using: Postgres 7.4


Mapping file for the banner


java code making the call, the call ends up querying for 3 Banner items in the list... and the HSQL shows that 3 calls were made to the bannerClickHistory for each banner. (this is the part where i'm wondering how do you join if even possible)



The HSQL output

try this: fetch="join".

Oops.. my mistake.. i pasted an old version of the hbm.xml file... my latest one does have fetch="join" in it... however hibernate still seems to ignore my mapping metadata setting....

I stumbled upon one thead that pointed to the Hibernate Users FAQ at http://hibernate.org/117.html and i found the entry ... Hibernate ignores my outer-join="true" or fetch="join" setting and fetch an association lazily, usering N+1 selects!

So after reading that i decided to modify my code a little and instead using criteria and actually using the .setFetchMode("bannerClickHistory", FetchMode.JOIN) to make my queries... this seem to have done the trick as i see only one Hibernate HSQL that contains a join query instead of N+1 selects! However I ran into another problem, where i was required to do add a Restriction into the query for a property that is in the Set of bannerClickHistory. Hibernate didn't like the idea of me doing something like.... .add(Restrictions.eq("bannerClickHistory.typeID", 1000)) as it would complain that the typeID property is not found. I then ran across a couple threads talking about using createAlias. That seem to have done the trick and now I can actually filter on that Set property, however now hibernate goes back to the original problem of not doing a join again. It seems as if it's now doing multiple queries cuz of the Alias defined. Am i suppose to apply a .setFetchMode to the Alias as well?

Thanks for any help

You can specify the join type on the createAlias method. e.g.