Heyo, " Query 1: session.Query<Listing>().BlaBla.ToList(). Query 2: access loadedListings[0].Items.First(), will load the Items collection for ALL loaded listings Query 3: access loadedListings[0].Items.First().Properties.First(), will load the Properties collection for ALL loaded Items Done. "
Query 3 is wrong. That will only load the properties for the FIRST item in the list. Then that becomes a query for EVERY item in the list. Then when you want to access the value of properties, it only does ONE. So while looping you end up doing an N+1. On Feb 6, 5:32 pm, Oskar Berggren <[email protected]> wrote: > Hi Brad, > > Obviously you know your system much better than me, but here are some > additional thoughts: > > On batch size: > It's late evening for me so forgive me if I have a mental accident > here, but it seems to me that with proper batch size on the > collections you should be able to get something similar to the > following scenario: > > Listing { Set<Item> Items } > Item { Set<Property> Properties } > Property { Name, Value } > > Query 1: session.Query<Listing>().BlaBla.ToList(). > Query 2: access loadedListings[0].Items.First(), will load the Items > collection for ALL loaded listings > Query 3: access loadedListings[0].Items.First().Properties.First(), > will load the Properties collection for ALL loaded Items > Done. > > Tree structures: > Have you considered other methods of efficiently loading tree > structures from SQL, such as "nested set" and "materialized path"? On > the other hand, since you have a known and limited depth, those > methods may not get you much compared to regular joins. > > On report: > To me, the concept of reading and displaying a large amount of data to > the user is a kind of report, in that it reports the contents for the > system. This is a factor behind the concept of > command/query-separation and using separate view models (where the > data may be stored denormalized to increase read performance). Btw, it > sounds like you are putting a lot of data in front of the user in a > single view. Is the user really able to cope with all that data at > once? > > Domain logic: > When you display all this information to the user, do you really need > (significant parts of) the domain logic in your domain model? If not, > then it may be that NHibernate isn't the right tool for this use case. > If you don't have a rich domain model, perhaps NHibernate isn't the > correct tool for this project. If you do have a rich domain model, > there may still be areas of the application which isn't optimal. > Everything is a compromise. (In some projects of mine I have many use > cases where I need to load less than 10 objects, usually less than > three levels deep.) > > If you don't really need anything in the entities for this usecase, > perhaps loading the data using raw SQL and NOT building the entities > is useful. > > On the other hand, if someone came up with a nice solution to > efficiently load such structures, it seems like an interesting new > feature for NHibernate. :) > > /Oskar > > 2012/2/6 Brad Laney <[email protected]>: > > > > > > > > > This is not a report. It just a simple output of an object. It just > > happens that every time we output the object we need to output these > > properties as well. > > > It is a listing on our site, which can contain many items inside it, > > like car, truck etc, and also every item can contain properties that > > describe the item. Like width, height, etc. > > > When we show a list of listings, then we need all the listings, all > > the items for each listing, and all the properties for each item. > > This is why it causes so many. There are up to 20 properties per > > listing item. > > > Almost everything we use in our business is complicated, we do not > > have simple "get one entity" needs. I haven't really been in a > > business where things are that simple. We have to show complex data to > > the user no matter what we are showing. And all the data is related. > > > We do have some rules, like, when we get a listing, we do not always > > want the items, but when we want the items, we always want the item > > properties. So when we do want the items, there are lots of objects > > that need to be created. > > > The lowest amount of queries we could do, I guess, is for every > > listing, get all the children. > > This would just be like 30 or 50 queries, but our DBA will still throw > > a fit. > > > The issue I have with "just do it raw". Then why not ALWAYS do it raw? > > If you have to do it raw once, then you are going to have a mapper, > > and it's what, one more line of code to make it do it for a list of > > items? Doesn't seem to really save you anything. If you have to do it > > once, then you can always do it. Which just means you write the code > > to do the manual ORM and then have the "overhead" for NH on simple > > stuff, when the simple stuff is the most common. > > > Is the answer, then, "sorry NH can't handle that yet"? > > > On Feb 2, 2:32 pm, Oskar Berggren <[email protected]> wrote: > >> I'm not sure exactly why this explodes to 500 queries. Maybe I'm > >> missing something but it seems to me the batchsize settings should be > >> able to keep that down to fairly few queries, though more than four. > >> Which batchsize have you set, and to what values? > > >> Another point to consider is that this sounds like some sort of report > >> page, which isn't exactly what NHibernate is optimized for. If this is > >> true, it may be useful to just use SQL to project the data to a > >> dataset or some simple view model and forget about the entities for > >> this case. There might also be the option of caching the "compiled" > >> view model in memory or serialized in e.g. a nosql database. > > >> How many objects are we talking about in each level here? If the > >> numbers grow beyond perhaps 10000 or so I would expect you to > >> experience sluggishness from NHibernate having to create all those > >> objects, even if data is fetched efficiently from the database. > > >> /Oskar > > >> 2012/2/2 Brad Laney <[email protected]>: > > >> > So... do it in raw sql, build the entities, and merge them into > >> > session? > > >> > That's going to take so freaking long, this is required in almost > >> > every single select query we do. > > >> > On Feb 2, 11:22 am, Darren Kopp <[email protected]> wrote: > >> >> Why not just use raw sql? I use nhibernate everywhere in my system > >> >> until I > >> >> need to do something that I can't do with nhibernate or would be > >> >> prohibitive performance wise to do so with nhibernate. Then I drop back > >> >> to > >> >> raw sql. > > >> > -- > >> > 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 > >> > athttp://groups.google.com/group/nhusers?hl=en. > > > -- > > 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 > > athttp://groups.google.com/group/nhusers?hl=en. -- 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.
