Ayende @ Rahien

Stefan Wenig commented on If you are way off in the deep end, there is only so much that tooling can do for you

Tue, 09 Feb 2010 09:26:04 GMT

Ayende, if I understand correctly, the only way in NH to have this n-query behavior is to manually write several queries and throw away all but one result set, and there's no way at all to prefetch collections of collections. If you want that, it's all in re-linq. There's a thread on the NH users list where I posted some details for a similar problem: [groups.google.com/.../c05d2c7ea7233340](http://groups.google.com/group/nhibernate-development/browse_thread/thread/c05d2c7ea7233340#msg_64032597075c7130) (only in this case it's easier, because that's just what re-linq's fetch mechanism is made for. you just need to add some extension methods to your namespace to turn it on.) there's one disadvantage: all of the created LINQ queries have to be converted to HQL and then SQL from scratch - a native NH solution could do better. however, that's what you'd get from manually built fetching queries too, and Steve's provider does enough caching to make this a neglectible problem. also, you can still replace it with an optimized implementation later if it should turn out desirable.

Ayende Rahien commented on If you are way off in the deep end, there is only so much that tooling can do for you

Mon, 08 Feb 2010 12:15:07 GMT

Mohamed, NH's eager fetching will work nicely as long as you use don't try to load more than a single collection eagerly per query. I suggest loading all single associations in the first query, and then a single collection association per each of the next queries.

Mohamed Meligy commented on If you are way off in the deep end, there is only so much that tooling can do for you

Mon, 08 Feb 2010 11:54:47 GMT

@Ayende, Ah.. sorry that I didn't notice it when the question came to mind. Actually, since I'm old user of LLBLGen Pro and read a lot about how it does fetching, I thought this was the default trend for ORM makers. Very big false assumption as it seems. I thought of futures already. Thanks for showing the complete example in the other post. Is there an easier way than futures in case of more than 2 fetch paths? I have been in situations when 3+ paths were required in a really huge screen. Deciding whether that decision was right will need much domain explanations but at least it wasn't very bad because it was agreed upon by 4 senior developers. So, it's possible to need 3+ fetch paths. Using LLBLGen Pro it's as simple as PreFetchPath.SubPath.Add (....childPrefetchPath). I thought NH Eager fetch works the same...

Ayende Rahien commented on If you are way off in the deep end, there is only so much that tooling can do for you

Mon, 08 Feb 2010 11:07:31 GMT

Mohamed, Take a look at the linked post

Mohamed Meligy commented on If you are way off in the deep end, there is only so much that tooling can do for you

Mon, 08 Feb 2010 11:04:56 GMT

@Ayende Is there a way to make NHibernate apply this in 3 queries? Then it'd use the identity map to connect the results together. Having Multiple Result Sets supported for the ADO.NET connection created under the hood is one feature that can be taken benefit of here..

Frans Bouma commented on If you are way off in the deep end, there is only so much that tooling can do for you

Sun, 07 Feb 2010 10:21:52 GMT

Tobi, that has several drawbacks: 1) your query result will get very wide, as every column of every subresult has to be present. 2) with every 1:n relationship traversal, you'll get an explosion of rows 3) multiple paths originating from the same parent gives problems (explosion of columns) 4) a lot of null-ed values are reported back. 5) your entity fetch engine has to be adjusted to consume these odd sets, while when you do it the way llblgen or re-linq do it, you can build it ON TOP of existing fetch pipelines, so it's really an easy job. and IMHO more efficient.

Stefan Wenig commented on If you are way off in the deep end, there is only so much that tooling can do for you

Sat, 06 Feb 2010 16:31:07 GMT

Like LLBLGen, our own ORM re-store is automatically splitting this into three separate queries: var query = from blog b in QueryFactory.Linq() where b.id == 1; query = query.Fetch (b => b.Posts).ThenFetch (p => p.Comments); This will create two additional queries, each including the previous query's conditions but only one set of SELECT clauses. Now the funny thing is that we talked about that feature, because it is in re-linq which eventually got used for LINQ2NH too, and you said you'd like a Cartesian product better. I didn't bother to ask for a reason back than, but I kept wondering why you would - in a general case - want to buy the advantage of a single query execution for the price of that much data overhead. Now it seems we're not that far off, are we? (mail from feb 2 2009 if you care) That said, I would like to have the option to switch to Cartesian products explicitly, because in some cases the redundant data may be neglectible compared to the effort of having to find the initial set of records (blogs in your example) multiple times. Like, in queries that can't quite use the index...

configurator commented on If you are way off in the deep end, there is only so much that tooling can do for you

Sat, 06 Feb 2010 15:48:30 GMT

So wouldn't the query var blogs = s.CreateQuery( @"from Posts p left join fetch p.Comments where p.Id = :id") .SetParameter("id", 1) .List

Udi Dahan commented on If you are way off in the deep end, there is only so much that tooling can do for you

Sat, 06 Feb 2010 14:28:53 GMT

That's why CQRS points to a different data structure for those queries :-)

Ayende Rahien commented on If you are way off in the deep end, there is only so much that tooling can do for you

Sat, 06 Feb 2010 10:24:12 GMT

Configurator, No, that is the problem that I am pointing out

configurator commented on If you are way off in the deep end, there is only so much that tooling can do for you

Sat, 06 Feb 2010 02:40:45 GMT

