how about a level 2 cache, generally that will reduce the db calls very much effectively negating the effects of N+1
On Sun, Jul 6, 2014 at 3:28 AM, Vasiliy Shiryaev <[email protected] > wrote: > It seems you have not tried Batch loading? It works exactly as you > described > > > On 6 July 2014 00:18, Josh Graber <[email protected]> wrote: > >> We've been having a lot of issues with N + 1 fetch problems with our >> NHibernate implementation. >> >> We have a very complex data schema / relational graph. >> >> After much research, it appears that there are currently four main >> approaches to resolving relationships in NH: >> >> 1) Eager fetching. Not an option for complex object graphs because of the >> magnitude of the required request. >> >> 2) Lazy fetching out of the box. Results in N + 1 queries. If many child >> objects are referenced, gets prohibitive very quickly. >> >> 3) Use `Fetch()` to "eagerly" resolve only the fields you expect to need. >> This works well until you need to use multiple `FetchMany()` calls, which >> is very inefficient and results in duplicate objects in collections because >> of the Cartesian product that results from the query. >> >> 4) Use `ToFuture()` to cache some queries for NHibernate. I have had a >> lot of difficulty linking cached queries to the nested properties / >> references of a final query. Perhaps I'm just doing it wrong. >> >> After much thought, I decided that if I extended lazy fetching with a >> group load similar to `Fetch()` I would have a very efficient solution >> without the Cartesian product. Here's some sample code demonstrating how >> something like this might be consumed (using FluentNHibernate with >> NHibernate.Linq). >> >> var recentOrders = session.Query<Order>() >> .Where(order => order.PlacedDate > 5.Days().Ago()); >> >> recentOrders.Load(session, order => order.Customer); >> recentOrders.LoadAll(session, order => order.Items, items => >> { >> items.Load(session, item => item.ProductCategory); >> }); >> >> This would result in an initial query to get the orders that were placed >> in the last five days. >> >> The first load call would cause another query that would get all >> customers filtered by the recent order foreign keys. These customers would >> then be mapped into the recent order objects (LazyLoading would be >> fulfilled so that an object reference would not trigger a query). >> >> The second load call would send a query getting all items with a foreign >> key matching against the recent orders, these would also be mapped into the >> recent orders objects. >> >> Finally, a query would be performed against the Product Category table, >> filtering down to only categories matching any of the items in any of the >> recent orders. >> >> This solution does not result in any Cartesian products because each >> query only joins two tables. Each load / resolve step only requires 1 >> query, however, instead of N, where N is the number of child objects. >> >> I attempted to actually implement this solution and immediately ran into >> difficulty getting information (mapping information particularly) from >> NHibernate. Here's an initial attempt to implement `Load()` that seems like >> it works but it is hard to tell initially: >> >> public static void Load<TRootEntity, TChildEntity>(this IEnumerable< >> TRootEntity> entities, ISession session, Expression<Func<TRootEntity, >> TChildEntity>> getChild, Action<IEnumerable<TChildEntity>> loadChildren = >> null) >> where TChildEntity : DomainEntity >> { >> var childProperty = getProperty(getChild); >> var childIds = entities.Select(parent => getChildId(parent, >> childProperty)).Distinct().ToArray(); >> >> var childDictionary = session.Query<TChildEntity>() >> .Where(child => childIds.Contains(child.Id)) >> .ToDictionary(child => child.Id); >> >> entities.Each(parent => parent.setChild(childDictionary, >> childProperty)); >> >> if (loadChildren != null) >> { >> loadChildren(childDictionary.Values); >> } >> } >> >> public static int getChildId<TRootEntity>(TRootEntity parent, >> PropertyInfo childProperty) >> { >> var proxy = childProperty.GetValue(parent) as INHibernateProxy; >> return (int) proxy.HibernateLazyInitializer.Identifier; >> } >> >> private static void setChild<TRootEntity, TChildEntity>(this TRootEntity >> parent, IDictionary<int, TChildEntity> childDictionary, PropertyInfo >> childProperty) >> where TChildEntity : DomainEntity >> { >> var childId = getChildId(parent, childProperty); >> var child = childDictionary[childId]; >> var childProxy = child as INHibernateProxy; >> >> var proxy = childProperty.GetValue(parent) as INHibernateProxy; >> proxy.HibernateLazyInitializer.SetImplementation(childProxy. >> HibernateLazyInitializer.GetImplementation()); >> } >> >> private static PropertyInfo getProperty<TPropertyOwner, TPropertyReturn>( >> Expression<Func<TPropertyOwner, TPropertyReturn>> getterExpression) >> { >> var propertyName = UiNameHelper.BuildNameFrom(getterExpression); >> return typeof(TPropertyOwner).GetProperty(propertyName, typeof( >> TPropertyReturn)); >> } >> >> This solution does not attempt to update any child collection properties >> that point back to the parent object, which could be a big problem. >> >> I attempted to implement `LoadAll()` and quickly got stuck because there >> was no easy way to get the parent ID from the child objects. One solution >> to this is to add an expression for getting the parent from the child, >> similar to the collection expression, but this seems like it should not be >> necessary since the NH map already has the key column for the has many >> relationship. Furthermore, I have idea how to modify the >> PersistentGenericBag<TRootEntity> on the parent without triggering it to >> fetch via a query to the database (thereby defeating my purpose). >> >> Does this seem like potentially a good solution? Is there a good way to >> implement this currently? What if support for something like this was built >> into NHibernate? >> >> ~Josh Graber >> >> -- >> You received this message because you are subscribed to the Google Groups >> "nhusers" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To post to this group, send email to [email protected]. >> Visit this group at http://groups.google.com/group/nhusers. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/nhusers. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/nhusers. For more options, visit https://groups.google.com/d/optout.
