Eager fetching with ManyToMany results in Cartesian result

eriken · **Joined:** Mon May 08, 2006 6:43 am **Posts:** 1

Is it possible to use eager fetching in a bidirectional many-to-many relationship without ending up with a Cartesian join? I know that baseCriteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) will at least fix the result, but the time spent on the query is not improved.

Regards,
Eriken

emmanuel · **Posted:** Thu May 25, 2006 3:34 pm

use @Fetch(subselect) or @Fetch(select)

frer · **Joined:** Wed Jul 14, 2004 11:51 am **Posts:** 43

Hi,

I am encountering the same problem and do not know how to fix it. If my relation is one sided, the query takes only a quarter of a second but when using a bidirectional association, it takes more than 10 seconds (on a heavy loaded database).

Here are my mappings:

Code:

        <set
            name="providers"
            table="PRODUCTS2PROVIDERS"
            lazy="false"
            cascade="none"
            sort="org.mdarad.samples.totaltest.entities.ProviderComparator"
            outer-join="true">

            <key
                column="PRODUCTS_FK"
            >
            </key>

            <many-to-many
                class="org.mdarad.samples.totaltest.entities.Provider"
                column="PROVIDERS_FK"
                outer-join="true"
             />

        </set>

Code:

        <set
            name="products"
            table="PRODUCTS2PROVIDERS"
            lazy="false"
            cascade="none"
            sort="org.mdarad.samples.totaltest.entities.ProductComparator"
            outer-join="true"
            inverse="true">

            <key
                column="PROVIDERS_FK"
            >
            </key>

            <many-to-many
                class="org.mdarad.samples.totaltest.entities.Product"
                column="PRODUCTS_FK"
                outer-join="true"
             />

        </set>

I have tried to add to the provider "set" the fetch=select or fetch=subselect but do not get the expected results. The select takes even more time and the subselect doesn't change anything.

Any idea how I can fix this to have my eager fetch not end up as a cartesian join for my bidirectional many-to-many association?

Thanks,

Francois[/code]

frer · **Joined:** Wed Jul 14, 2004 11:51 am **Posts:** 43

It is important to note that I do not wish to lazy load my collections (I've seen many posts where people do the same when lazy-loading but this is not my case.

I've actually also tried this with a bidirectional many-to-one (and one-to-many) association and I encounter the same behaviour.

emmanuel · **Posted:** Tue Nov 07, 2006 6:37 pm

wrong forum