Ayende @ Rahien

Ayende Rahien commented on Eagerly loading entity associations efficiently with NHibernate

Mon, 08 Feb 2010 20:22:30 GMT

Mohamed, Depending on which DB you use, IN queries do not use the proper indexing

Mohamed Meligy commented on Eagerly loading entity associations efficiently with NHibernate

Mon, 08 Feb 2010 13:18:25 GMT

SELECT * FROM ChildCollection child WHERE child.ParentId IN (SELECT parent.id FROM parent ParentCollection WHERE parent.RootObjectId = :aggregateRootObjectId)

Mohamed Meligy commented on Eagerly loading entity associations efficiently with NHibernate

Mon, 08 Feb 2010 13:16:50 GMT

Sorry, I meant child.ParentId .. Correction: select * from ChildCollection child WHERE child.id IN (SELECT parent.id FROM parent ParentCollection WHERE parent.RootObjectId = :aggregateRootObjectId)

Mohamed Meligy commented on Eagerly loading entity associations efficiently with NHibernate

Mon, 08 Feb 2010 13:10:05 GMT

Not even using a query like ... WHERE child.id IN (SELECT parent.id FROM parent ParentCollection WHERE parent.RootObjectId = :aggregateRootObjectId) ... ?

Ayende Rahien commented on Eagerly loading entity associations efficiently with NHibernate

Sat, 30 Jan 2010 23:16:06 GMT

Steven, You can't, period.

Steven De Kock commented on Eagerly loading entity associations efficiently with NHibernate

Thu, 28 Jan 2010 09:01:59 GMT

Ayende, Can you show us an example for loading collections of collections. How would you write a query and avoid a Cartesian product multiple levels deep ?

Ayende Rahien commented on Eagerly loading entity associations efficiently with NHibernate

Fri, 22 Jan 2010 06:57:23 GMT

Simon, It is likely that NH will gain something like this, yes.

Simon Bateman commented on Eagerly loading entity associations efficiently with NHibernate

Fri, 22 Jan 2010 00:02:04 GMT

Ayende, I lead the development of a C++ base ORM for a company starting in ’96 (using C++ and COM). The ORM is successfully running in a large percentage of the worlds news papers for the last 10 years It had a more formal approach to defining object hierarchies. We initially used a join approach to optimise the fetching of “contained collections” (associated entities) but as you’ve noted this became complex and slow loading many associations (including child and grandchild associations). We solved the issue by factoring out these loads into separate queries. Knowing the query to return entities for the root entity and the associations joins to that root entity it’s easy to load the associated entities via a select from their table joined to the root table. In detailed performance testing the cost of the additional query was easily outweighed by the additional cost of returning and processing the duplicated root entity data. You’ve practically done that with the 2 future joins – with the 2 queries join via the SQL union. Could nHibernate be updated to have an option to load associated entities via additional queries instead of via joins (for worse still fetches). I’ve moved onto a new company now and am having to do multiple future joins as you suggest to overcome some N+1 performance issues. Thanks, SB

Ayende Rahien commented on Eagerly loading entity associations efficiently with NHibernate

Thu, 21 Jan 2010 06:54:37 GMT

Graham, Yes & Yes.

Graham commented on Eagerly loading entity associations efficiently with NHibernate

Wed, 20 Jan 2010 23:20:29 GMT

Hmm... I think I've been too complicated then. In the past I've done something similar with MultiQueries but simply discarded the second (or Third or whatever) set of results as I know NH has loaded it. Though this is in a 1.2.0GA app, I assume Futures is the way to do this now in 2.1.3 ? If you choose to do so, I'm guessing there wil be a NH-Linq equivalent in NH 3.0.0?

Stephen commented on Eagerly loading entity associations efficiently with NHibernate

Sun, 17 Jan 2010 20:57:18 GMT

Many, many thanks for this post. I'm used to LLBLGen, which handles complex prefetch graphs *very* easily. Every time I've tried to use NHibernate, I've hit this issue, I couldn't find any article that would at least acknowledge the issue, and try to propose solutions. I still thing that NHibernate lacks an easy way to prefetch complex graphs (including filtering, sorting, etc.), though. Or is it that I am not thinking "the NHibernate way"?

Tolomaüs commented on Eagerly loading entity associations efficiently with NHibernate

Sun, 17 Jan 2010 09:22:21 GMT

Fabio, I'm not sure if I understood what you mean but I think the two features missing in NH at the moment to allow for this kind of complex fetching are: - combining fetch="subselect" with lazy="true"/"false" for one-to-many associations currently it always assumes lazy="true", thus requiring an Initialize() - and so an additional trip to the db - to load the collections. Specifying lazy="false" should result in combining the original sql query with the subselect query in one db trip IMO - the possibility to specify fetch="subselect" for many-to-one associations as well In case you want to load the may-to-ones that are references through one-to-many. e.g. blog.Posts.Writer I don't think it's a huge problem because yo can easily work around it by writing your own custom hql for each association of course.

Fabio Maulo commented on Eagerly loading entity associations efficiently with NHibernate

