I'm trying to reduce the number of queries that need to get executed and am looking for some tips. We have a search query that is returning elements of type FooImpl. Each FooImpl class has a collection property mBars that contains elements of type BarImpl. The BarImpl class has no reference to the FooImpl that contains it.

Our original search query looked like this:


This results in n+1 queries where n is the number of FooImpls in my results. 1 query for the search itself, and 1 query for each FooImpl to load the Bars.

We modified the search to prefetch the Bars.


This preloaded the Bars just fine, but it resulted in duplicate entries for each of my FooImpls. There was one instance of each FooImpl for each BarImpl that was pre fetched. (ie. if the FooImpl points to 2 BarImpls, the search results contain 2 instances of that FooImpl)

We back outed the left join fetch and added a new query to load all of the BarImpl's for each foo impl in the result. We pass the list of FooImpls to the following query:



This works great at preloading the BarImpl instances but obviously doesn't reduce the number of queries. The collection cache for Foo.BarImpl doesn't contain these results (since there is nothing tying the FooImpl to *which* BarImpls it should contain. This means that there is still an additional query for each FooImpl.mBars.

Is there a way to define my left join fetch so duplicate results aren't returned?

Or, does anyone have any good techniques for taking the results of my last query and "seeding" the appropriate collection cache?

thanks for any ideas

Here's my hack workaround - I use it not in the query definition, but in the Java code. I don't know of a cleaner way, but this works.

List result = q.list();
//Convoluted way to ensure that we return only unique entries
Set rSet = new LinkedHashSet();
rSet.addAll(result);
List rList = new ArrayList();
rList.addAll(rSet);
return rList;