Loading...

nhusers@googlegroups.com

[Prev] Thread [Next]  |  [Prev] Date [Next]

[nhusers] Re: Deep Eager load avoiding N+1 and cartesian Brad Laney Fri Feb 03 17:10:02 2012

Hey,

I understand that is how NH works, but you need to understand I don't
want to do a Cartesian join for this.

I have A -> B -> C -> D

If I want D populated then i will be selecting the entire contents of
A, like, 40 times.

I understand it needs to know the link. And I understand I can use
futures to efficiently populate one level deep.
What I want is an efficient way to populate 4 levels deep, just like I
could with TSQL then join them together at the end.

I do not care the method, or technique to doing this. I just need an
object so that my DBA does not tell me I am bringing down the server
by having a high visited page run 500 queries every time it runs.

Doing this is not an acceptable solution, specially since it requires
left outer join:

from A left outer join fetch A.B left outer join fetch B.C left outer
join fetch C.D


On Feb 2, 6:15 am, Boris Drajer <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Sorry if I misunderstand, but it seems to me that the crucial point
> you're missing is that it's not enough to load the contents of the
> collections, NHibernate needs to load the collection as a part of its
> owner. If you retrieve individual objects, they will all be present in
> the session but the collections that contain them will remain
> uninitialized. When you access such collections, NHibernate executes a
> query and then for each row retrieved discovers that it's already
> cached in the session, so it only adds it to the collection. What you
> need to do is to load them *together*. This is best illustrated with
> HQL:
>
> from A a left join fetch a.Bs where [something]
>
> This loads the A's along with their collections of B's, not just A's
> and B's separately.
>
> With QueryOver, I think it would look something like this:
>
> var q = session.QueryOver<A>().Fetch(a =>
> a.Bs).Eager.Where([something]);
>
> You can execute multiple different queries of this sort to load
> different portions of the object graph.
>
> HTH!
>
> On 30 јан, 21:54, Brad Laney <[EMAIL PROTECTED]> wrote:
>
>
>
>
>
>
>
> > This relationship goes 4 deep. When it does lazy loading it ends up
> > running like 500+ queries.
> > This job can easily be done in 4 queries, and then the content
> > aggregated.
> > I have not touched batch size, because its already set, and the number
> > of records coming back is not the issue.
> > I have tried fetch but then you end up returning large amounts of
> > unnecessary data, which is why I want to use futures.
> > This is supposed work. It's documented as how it works. I just don't
> > know how to get it to do join type select with QueryOver.
> > I'm going to try it with the criteria API.
>
> > .SetFetchMode("Items", FetchMode.Select) <-- this is pretty much what
> > I want.
>
> > What is subselect fetching?
>
> > Is there a way to fetch an entire list of data, instead of one at a
> > time?
> > x.Items.Items <-- I want to fill every single item inside that
> > collection in one sql call instead of it lazy loading each one that I
> > want.
>
> > What I want it to run is:
>
> > select A.* from A where A.id = ?
> > select B.* from B where B.aid = ?
> > select C.* from C left join B where B.aid = ?
> > select D.* from D left join C left join B where b.aid = ?
>
> > I don't mind joining up to A instead of B. But yeah. Also notice I
> > don't want every single column, because that's pointless.
>
> > On Jan 30, 1:44 pm, Oskar Berggren <[EMAIL PROTECTED]> wrote:
>
> > > 2012/1/30 Brad Laney <[EMAIL PROTECTED]>:
>
> > > > Hello.
>
> > > > I am trying to avoid N+1 and cartesian product when I need to do deep
> > > > eager loads. I want to do this for performance. It is an EAV type of a
> > > > data structure.
>
> > > Is the problem too many queries, or that you absolutely cannot have
> > > more than one roundtrip to the database?
>
> > > [...]
>
> > > >                var r = s.QueryOver<NHMapTest1>()
> > > >                    .Where(x => x.Id == id)
> > > >                    .Future();
>
> > > >                s.QueryOver<NHMapTest2>()
> > > >                    .Where(x => x.Parent.Id == id)
> > > >                    .Future();
> > > > Running r.Single(); returns the entity, but if you do .Items it'll
> > > > query the DB, even tho they were selected in the 2nd future. Both
> > > > queries run correctly and return valid results.
>
> > > When you access the collection, even though a number of objects of the
> > > collection's member type has been loaded, NH doesn't know they are
> > > part of the collection, that's why it needs to query again. Looking at
> > > the child's Parent reference of loaded objects wouldn't be enough,
> > > since it doesn't know if those are _all_ the children.
>
> > > Have you set proper batch size on your collections and classes? And
> > > possibly specify subselect fetching for the collections. With that you
> > > should be able to rely on lazy fetch, but with a limited number of
> > > round trips.
>
> > > /Oskar

-- 
You received this message because you are subscribed to the Google Groups 
"nhusers" group.
To post to this group, send email to [EMAIL PROTECTED]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/nhusers?hl=en.