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.
