-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 2
  [ 25 posts ]  Go to page 1, 2  Next
  Previous topic | Next topic 
Author Message
Bugmaster
 Post subject: Avoiding N+1 Selects
PostPosted: Wed Oct 18, 2006 10:25 pm 
Beginner
Beginner

Joined: Fri Jun 02, 2006 1:14 am
Posts: 30
Hi All,

I have two pretty simple tables that represent a parent-child relationship: a Parent can have 1..N children, but a Child can only have one Parent.

I want to retrieve a list of Parents that fit certain criteria, and I want to do it in a single SELECT statement. Here's how I'd do this using raw SQL (pseudocode):

Code:
ResultSet r = db.Select("select p.*, c.* from parent p, child c where p.id = c.parent_id and p.something = 'some criteria'");
int oldParentId = -1;
Parent p = null;
IList list = new ArrayList();

while(r.NextRow()) {
  int parentId = r.GetInt32("parent_id");

  if(parentId != oldParentId) {
    p = new Parent();
    FillInAttributesOfParent(p, r);
    list.Add(p);
    oldParentId = parentId;
  }

  Child c = new Child();
  FillInAttributesOfChild(c, r);
  p.AddChild(c);
}
return list;


Is there a way to do something similar using NHibernate ? Right now, no matter how much I play with "join fetch" directives and mapping files, NHibernate seems to perform an extra select for each parent in the list (in order to select its children), and this approach is too slow for my purposes.
Top
 Profile  
 
sergey
 Post subject:
PostPosted: Thu Oct 19, 2006 12:42 am 
Contributor
Contributor

Joined: Wed May 11, 2005 4:59 pm
Posts: 1766
Location: Prague, Czech Republic
Have you tried "select p, c from Parent p left join fetch p.Children c"? Why didn't it work?
Top
 Profile  
 
desfen
 Post subject:
PostPosted: Thu Oct 19, 2006 3:41 am 
Beginner
Beginner

Joined: Wed Aug 10, 2005 6:21 pm
Posts: 24
Location: Antwerp, Belgium
http://www.ayende.com/Blog/CombatingThe ... rnate.aspx
Top
 Profile  
 
Bugmaster
 Post subject:
PostPosted: Thu Oct 19, 2006 1:08 pm 
Beginner
Beginner

Joined: Fri Jun 02, 2006 1:14 am
Posts: 30
sergey wrote:
Have you tried "select p, c from Parent p left join fetch p.Children c"? Why didn't it work?

It started opening cursors like crazy, and then ran out.

Edit: I am using the same exact pattern as the one on the blog you linked to, and I'm running out of open cursors right away. My guess is that NHibernate is trying to open a new cursor for each child collection. Seeing as my query returns about 1000 Parents, this is not very practical.
Top
 Profile  
 
sergey
 Post subject:
PostPosted: Fri Oct 20, 2006 3:25 am 
Contributor
Contributor

Joined: Wed May 11, 2005 4:59 pm
Posts: 1766
Location: Prague, Czech Republic
This sounds strange... Please post the version of NHibernate you are using and the relevant DEBUG logs.
Top
 Profile  
 
Bugmaster
 Post subject:
PostPosted: Fri Oct 20, 2006 1:19 pm 
Beginner
Beginner

Joined: Fri Jun 02, 2006 1:14 am
Posts: 30
sergey wrote:
This sounds strange... Please post the version of NHibernate you are using and the relevant DEBUG logs.

I am using NHibernate 1.0.2., but I think I figured out a workaround. The problem was that I had my Child class marked as non-lazy; when I marked it as lazy, NHibernate stopped trying to open a cursor for each child class.

Now, I only have a single SQL query, but my performance is still 10x (literally) worse than what I get from using raw SQL. What else could I be doing wrong ?

Also, is there a way to get Hibernate to automatically collect Child instances, attach them to the correct Parents, and return me only a list of unique Parents ? Currently, the HQL query "from Parent p left join fetch p.Children c" returns a list of non-unique parents, one parent for each child, and that's not what I ultimately want.
Top
 Profile  
 
Ayende Rahien
 Post subject:
PostPosted: Fri Oct 20, 2006 3:55 pm 
Contributor
Contributor

