hi,

I have a very simple question but I'm to stupid to find anything by my self.

Basically I have an Object with 5 1:N relations, mapped over Collections. This object has also 20 Other attributes.

What I want todo is only fetch this 20 Attributes without any association.

my query is like:

from MyObject a where a.sample.id = x

now my problem is it generate tons of sub selects, cause my sample has associations to other objects which have associations to more objects and so one. My complete tree is about 200 Objects, so it would be a little bit much to post here all the mapping files.

as sql query it should look like:

SELECT * FROM MyObject a where a.sample_id = x

how can I do this? I need to get this result in under 1 second and hibernate needs about 30 - 40 seconds to return the result.

I always think, that if you map your associations as lazy (in Hibernate 3.x it's the default mode), you don't have N+1 selects problem. How was your assiciations mapped? I needn't all your associations mapping, just one will be enough.

ok here are some more informations

basically i have a table with samples:


sample A
sample B
sample C

a sample is defiend as <Mapping Sample>

now the user selects from this table his sample of interest. The model than executes a query against the database

from Spectra a where a.sample.id = <selected sample id>

Spectra is defined as <Mapping Spectra>

I know hibernate can do this automatical using:

Collection = sample.getSpectra()

but because of existing code we need todo it with the HQl query.

now i takes roughly 30 seconds and I get a rendered table of x Spectra ( 300 - 500 roughly)

If I run the query in sql on the db layer it takes less than a second. From the logfile it looks like it fetches all reference to this spectra. Which would explain why it slowsdown so much. The question is just how to avoid this.


Mapping Sample

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping
>
<class
name="edu.ucdavis.genomics.metabolomics.binbase.bdi.types.experiment.sample.Sample"
table="`SAMPLES`"
dynamic-update="true"
dynamic-insert="true"
>

<id
name="id"
column="`sample_id`"
type="java.lang.Integer"
>
<generator class="assigned">
<!--
To add non XDoclet generator parameters, create a file named
hibernate-generator-params-Sample.xml
containing the additional parameters and place it in your merge dir.
-->
</generator>
</id>

<set
name="bins"
lazy="true"
inverse="true"
cascade="none"
sort="unsorted"
>

<key
column="`sample_id`"
>
</key>

<one-to-many
class="edu.ucdavis.genomics.metabolomics.binbase.bdi.types.experiment.sample.bin.Bin"
/>

</set>

<set
name="comments"
lazy="true"
inverse="true"
cascade="all"
sort="unsorted"
>

<key
column="type"
>
</key>

<one-to-many
class="edu.ucdavis.genomics.metabolomics.binbase.bdi.types.experiment.sample.SampleComment"
/>

</set>

<many-to-one
name="configuration"
class="edu.ucdavis.genomics.metabolomics.binbase.bdi.types.experiment.sample.Configuration"
cascade="none"
outer-join="auto"
update="false"
insert="false"
column="`configuration_id`"
/>

<many-to-one
name="type"
class="edu.ucdavis.genomics.metabolomics.binbase.bdi.types.experiment.sample.Type"
cascade="none"
outer-join="auto"
update="false"
insert="false"
column="`type`"
/>

<property
name="correctionFailedString"
type="java.lang.String"
update="false"
insert="false"
column="`correction_failed`"
not-null="true"
/>

<property
name="createdBinString"
type="java.lang.String"
update="false"
insert="false"
column="`created_bin`"
not-null="true"
/>

<many-to-one
name="experiment"
class="edu.ucdavis.genomics.metabolomics.binbase.bdi.types.experiment.ExperimentClass"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="`class`"
/>

<property
name="name"
type="java.lang.String"
update="true"
insert="true"
column="`sample_name`"
not-null="true"
/>

<property
name="newBinAllowedString"
type="java.lang.String"
update="true"
insert="true"
column="`allowNewBin`"
not-null="true"
/>

<set
name="notMatchedSpectra"
lazy="true"
inverse="true"
cascade="none"
sort="unsorted"
>

<key
column="`sample_id`"
>
</key>

<one-to-many
class="edu.ucdavis.genomics.metabolomics.binbase.bdi.types.experiment.sample.spectra.NotFoundSpectra"
/>

</set>

<property
name="priority"
type="java.lang.Integer"
update="true"
insert="true"
column="`priority`"
not-null="true"
/>

<property
name="saturatedString"
type="java.lang.String"
update="false"
insert="false"
column="`saturated`"
not-null="true"
/>

<property
name="setupxId"
type="java.lang.String"
update="true"
insert="false"
column="`setupx_id`"
not-null="true"
/>

<set
name="spectra"
lazy="true"
inverse="true"
cascade="none"
sort="unsorted"
>

<key
column="`sample_id`"
>
</key>

<one-to-many
class="edu.ucdavis.genomics.metabolomics.binbase.bdi.types.experiment.sample.spectra.Spectra"
/>

</set>

<property
name="status"
type="java.lang.Integer"
update="true"
insert="true"
column="`status`"
not-null="true"
/>

<property
name="version"
type="java.lang.Integer"
update="false"
insert="false"
column="`version`"
/>

<property
name="visibleString"
type="java.lang.String"
update="false"
insert="false"
column="`visible`"
not-null="true"
/>

<one-to-one
name="result"
class="edu.ucdavis.genomics.metabolomics.binbase.bdi.types.experiment.sample.SampleResult"
cascade="none"
outer-join="auto"
constrained="false"
/>

<set
name="resultLinks"
lazy="true"
inverse="true"
cascade="none"
sort="unsorted"
>

<key
column="`sample_id`"
>
</key>

<one-to-many
class="edu.ucdavis.genomics.metabolomics.binbase.bdi.types.experiment.sample.ResultLink"
/>

</set>

<property
name="finishedString"
type="java.lang.String"
update="false"
insert="false"
column="`finished`"
not-null="true"
/>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-Sample.xml
containing the additional properties and place it in your merge dir.
-->

</class>

</hibernate-mapping>


Mapping Spectra

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping
>
<class
name="edu.ucdavis.genomics.metabolomics.binbase.bdi.types.experiment.sample.spectra.Spectra"
table="`SPECTRA_FOUND`"
dynamic-update="true"
dynamic-insert="true"
where="`bin_id` is not null"
>

<id
name="id"
column="`spectra_id`"
type="java.lang.Integer"
>
<generator class="native">
<param name="sequence">SPECTRA_ID</param>
<!--
To add non XDoclet generator parameters, create a file named
hibernate-generator-params-Spectra.xml
containing the additional parameters and place it in your merge dir.
-->
</generator>
</id>

<property
name="apexSn"
type="java.lang.Double"
update="true"
insert="true"
column="`apex_sn`"
not-null="true"
/>

<property
name="apexSpec"
type="java.sql.Clob"
update="true"
insert="true"
column="`apex`"
not-null="true"
/>

<many-to-one
name="bin"
class="edu.ucdavis.genomics.metabolomics.binbase.bdi.types.experiment.sample.bin.Bin"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="`bin_id`"
/>

<set
name="comments"
lazy="true"
inverse="true"
cascade="all"
sort="unsorted"
>

<key
column="type"
>
</key>

<one-to-many
class="edu.ucdavis.genomics.metabolomics.binbase.bdi.types.experiment.sample.spectra.SpectraComment"
/>

</set>

<property
name="massSpec"
type="java.sql.Clob"
update="true"
insert="true"
column="`spectra`"
not-null="true"
/>

<property
name="purity"
type="java.lang.Double"
update="true"
insert="true"
column="`purity`"
not-null="true"
/>

<property
name="retentionIndex"
type="java.lang.Integer"
update="true"
insert="true"
column="`retention_index`"
not-null="true"
/>

<property
name="retentionTime"
type="java.lang.Integer"
update="true"
insert="true"
column="`retention_time`"
not-null="true"
/>

<many-to-one
name="sample"
class="edu.ucdavis.genomics.metabolomics.binbase.bdi.types.experiment.sample.Sample"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="`sample_id`"
/>

<property
name="similarity"
type="java.lang.Double"
update="true"
insert="true"
column="`match`"
not-null="true"
/>

<property
name="uniqueMass"
type="java.lang.Integer"
update="true"
insert="true"
column="`uniquemass`"
not-null="true"
/>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-Spectra.xml
containing the additional properties and place it in your merge dir.
-->

</class>

</hibernate-mapping>

Well, actually it's exactly the contrary. If you map your relations eagerly, you won't encounter lazy-loading (quite straightforward). If you map it lazy="true", then accessing it will trigger a select for each element.

Imagine an association between students and teachers, let's say a Teacher has a list of students. If you retrieve all the teachers with something like



Then, if you iterate on every teachers to display their students :


Each getStudents() method call will trigger a select. That's what is called N+1 select syndroma.

If you don't want to meet this problem, just modify your code so as to force hibernate to change the lazy mode for the relation you want to be directly loaded, so your code would look like :



This will do a select with a join to also retrieve the associated students with each teacher.

Hope this helps.

yeah I know this the problem is just that each object has a relation to another table which is then related to the next and I end with fetchign the complete database, which has several TB which leads to an out of MemoryException and frustrated users.

As background what I'm doing is to write a frontend for a massspec database of experiments in our lab. And the user needs all associations, but not all at the same time. Its more like browsing, show me this and this and maybe this. Basically its on gigantic tree which the users browses from the top node.

If I use lazy fetcihng (found an easy way to change the model) Everything works verywell except two things.

- if the user adds a relation, its not saved --> problem of my model so not related

- if the user makes a query against object Sample (which has an association to object Spectra) and he now does another query in another view (eclipse based framework) against a Spectra (which is related to a Sample) I always end up with an Collection is evicted Error when I'm going back to view one and select the sample ( to display its associated spectras in view number 3) .
Which makes sense, cause the objects of view 2 are newer than the sample in View 1 which wants to display its spectra association in view 3.

Now Is there a way in hibernate to disable the collection eviction (or the caching at all) or to automatically ( or better call it magical) updates the objects in all views? Or just point me to a good explanations of the Collecion is evicted, I'm just not able to find good stuff about this.

...sorry for this complicated writing, english is not my native language and the hole thing is quite complex...

Well, you're asking for a lot of things. So I'll try and answer where I can find a "?" :-). If you want to disable first level caching, you can use a StatelessSession, (sessionFactory.openStatelessSession()).

You're right. But let's see what the user wants:



So, how i understand, the user don't need to fetch all that associations in one query and use some of its properties, thats why i wrote that mapping association as lazy allow user to avoid N+1 select problem and don't fetch unnecessary collection. If user needs to iterate over associations and access its properties, then obviously he must fetch association eagerly or use Hibernate.initialize() on association or LazyInitializationException will be thrown by Hibernate.