@tobi: Wow. I'm speechless. @Ayende: Am I right that the response here would be for 2 blogs, 2 posts each and 2 comments each blog1, blog1data, post1, post1data, comment1, comment1data blog1, blog1data, post1, post1data, comment2, comment2data blog1, blog1data, post2, post2data, comment1, comment1data blog1, blog1data, post2, post2data, comment2, comment2data blog2, blog2data, post1, post1data, comment1, comment1data blog2, blog2data, post1, post1data, comment2, comment2data blog2, blog2data, post2, post2data, comment1, comment1data blog2, blog2data, post2, post2data, comment2, comment2data And is this duplication of data what you mean when you say Cartesian product? Since this is blogs we're talking about, the data would be quite large. In fact, I think that even getting the post data with its comments would be a mistake, since the blog contents would be duplicated for each comment. Am I missing something here?

tobi commented on If you are way off in the deep end, there is only so much that tooling can do for you

Fri, 05 Feb 2010 17:38:15 GMT

The discussion about the "cartesian product" is not the point here. What is important to all of us is that Ayende has recognized this phenomenon as an important issue. @Ayende, you did not discuss an optimal solution to this problem. I propose: select b.id as id, 0 as tag ... from blogs b union all select p.blogid as id, 1 as tag, ... from posts p union all select c.blogid as blogid, 2 as tag, ... from comments c order by id, tag i learned this pattern in SQL Books Online as an example on how to construct a hierarchical XML result set.

JeroenH commented on If you are way off in the deep end, there is only so much that tooling can do for you

Fri, 05 Feb 2010 16:09:33 GMT

@Ayende, I have to second the other commenters (and your own acknowledgement) that you should avoid the term "Cartesian Product" in this case. It's confusing at first...

Mark Gellings commented on If you are way off in the deep end, there is only so much that tooling can do for you

Fri, 05 Feb 2010 15:56:31 GMT

The NHibernate SQL is not a Cartesian product. You are getting a flattened resultset combining the various tables. A Cartesian product from what I understand would be formed if there were no joins between the tables in which all possible combinations are produced.

Graeme Hill commented on If you are way off in the deep end, there is only so much that tooling can do for you

Fri, 05 Feb 2010 15:55:09 GMT

Yeah, I thought you would have to do a cross join to get a Cartesian product. So the problem is just that the result set generated by one query actually has more cells than the sum of the three record sets returned by separate queries? If that is the case then it begs the question: Which has more overhead, sending separate queries (will they be in a single round trip?) or just lumping it into one query and returning a little more data?

Ayende Rahien commented on If you are way off in the deep end, there is only so much that tooling can do for you

Fri, 05 Feb 2010 15:25:12 GMT

David, I am probably using Cartesian product term a bit losely. What I meant here is that you are going to lot a lot of information unnecessarily.

david commented on If you are way off in the deep end, there is only so much that tooling can do for you

Fri, 05 Feb 2010 15:19:50 GMT

From my understanding the SQL generated by NH is not a Cartesian product. Left outer joins do not generate Cartesian products like this. A Cartesian product would have the effect of returning number of blog entries * number of post entries * number of comment entries, the format would be like so: Select * --brevity from blog, post, comment thus omitting the join conditions, effectively using CROSS JOIN statements. What the NH Sql does do is populate the entire graph of objects, and while that is in most cases not the intent of what you want, it is not nearly as bad as a Cartesian product. I did not look in detail at what the EF generated as that is more than my poor brain can handle without running the SQL

Ayende Rahien commented on If you are way off in the deep end, there is only so much that tooling can do for you

Fri, 05 Feb 2010 13:00:36 GMT

Rafal, I am saying that you need to be aware of what is actually going on. And in general, you want to load a single collection per query.

Rafal commented on If you are way off in the deep end, there is only so much that tooling can do for you

Fri, 05 Feb 2010 12:56:30 GMT

Ayende, are you trying to say that using prefetch is harmful when loading a single master record with many dependencies? Let's see: prefetch is bad when loading a single record - as shown above. And it's equally bad when loading multiple records because you usually don't need collections related to main object (you are showing a list, not going into each record's details). So, prefetching is only useful if we have 1:1 mapping, or the number of related records is very low?

Frans Bouma commented on If you are way off in the deep end, there is only so much that tooling can do for you

Fri, 05 Feb 2010 12:24:25 GMT

LLBLGen Pro (using linq) var q = metaData.Customer .WithPath(p => p.Prefetch c.Orders) .SubPath(op => op.Prefetch(o => o.OrderDetails))); 3 queries: 1 for Customer, 1 for Order with a filter on customerid based on the query on customer, and 1 for orderdetails, with a filter on orderid based on the query on order. This tunes itself at runtime, if there are n Customers fetched and n is below a threshold it will replace the subquery filter on order with an IN predicate on order.CustomerID with the values for the PK values of the fetched customers. You can extend the Prefetch<> elements above with order by, filter, exclude fields etc. >

Thilak Nathen commented on If you are way off in the deep end, there is only so much that tooling can do for you

Fri, 05 Feb 2010 11:51:53 GMT

Your NH examples seem to always use the query API with HQL. While that is not the point of this post (or most of your other ones), it scares first time NH users, who may not know about the Criteria API or the new QueryOver stuff or the linq provider... and augments to the arguments of NH haters. This is especially so when you put NH and EF query syntaxes side by side in a single post... using the most verbose way to query NH and the most elegant way to query EF.

Ayende Rahien commented on If you are way off in the deep end, there is only so much that tooling can do for you

Fri, 05 Feb 2010 11:49:09 GMT

Liviu, Because the L2S version fails very frequently in anything but the most common cases.

liviu commented on If you are way off in the deep end, there is only so much that tooling can do for you

Fri, 05 Feb 2010 11:43:29 GMT

hi. you ignore the linq to sql generated statements which are THE MOST EFFICIENT. And do not require changes of semantics Why?