Joined: Sat Sep 24, 2005 11:25 am
Posts: 198
What is the generated SQL Statement?

The non unique that you mentioned is a feature, actually, useful for when you want to process a parent per child.

Run it through a HashedSet and it will return unique roots.
Top
 Profile  
 
Bugmaster
 Post subject:
PostPosted: Fri Oct 20, 2006 5:58 pm 
Beginner
Beginner

Joined: Fri Jun 02, 2006 1:14 am
Posts: 30
Argh ! I attempted to clean up my mappings in order to produce a more readable SQL output. In the process, I must have broken some piece of magic, because now I am getting N+1 selects again:

Code:
2006-10-20 14:51:06,733 [2532] DEBUG NHibernate.Hql.QueryTranslator - HQL: from Crs.NHibernate.NHGdnaAnnot a left join fetch a.Frags where  a.Method = :method and a.IsActive = 1 and a.RunId = :runId order by a.Id
2006-10-20 14:51:06,733 [2532] DEBUG NHibernate.Hql.QueryTranslator - SQL: select nhgdnaanno0_.ANNOT_ID as ANNOT_ID0_, frags1_.ANNOT_ID as ANNOT_ID1_, frags1_.FRAG_NO as FRAG_NO1_, nhgdnaanno0_.SCORE as SCORE0_, nhgdnaanno0_.IS_ACTIVE as IS_ACTIVE0_, nhgdnaanno0_.ANNOT_END as ANNOT_END0_, nhgdnaanno0_.RUN_ID as RUN_ID0_, nhgdnaanno0_.METHOD as METHOD0_, nhgdnaanno0_.GDNA_ID as GDNA_ID0_, nhgdnaanno0_.IS_WRONG as IS_WRONG0_, nhgdnaanno0_.ANNOT_START as ANNOT_ST7_0_, nhgdnaanno0_.STRAND as STRAND0_, frags1_.STRAND as STRAND1_, frags1_.FRAG_TYPE as FRAG_TYPE1_, frags1_.END_POS as END_POS1_, frags1_.START_POS as START_POS1_, frags1_.ANNOT_ID as ANNOT_ID__, frags1_.FRAG_NO as FRAG_NO__ from GDNA_ANNOT nhgdnaanno0_, GDNA_ANNOT_FRAG frags1_ where nhgdnaanno0_.ANNOT_ID=frags1_.ANNOT_ID(+) and ((nhgdnaanno0_.METHOD=:?)and(nhgdnaanno0_.IS_ACTIVE=1)and(nhgdnaanno0_.RUN_ID=:?)) order by  nhgdnaanno0_.ANNOT_ID, frags1_.FRAG_NO asc
2006-10-20 14:51:06,749 [2532] DEBUG NHibernate.Impl.BatcherImpl - Opened new IDbCommand, open IDbCommands :1
2006-10-20 14:51:06,749 [2532] DEBUG NHibernate.Impl.BatcherImpl - Building an IDbCommand object for the SqlString: select nhgdnaanno0_.ANNOT_ID as ANNOT_ID0_, frags1_.ANNOT_ID as ANNOT_ID1_, frags1_.FRAG_NO as FRAG_NO1_, nhgdnaanno0_.SCORE as SCORE0_, nhgdnaanno0_.IS_ACTIVE as IS_ACTIVE0_, nhgdnaanno0_.ANNOT_END as ANNOT_END0_, nhgdnaanno0_.RUN_ID as RUN_ID0_, nhgdnaanno0_.METHOD as METHOD0_, nhgdnaanno0_.GDNA_ID as GDNA_ID0_, nhgdnaanno0_.IS_WRONG as IS_WRONG0_, nhgdnaanno0_.ANNOT_START as ANNOT_ST7_0_, nhgdnaanno0_.STRAND as STRAND0_, frags1_.STRAND as STRAND1_, frags1_.FRAG_TYPE as FRAG_TYPE1_, frags1_.END_POS as END_POS1_, frags1_.START_POS as START_POS1_, frags1_.ANNOT_ID as ANNOT_ID__, frags1_.FRAG_NO as FRAG_NO__ from GDNA_ANNOT nhgdnaanno0_, GDNA_ANNOT_FRAG frags1_ where nhgdnaanno0_.ANNOT_ID=frags1_.ANNOT_ID(+) and ((nhgdnaanno0_.METHOD=:p00)and(nhgdnaanno0_.IS_ACTIVE=1)and(nhgdnaanno0_.RUN_ID=:p10)) order by  nhgdnaanno0_.ANNOT_ID, frags1_.FRAG_NO asc
2006-10-20 14:51:06,765 [2532] DEBUG NHibernate.Type.AnsiStringType - binding 'OCDNA' to parameter: 0
2006-10-20 14:51:06,765 [2532] DEBUG NHibernate.Type.Int32Type - binding '180824' to parameter: 1
2006-10-20 14:51:06,765 [2532] INFO  NHibernate.Loader.Loader - select nhgdnaanno0_.ANNOT_ID as ANNOT_ID0_, frags1_.ANNOT_ID as ANNOT_ID1_, frags1_.FRAG_NO as FRAG_NO1_, nhgdnaanno0_.SCORE as SCORE0_, nhgdnaanno0_.IS_ACTIVE as IS_ACTIVE0_, nhgdnaanno0_.ANNOT_END as ANNOT_END0_, nhgdnaanno0_.RUN_ID as RUN_ID0_, nhgdnaanno0_.METHOD as METHOD0_, nhgdnaanno0_.GDNA_ID as GDNA_ID0_, nhgdnaanno0_.IS_WRONG as IS_WRONG0_, nhgdnaanno0_.ANNOT_START as ANNOT_ST7_0_, nhgdnaanno0_.STRAND as STRAND0_, frags1_.STRAND as STRAND1_, frags1_.FRAG_TYPE as FRAG_TYPE1_, frags1_.END_POS as END_POS1_, frags1_.START_POS as START_POS1_, frags1_.ANNOT_ID as ANNOT_ID__, frags1_.FRAG_NO as FRAG_NO__ from GDNA_ANNOT nhgdnaanno0_, GDNA_ANNOT_FRAG frags1_ where nhgdnaanno0_.ANNOT_ID=frags1_.ANNOT_ID(+) and ((nhgdnaanno0_.METHOD=:p0)and(nhgdnaanno0_.IS_ACTIVE=1)and(nhgdnaanno0_.RUN_ID=:p1)) order by  nhgdnaanno0_.ANNOT_ID, frags1_.FRAG_NO asc
2006-10-20 14:51:06,765 [2532] DEBUG NHibernate.SQL - select nhgdnaanno0_.ANNOT_ID as ANNOT_ID0_, frags1_.ANNOT_ID as ANNOT_ID1_, frags1_.FRAG_NO as FRAG_NO1_, nhgdnaanno0_.SCORE as SCORE0_, nhgdnaanno0_.IS_ACTIVE as IS_ACTIVE0_, nhgdnaanno0_.ANNOT_END as ANNOT_END0_, nhgdnaanno0_.RUN_ID as RUN_ID0_, nhgdnaanno0_.METHOD as METHOD0_, nhgdnaanno0_.GDNA_ID as GDNA_ID0_, nhgdnaanno0_.IS_WRONG as IS_WRONG0_, nhgdnaanno0_.ANNOT_START as ANNOT_ST7_0_, nhgdnaanno0_.STRAND as STRAND0_, frags1_.STRAND as STRAND1_, frags1_.FRAG_TYPE as FRAG_TYPE1_, frags1_.END_POS as END_POS1_, frags1_.START_POS as START_POS1_, frags1_.ANNOT_ID as ANNOT_ID__, frags1_.FRAG_NO as FRAG_NO__ from GDNA_ANNOT nhgdnaanno0_, GDNA_ANNOT_FRAG frags1_ where nhgdnaanno0_.ANNOT_ID=frags1_.ANNOT_ID(+) and ((nhgdnaanno0_.METHOD=:p0)and(nhgdnaanno0_.IS_ACTIVE=1)and(nhgdnaanno0_.RUN_ID=:p1)) order by  nhgdnaanno0_.ANNOT_ID, frags1_.FRAG_NO asc
2006-10-20 14:51:06,765 [2532] DEBUG NHibernate.SQL - :p0 = 'OCDNA'
2006-10-20 14:51:06,765 [2532] DEBUG NHibernate.SQL - :p1 = '180824'
2006-10-20 14:51:06,781 [2532] DEBUG NHibernate.Impl.BatcherImpl - Opened Reader, open Readers :1
2006-10-20 14:51:06,781 [2532] DEBUG NHibernate.Loader.Loader - processing result set
2006-10-20 14:51:12,105 [2532] DEBUG NHibernate.Type.Int32Type - returning '1772692' as column: ANNOT_ID0_
2006-10-20 14:51:12,105 [2532] DEBUG NHibernate.Type.Int32Type - returning '1772692' as column: ANNOT_ID1_
2006-10-20 14:51:12,105 [2532] DEBUG NHibernate.Type.Int32Type - returning '1' as column: FRAG_NO1_
2006-10-20 14:51:12,121 [2532] DEBUG NHibernate.Impl.SessionImpl - loading [NHGdnaAnnot#1772692]
2006-10-20 14:51:12,121 [2532] DEBUG NHibernate.Impl.SessionImpl - attempting to resolve [NHGdnaAnnot#1772692]
2006-10-20 14:51:12,121 [2532] DEBUG NHibernate.Impl.SessionImpl - object not resolved in any cache [Crs.NHibernate.NHGdnaAnnot#1772692]
2006-10-20 14:51:12,121 [2532] DEBUG NHibernate.Persister.EntityPersister - Materializing entity: Crs.NHibernate.NHGdnaAnnot#1772692
2006-10-20 14:51:12,121 [2532] DEBUG NHibernate.Impl.BatcherImpl - Opened new IDbCommand, open IDbCommands :2
2006-10-20 14:51:12,121 [2532] DEBUG NHibernate.Impl.BatcherImpl - Building an IDbCommand object for the SqlString: SELECT nhgdnaanno0_.ANNOT_ID as ANNOT_ID0_, nhgdnaanno0_.SCORE as SCORE0_, nhgdnaanno0_.IS_ACTIVE as IS_ACTIVE0_, nhgdnaanno0_.ANNOT_END as ANNOT_END0_, nhgdnaanno0_.RUN_ID as RUN_ID0_, nhgdnaanno0_.METHOD as METHOD0_, nhgdnaanno0_.GDNA_ID as GDNA_ID0_, nhgdnaanno0_.IS_WRONG as IS_WRONG0_, nhgdnaanno0_.ANNOT_START as ANNOT_ST7_0_, nhgdnaanno0_.STRAND as STRAND0_ FROM GDNA_ANNOT nhgdnaanno0_ WHERE nhgdnaanno0_.ANNOT_ID=:ANNOT_ID
2006-10-20 14:51:12,121 [2532] DEBUG NHibernate.Type.Int32Type - binding '1772692' to parameter: 0
2006-10-20 14:51:12,121 [2532] INFO  NHibernate.Loader.Loader - SELECT nhgdnaanno0_.ANNOT_ID as ANNOT_ID0_, nhgdnaanno0_.SCORE as SCORE0_, nhgdnaanno0_.IS_ACTIVE as IS_ACTIVE0_, nhgdnaanno0_.ANNOT_END as ANNOT_END0_, nhgdnaanno0_.RUN_ID as RUN_ID0_, nhgdnaanno0_.METHOD as METHOD0_, nhgdnaanno0_.GDNA_ID as GDNA_ID0_, nhgdnaanno0_.IS_WRONG as IS_WRONG0_, nhgdnaanno0_.ANNOT_START as ANNOT_ST7_0_, nhgdnaanno0_.STRAND as STRAND0_ FROM GDNA_ANNOT nhgdnaanno0_ WHERE nhgdnaanno0_.ANNOT_ID=:p0
2006-10-20 14:51:12,121 [2532] DEBUG NHibernate.SQL - SELECT nhgdnaanno0_.ANNOT_ID as ANNOT_ID0_, nhgdnaanno0_.SCORE as SCORE0_, nhgdnaanno0_.IS_ACTIVE as IS_ACTIVE0_, nhgdnaanno0_.ANNOT_END as ANNOT_END0_, nhgdnaanno0_.RUN_ID as RUN_ID0_, nhgdnaanno0_.METHOD as METHOD0_, nhgdnaanno0_.GDNA_ID as GDNA_ID0_, nhgdnaanno0_.IS_WRONG as IS_WRONG0_, nhgdnaanno0_.ANNOT_START as ANNOT_ST7_0_, nhgdnaanno0_.STRAND as STRAND0_ FROM GDNA_ANNOT nhgdnaanno0_ WHERE nhgdnaanno0_.ANNOT_ID=:p0
2006-10-20 14:51:12,121 [2532] DEBUG NHibernate.SQL - :p0 = '1772692'
2006-10-20 14:51:12,707 [2532] DEBUG NHibernate.Impl.BatcherImpl - Opened Reader, open Readers :2
2006-10-20 14:51:12,707 [2532] DEBUG NHibernate.Loader.Loader - processing result set
2006-10-20 14:51:12,707 [2532] DEBUG NHibernate.Loader.Loader - result row: 1772692
2006-10-20 14:51:12,723 [2532] DEBUG NHibernate.Loader.Loader - Initializing object from DataReader: 1772692


NHGdnaAnnot is the parent; NHGdnaAnnotFrag is the child. It uses a composite primary key. I realize that this is not recommended by the NHibernate docs, but I'm using a legacy data model, and changing the DB is not an option.

Here is my mapping file:

Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0"
    namespace="Crs.NHibernate" assembly="Crs">

  <class name="NHGdnaAnnot" table="GDNA_ANNOT">

    <id name="Id" column="ANNOT_ID">
      <generator class="sequence">
        <param name="sequence">ANNOT_ID_SEQ</param>
      </generator>
    </id>

    <property name="RunId" column="RUN_ID"/>
   
    <property name="GdnaId" column="GDNA_ID" not-null="true"/>

    <property name="Method" type="AnsiString" column="METHOD"/>
    <property name="Score" column="SCORE"/>
    <property name="Strand" type="AnsiString" column="STRAND"/>
    <property name="AnnotStart" column="ANNOT_START"/>
    <property name="AnnotEnd" column="ANNOT_END"/>

    <property name="IsActive" column="IS_ACTIVE"/>
    <property name="IsWrong" column="IS_WRONG"/>

    <set name="Frags" order-by="FRAG_NO asc" lazy="true">
      <key column="ANNOT_ID"/>
      <one-to-many class="NHGdnaAnnotFrag"/>
    </set>

  </class>

  <class name="NHGdnaAnnotFrag" table="GDNA_ANNOT_FRAG">
    <composite-id>
      <key-many-to-one name="GdnaAnnot" class="NHGdnaAnnot" column="ANNOT_ID"></key-many-to-one>
      <key-property name="FragNo" column="FRAG_NO"></key-property>
    </composite-id>

    <property name="FragType" column="FRAG_TYPE" type="AnsiString" not-null="true"/>
    <property name="StartPos" column="START_POS" not-null="true"/>
    <property name="EndPos" column="END_POS" not-null="true"/>
    <property name="Strand" column="STRAND" type="AnsiString" not-null="true"/>

  </class>

</hibernate-mapping>


And here is the C# code:

Code:
        public static IList Select(String hql, params object[] args) {
            IQuery query = currentSession.CreateQuery(hql);
            for(int i=0; i<args.Length; i+=2) {
                string name = (string)args[i];
                object value = args[i + 1];
                if (value is string) {
                    query.SetAnsiString(name, (string)value); // Gets around the OCI error
                } else {
                    query.SetParameter(name, value);
                }
            }
            return query.List();
        }

...

foreach (NHGdnaAnnot annot in Select("from NHGdnaAnnot a left join fetch a.Frags where " +
                    " a.Method = :method and a.IsActive = 1 and a.RunId = :runId order by a.Id",
                   "method", AnnotMethods.OCDNA,
                   "runId", 180824)) {
...
}



So, why is it that I get N+1 selects with this code ? I am getting fairly frustrated by all the magic surrounding NHibernate :-(
Top
 Profile  
 
Bugmaster
 Post subject:
PostPosted: Fri Oct 20, 2006 6:14 pm 
Beginner
Beginner

Joined: Fri Jun 02, 2006 1:14 am
Posts: 30
Forgot to mention: I understand that piping results through a HashedSet is the accepted practice, but I have about 78000 rows of Parents (and about 10x that of the Children). All I really want to do is iterate through these rows, identify unique parents, and do some computation on their children. Creating a List of 78k elements, and then hashing them in a HashedSet, is something I want to avoid -- especially since I expect to have 300k rows or more in the future.
Top
 Profile  
 
Ayende Rahien
 Post subject:
PostPosted: Fri Oct 20, 2006 7:50 pm 
Contributor
Contributor

Joined: Sat Sep 24, 2005 11:25 am
Posts: 198
About the uniquing issue.
There is an overload of List() that takes an IList as parameter.
You can use that to make sure that no duplicate items are inserted (basically checking and rejecting any adds already existing).
Top
 Profile  
 
Ayende Rahien
 Post subject:
PostPosted: Fri Oct 20, 2006 7:52 pm 
Contributor
Contributor

Joined: Sat Sep 24, 2005 11:25 am
Posts: 198
NHGdnaAnnotFrag isn't lazy, shouldn't it be?
Top
 Profile  
 
Bugmaster
 Post subject:
PostPosted: Fri Oct 20, 2006 8:33 pm 
Beginner
Beginner

Joined: Fri Jun 02, 2006 1:14 am
Posts: 30
Ayende Rahien wrote:
About the uniquing issue.
There is an overload of List() that takes an IList as parameter.
You can use that to make sure that no duplicate items are inserted (basically checking and rejecting any adds already existing).

How is this different from using a HashedSet ?

Quote:
NHGdnaAnnotFrag isn't lazy, shouldn't it be?

I actually don't know... why do you think it should be lazy ?

My current goal is to produce a list of NHGdnaAnnots, with their child collections already populated, by using a single query, as opposed to N+1 queries. Since I'm specifying (or attempting to, at least) my fetch strategy in HQL, I didn't think that class laziness would matter...
Top
 Profile  
 
Ayende Rahien
 Post subject:
PostPosted: Sat Oct 21, 2006 3:21 am 
Contributor
Contributor

Joined: Sat Sep 24, 2005 11:25 am
Posts: 198
Quote:
How is this different from using a HashedSet ?


You don't need to create a 78K list first, it just create what is needed.

SELECT N+1:
The SQL Generated looks fine:

Code:
select
    nhgdnaanno0_.ANNOT_ID as ANNOT_ID0_,
    frags1_.ANNOT_ID as ANNOT_ID1_,
    frags1_.FRAG_NO as FRAG_NO1_,
    nhgdnaanno0_.SCORE as SCORE0_,
    nhgdnaanno0_.IS_ACTIVE as IS_ACTIVE0_,
    nhgdnaanno0_.ANNOT_END as ANNOT_END0_,
    nhgdnaanno0_.RUN_ID as RUN_ID0_,
    nhgdnaanno0_.METHOD as METHOD0_,
    nhgdnaanno0_.GDNA_ID as GDNA_ID0_,
    nhgdnaanno0_.IS_WRONG as IS_WRONG0_,
    nhgdnaanno0_.ANNOT_START as ANNOT_ST7_0_,
    nhgdnaanno0_.STRAND as STRAND0_,
    frags1_.STRAND as STRAND1_,
    frags1_.FRAG_TYPE as FRAG_TYPE1_,
    frags1_.END_POS as END_POS1_,
    frags1_.START_POS as START_POS1_,
    frags1_.ANNOT_ID as ANNOT_ID__,
    frags1_.FRAG_NO as FRAG_NO__
from
    GDNA_ANNOT nhgdnaanno0_,
    GDNA_ANNOT_FRAG frags1_
where
    nhgdnaanno0_.ANNOT_ID = frags1_.ANNOT_ID(+)  and
    (
      ( nhgdnaanno0_.METHOD = :? ) and
      ( nhgdnaanno0_.IS_ACTIVE = 1 ) and
      ( nhgdnaanno0_.RUN_ID = :? )
    )
order by
    nhgdnaanno0_.ANNOT_ID,
    frags1_.FRAG_NO asc


Interestingly, it looks like the N+1 is being caused by the parents, not the children.
Start by marking both classes as lazy (always a good thing) run the query again and post the logs...
Top
 Profile  
 
Bugmaster
 Post subject:
PostPosted: Mon Oct 23, 2006 2:06 pm 
Beginner
Beginner

Joined: Fri Jun 02, 2006 1:14 am
Posts: 30
Ayende Rahien wrote:
You don't need to create a 78K list first, it just create what is needed.

But I have 78k unique parents...

Quote:
Interestingly, it looks like the N+1 is being caused by the parents, not the children. Start by marking both classes as lazy (always a good thing) run the query again and post the logs...

Sure, here's the new generated SQL (looks about the same to me):
Code:
SELECT   nhgdnaanno0_.annot_id AS annot_id0_, frags1_.annot_id AS annot_id1_,
         frags1_.frag_no AS frag_no1_, nhgdnaanno0_.score AS score0_,
         nhgdnaanno0_.is_active AS is_active0_,
         nhgdnaanno0_.annot_end AS annot_end0_,
         nhgdnaanno0_.run_id AS run_id0_, nhgdnaanno0_.method AS method0_,
         nhgdnaanno0_.gdna_id AS gdna_id0_,
         nhgdnaanno0_.is_wrong AS is_wrong0_,
         nhgdnaanno0_.annot_start AS annot_st7_0_,
         nhgdnaanno0_.strand AS strand0_, frags1_.strand AS strand1_,
         frags1_.frag_type AS frag_type1_, frags1_.end_pos AS end_pos1_,
         frags1_.start_pos AS start_pos1_, frags1_.annot_id AS annot_id__,
         frags1_.frag_no AS frag_no__
    FROM gdna_annot nhgdnaanno0_, gdna_annot_frag frags1_
   WHERE nhgdnaanno0_.annot_id = frags1_.annot_id(+)
     AND (    (nhgdnaanno0_.method = :p0)
          AND (nhgdnaanno0_.is_active = 1)
          AND (nhgdnaanno0_.run_id = :p1)
         )
ORDER BY nhgdnaanno0_.annot_id, frags1_.frag_no ASC

As to the log, I see a lot of statements like these:
Code:
2006-10-23 10:51:45,606 [3604] DEBUG NHibernate.Impl.SessionImpl - reading row
2006-10-23 10:51:45,606 [3604] DEBUG NHibernate.Type.Int32Type - returning '1836945' as column: ANNOT_ID__
2006-10-23 10:51:45,606 [3604] DEBUG NHibernate.Type.Int32Type - returning '7' as column: FRAG_NO__
2006-10-23 10:51:45,606 [3604] DEBUG NHibernate.Impl.SessionImpl - loading [NHGdnaAnnot#1836945]
2006-10-23 10:51:45,606 [3604] DEBUG NHibernate.Impl.SessionImpl - attempting to resolve [NHGdnaAnnot#1836945]
2006-10-23 10:51:45,621 [3604] DEBUG NHibernate.Impl.SessionImpl - resolved object in session cache [Crs.NHibernate.NHGdnaAnnot#1836945]
2006-10-23 10:51:45,621 [3604] DEBUG NHibernate.Impl.SessionImpl - loading [NHGdnaAnnotFrag#Crs.NHibernate.NHGdnaAnnotFrag]
2006-10-23 10:51:45,621 [3604] DEBUG NHibernate.Impl.SessionImpl - attempting to resolve [NHGdnaAnnotFrag#Crs.NHibernate.NHGdnaAnnotFrag]
2006-10-23 10:51:45,621 [3604] DEBUG NHibernate.Impl.SessionImpl - resolved object in session cache [Crs.NHibernate.NHGdnaAnnotFrag#Crs.NHibernate.NHGdnaAnnotFrag]
2006-10-23 10:51:45,637 [3604] DEBUG NHibernate.Type.Int32Type - returning '1836945' as column: ANNOT_ID0_2006-10-23 10:51:45,637 [3604] DEBUG NHibernate.Type.Int32Type - returning '1836945' as column: ANNOT_ID1_
2006-10-23 10:51:45,637 [3604] DEBUG NHibernate.Type.Int32Type - returning '8' as column: FRAG_NO1_2006-10-23 10:51:45,637 [3604] DEBUG NHibernate.Impl.SessionImpl - loading [NHGdnaAnnot#1836945]
2006-10-23 10:51:45,637 [3604] DEBUG NHibernate.Impl.SessionImpl - attempting to resolve [NHGdnaAnnot#1836945]
2006-10-23 10:51:45,637 [3604] DEBUG NHibernate.Impl.SessionImpl - resolved object in session cache [Crs.NHibernate.NHGdnaAnnot#1836945]
2006-10-23 10:51:45,637 [3604] DEBUG NHibernate.Loader.Loader - result row: 1836945, Crs.NHibernate.NHGdnaAnnotFrag
2006-10-23 10:51:45,637 [3604] DEBUG NHibernate.Loader.Loader - Initializing object from DataReader: Crs.NHibernate.NHGdnaAnnotFrag

Later on, I get this:
Code:
2006-10-23 10:59:12,079 [3604] DEBUG NHibernate.Impl.SessionImpl - done materializing entity [Crs.NHibernate.NHGdnaAnnotFrag#Crs.NHibernate.NHGdnaAnnotFrag]
2006-10-23 10:59:12,079 [3604] DEBUG NHibernate.Impl.SessionImpl - resolving associations for: [Crs.NHibernate.NHGdnaAnnotFrag#Crs.NHibernate.NHGdnaAnnotFrag]
2006-10-23 10:59:12,079 [3604] DEBUG NHibernate.Impl.SessionImpl - done materializing entity [Crs.NHibernate.NHGdnaAnnotFrag#Crs.NHibernate.NHGdnaAnnotFrag]
2006-10-23 10:59:12,094 [3604] DEBUG NHibernate.Impl.SessionImpl - resolving associations for: [Crs.NHibernate.NHGdnaAnnotFrag#Crs.NHibernate.NHGdnaAnnotFrag]
2006-10-23 10:59:12,094 [3604] DEBUG NHibernate.Impl.SessionImpl - done materializing entity [Crs.NHibernate.NHGdnaAnnotFrag#Crs.NHibernate.NHGdnaAnnotFrag]
...
2006-10-23 11:00:18,505 [3604] DEBUG NHibernate.Impl.SessionImpl - collection fully initialized: [Crs.NHibernate.NHGdnaAnnot.Frags#1851158]
2006-10-23 11:00:18,505 [3604] DEBUG NHibernate.Impl.SessionImpl - collection fully initialized: [Crs.NHibernate.NHGdnaAnnot.Frags#1780117]
2006-10-23 11:00:18,505 [3604] DEBUG NHibernate.Impl.SessionImpl - collection fully initialized: [Crs.NHibernate.NHGdnaAnnot.Frags#1795340]
2006-10-23 11:00:18,505 [3604] DEBUG NHibernate.Impl.SessionImpl - collection fully initialized: [Crs.NHibernate.NHGdnaAnnot.Frags#1810563]
2006-10-23 11:00:18,505 [3604] DEBUG NHibernate.Impl.SessionImpl - collection fully initialized: [Crs.NHibernate.NHGdnaA

So, it looks like NHibernate does several passes over the data (including the extra pass needed to construct the final List), and it keeps all data in the session cache in order to properly resolve associations. On the plus side, with the parent class marked as lazy, there are no more extra queries (though I can't understand why). On the minus side, this way of loading the data is still 10x (according to the timer, at least) slower than using plain old SQL.

Hence, I had some questions:

* Is there a way to tell NHibernate to iterate over the data, as opposed to constructing a List ? The Java Hibernate has a "Query.scroll" method, but I couldn't find a corresponding method in NHibernate 1.0.2.
* Is there a way to reduce memory usage ? I am expecting to have hundreds of thousands of objects, of which I need maybe 20-30 at the same time (as per the SQL pseudocode above)
* In general, what else can I do in order to speed up the query ?
* Why do I need to mark all my classes as lazy, anyway ?
Top
 Profile  
 
Ayende Rahien
 Post subject:
PostPosted: Mon Oct 23, 2006 5:07 pm 
Contributor
Contributor

Joined: Sat Sep 24, 2005 11:25 am
Posts: 198
Is there any relation to:
http://jira.nhibernate.org/browse/NH-766
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 2
  [ 25 posts ]  Go to page 1, 2  Next

Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.