Sun, 17 Jan 2010 03:27:14 GMT

We should implements a pending task merging the behaviour of

Sam Meacham commented on Eagerly loading entity associations efficiently with NHibernate

Sun, 17 Jan 2010 00:12:18 GMT

VERY glad you posted this, as I've been trying to figure out how to load more complex object graphs in NH for quite some time now. I think Andrey Shchekin has it right though, the API for EF is superior when it comes to situations like this. The ease of use of the Include() method, and the flexibility of what it will interpret makes EF not only easier to use, but perhaps allows for certain queries that NH is not able to construct. Loading a single Blog, with both the Posts and Users collections populated is fine, but it's neither a large nor an overly complex object graph. EF allows you to put together pretty much anything you want. var q = from blog in context.Blogs .Include("Users.Friends") .Include("Users.Preferences") .Include("Comments.Replies") .Include("Posts.Comments") where blog.ID == 1 select blog; That would give you a blog object, with the Users collection already populated, and for each User, their Friends collections populated, each Users Preferences object pre fetched, the root Blogs Comments collection, with each comments Replies collection populated, the root blogs Posts, with each Posts comments, etc. EF will do this in a single query, and will ONLY produce the number of records required to get the data back. The way I've been doing this with NH, I get the data back I want, but the SQL generated does a bunch of flat (no subselects) left joins that give me a giant cartesian product that produces millions of records, taking minutes to execute on the server sometimes. Could you possibly show an example using Futures that does a more complex object graph like this? I'd be happy to see it working, but in the end, I still don't think it can be done in NH with a single query. Also, the Include() method is arbitrary enough that I don't have to construct the queries, specifying the joins, setting up the futures, etc. It's just a list of strings indicating the relationship paths I want to eagerly fetch. This allows me to create ORM agnostic methods like: T GetByKey(object key, string where, params string[] includes) That lets me grab any entity type by key, with a where filter, and a list of related entities I want to eagerly fetch. Can the Futures be set up generically, using nothing but a string[] of association paths you want to load along with your object? Including any number of branches and multiple levels of collections? Been trying to solve this for a while! I'd love some help...

Ayende Rahien commented on Eagerly loading entity associations efficiently with NHibernate

Sat, 16 Jan 2010 21:16:06 GMT

Sarong, SetMaxResults, but that might cause issues with the loading. At that point, I might want to execute two totally separate queries, first to get just the entities, then to load the related associations.

sarong commented on Eagerly loading entity associations efficiently with NHibernate

Sat, 16 Jan 2010 20:00:57 GMT

Ayende, and what to do if a have to do the same thing but paged?

Andrey Shchekin commented on Eagerly loading entity associations efficiently with NHibernate

Sat, 16 Jan 2010 19:44:43 GMT

In this specific example EF eager-loading API looks better: EF uses "Includes" which is an abstract concept, while NH uses "join" which is a DB concept.

Ayende Rahien commented on Eagerly loading entity associations efficiently with NHibernate

Sat, 16 Jan 2010 18:11:22 GMT

Nick, You can do the same using the Criteria API, which is what the release NH Linq provider is based on. And you can do the same with the trunk Linq provider.

Nick Berardi commented on Eagerly loading entity associations efficiently with NHibernate

Sat, 16 Jan 2010 15:32:02 GMT

Ayende, You cheated a little. You used HQL to generate your one query which is much more efficient than if you did the same through NHibernate LINQ. Same is true for EF, if you wanted to compare apples to apples you should have used EFQL.

cowgaR commented on Eagerly loading entity associations efficiently with NHibernate

Sat, 16 Jan 2010 14:37:57 GMT

excellent job! the only par missing are the results which we'll get using ANTLR based LINQ provider (that Steve Strong is working on) running the same LINQ query. Will it use futures as well and thus will "spit" the same SQL? Maybe it's the question for Steve...

Ayende Rahien commented on Eagerly loading entity associations efficiently with NHibernate

Sat, 16 Jan 2010 14:19:22 GMT

Diego, Yes, I did mean the opposite, fixed.

tobi commented on Eagerly loading entity associations efficiently with NHibernate

Sat, 16 Jan 2010 11:56:41 GMT

I recently hit this problem when stream processing a gigantic table with a few "child"-tables like in your example. Left join did a poor job while the union all pattern worked very nice. SQL Server is defiantely able to optimize it into a merge join when the subtables are clustered in the right way or have indices. I would like to request the union-all join for NHibernate as it seems to me that it is superior in every case.

Diego Mijelshon commented on Eagerly loading entity associations efficiently with NHibernate

Sat, 16 Jan 2010 11:49:18 GMT

"the first query to eager load all the one-to-many associations, and then a series of queries (one per required loaded collection) to load many-to-one or many-to-many associations." Didn't you mean the opposite? First query to load many-to-one, the rest to load one-to-many and many-to-many (collections).

Barry Dahlberg commented on Eagerly loading entity associations efficiently with NHibernate

Sat, 16 Jan 2010 10:38:32 GMT

You are scarily relevant sometimes, are you reading my code?