If you have a single user, who's returning 1333 Login objects, then that seems like a possible design issue, I would agree. I currently write a very large EAV system using NH, so I know all about returning a lot of rows ;) (12 million rows in the values table without really trying right now).
There's a few things you can do (I like numbered lists...): 1.) Change your association between User and Login to exist only at the service level of your application, not the model. What this means is drop the collection, and only have a many-to-one back to User from Login for save purposes. This will mean you won't be able to cascade saves from User to Login, but that will actually work out well for you, especially since it'll eliminate the IsDirty() check on each of those Logins. Now, you can get Logins by User, as a repository or Query object method, that executes HQL or Criteria query. Example: SELECT login FROM Login AS login WHERE login.User.Id = :UserId Now that you've got an HQL query (or a criteria if you wanted), you can set the .SetFirstResult() and .SetMaxResults() on the query object. Presto, instant paging. Now you can load just the logins you want, especially if you're rendering them out to something like a grid list, and you can set paging parameters asynchronously (assuming ajax or windows forms) to load them on demand. 2.) Avoid the WHERE statement on the actual mapping. That won't give you what you need, especially if what your Users needs is all the logins associated to it. Also, batching may not do what you think it does. The intent of batching is to say something like: I have retrieved 40 users. I access the Logins collection on user #1. I want the next 10 users to also load their Logins collections in one Sql Statement. That's where your IN statement gets fired. So rather than 40 selects to get Logins collections, if you batched 10, you'd end up with 4 statements. 3.) Use Database Engine Tuning Advisor with Sql, in conjuction with the SQL statements getting fired, and see what indexes it suggests for you. Typically you can see gains of 10-90% if there are not the proper indexes in place. 4.) Use 2nd level caching. This is big. The 2nd level cache holds two things. 1.) The class itself that's being cached. 2.) collections (which can be cached seperately). This second one would be of huge benefit in your scenario. 5.) Not worry about it. And I say this from experience. 1333 Login classes returned for a single user in the collection? Assuming those aren't super heavy classes, then you're probably a far cry away from having any significant negative performance impact. I prefer not to pre-optimize until I know something is running slow, then I'll start down the path of finding out why. I really wouldn't worry about it at this point. I return 5000 heavy objects in some associations and it's barely a blip on the radar in terms of speed impact. On Fri, Jan 29, 2010 at 10:18 AM, TheNephalim <[email protected]>wrote: > This is another issue that I'm working on and, unlike the previous > one, I don't have a solution for it. > > The issue is that I have a property on my parent object, User, and the > property is called Logins. Logins represents a collection of Login > information that includes when the user logged in, ip address, etc. > The problem is that the number of login objects gets rather large. > For example, my record count returned is 1333 on our development > database. > > Is there a way to limit the number of records returned for this? > > The mapping is for this property is as follows: > > HasMany<Login>(x => x.Logins) > .Table("SystemUserLogin") > .KeyColumn("UserId") > .OrderBy("SystemUserLoginId DESC") > .Inverse() > .AsBag(); > > I tried BatchSize, but that caused a really odd SQL query wherein the > WHERE clause was something like: > > WHERE logins0_.[UserId] in (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, > @p8, @p9) > > The variables were UserIds and I'm not sure from where it got all of > the IDs. I'm thinking that an easy way of doing this would be to add > a WHERE() to the mapping and limit the results returned by accessing > the ROW_NUMBER field in the result set, but, at this juncture, we're > still using SQL Server 2000. I may have to update this when we > upgrade to 2K8 shortly, but I'm not sure whether that would be the > correct thing to do from a design perspective. > > -Robert Eberhart > > -- > You received this message because you are subscribed to the Google Groups > "Fluent NHibernate" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]<fluent-nhibernate%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/fluent-nhibernate?hl=en. > > -- - Hudson http://www.bestguesstheory.com http://twitter.com/HudsonAkridge -- You received this message because you are subscribed to the Google Groups "Fluent NHibernate" 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/fluent-nhibernate?hl=en.
