NHibernate Queryover-检索此对象加集合图时，是否有任何方法可以使nHibernate中的SQL更好? [英] NHibernate Queryover - Is there any way of getting better SQL out of nHibernate when retrieving this object plus collections graph?
问题描述
我们遇到一种情况，我们试图使用QueryOver为对象图检索几个层次的深度.因此，如果我们的顶层是ParentEntity，而我们的子级是ChildEntitysA，ChildEntitysB和ChildEntitysC，那么我们可以使用类似以下代码的方式来检索图.
We have a situation where we are trying to retrieve a couple of levels deep for an object graph using QueryOver. So if our top level is ParentEntity and our children are ChildEntitysA, ChildEntitysB and ChildEntitysC then we have something like the following code to retrieve our graph.
var query = session.QueryOver<ParentEntity>(() => pAlias).Where(() => pAlias.Id == key).Future<ParentEntity>();
var queryA = session.QueryOver<ParentEntity>(() => pAlias).Left.JoinAlias(x => x.ChildEntitysA, () => caAlias).Where(() => pAlias.Id == key).Future<ParentEntity>();
var queryB = session.QueryOver<ParentEntity>(() => pAlias).Left.JoinAlias(x => x.ChildEntitysB, () => cbAlias).Where(() => pAlias.Id == key).Future<ParentEntity>();
var queryC = session.QueryOver<ParentEntity>(() => pAlias).Left.JoinAlias(x => x.ChildEntitysC, () => ccAlias).Where(() => pAlias.Id == key).Future<ParentEntity>();
return query.ToList();
这将在一次对数据库的调用中生成4条SQL语句，并带回我们想要的内容.但是，它生成的SQL确实效率低下，因为所有子查询都将在SELECT语句中包含父级的所有列以及子实体的列，类似于:
This should generate 4 SQL statements in one single call to the database and brings back what we want. However, the SQL it generates does have some inefficiency as all of the child queries will contain in the SELECT statement all the columns of the parent as well as the child entity's columns, something akin to:
SELECT parent.col0, parent.col1, parent.col2, parent.col3, .... parent.col99
FROM parent WHERE .....
SELECT parent.col0, parent.col1, parent.col2, parent.col3, .... parent.col99, childA.col0, childA.col1, childA.col2 .... childA.col99
FROM parent LEFT OUTER JOIN childA ON ....
SELECT parent.col0, parent.col1, parent.col2, parent.col3, .... parent.col99, childB.col0, childB.col1, childB.col2 .... childB.col99
FROM parent LEFT OUTER JOIN childB ON ....
SELECT parent.col0, parent.col1, parent.col2, parent.col3, .... parent.col99, childC.col0, childC.col1, childC.col2 .... childC.col99
FROM parent LEFT OUTER JOIN childC ON ....
但是为父实体生成的查询已经包含所有必要的数据！如果我们返回父级的倍数，并且每个子级查询的数据量相应增加(尤其是所涉及的实体在基础列数方面非常大)，那么这确实会引起效率问题.
yet the query generated for the parent entity already contains all the necessary data! This does become a bit of an efficiency concern if we're returning multiples of the parent and the volume of data per child query rises accordingly (especially as the entities involved are quite large in terms of the number of underlying columns).
那么，有什么方法可以强制nHibernate不生成SQL来为查询的每个部分返回所有父实体的值，而仅将其限制为仅在SQL中返回最小键列? Queryover甚至是针对这种情况的最佳API吗?
So, is there any way we can force nHibernate to not generate SQL to return all the parent entity's values for every single part of the query and just limit it to returning minimal key columns only in the SQL? Is Queryover even the optimal API for this situation?
推荐答案
我建议的方式是在这些组合中充分利用NHibernate功能:
The way I would suggest, is to use full power of NHibernate features in these combinations:
- 第一个是复杂的查询.
- 第二种是从
lazy加载和batch-size设置中获利
- The first is a complex query.
- The second is to profit from
lazyloading andbatch-sizesetting
因此，在第一种情况下，我们将创建一个并且只有一个QueryOver (可能包括一些子查询)
So, in the first case we are about to create one and only one QueryOver (maybe including some subqueries)
它必须包含预测，这是必不可少的.它可以包含(许多)左，内部联接，可以被深层过滤...然后，我们将获得缩小的SELECT语句，其中仅包含必填字段.这将导致一个 SQL查询，该查询必须转换为某些(未映射) DTO对象
It must contain projections, that's essential. it can include (many) Left, Inner joins, can be deeply filtered... We will then get the narrowed SELECT statement, containing only required fields. This results in one SQL query, which must be converted into some (unmapped) DTO object
Projections 和 DTO 的意思是，最后我们必须使用ResultTransformer，它将所有即将出现的选定字段转换为DTO对象属性.
The Projections and DTO means, that at the end we have to use ResultTransformer, which will convert all coming selected fields into DTO object properties.
在第二种情况下，我们试图从懒惰行为和batch-size映射中获利.看起来可能像这样:
In the second case, we are trying to profit from lazy behaviour and batch-size mapping. It could look like this:
1)类/实体
<class name="ParentEntity" batch-size="25" lazy="true">
...
2)集合
<bag name="Children" lazy="true" batch-size="25"
...
请参阅文档中的更多信息: 19.1.5.使用批量提取
See more in documentation: 19.1.5. Using batch fetching
我们在这里获得的是，我们可以创建仅返回轻对象(已映射的对象)的软查询.这是第一个选择.然后将所有属性(多对一，一对多)批量加载-但仅在需要访问它们时才需要.
What we gain here is, that we can create soft queries returning only light objects (these which were mapped). This is the first Select. Then all the properties (many-to-one, one-to-many) are loaded in batches - but only if they are accessed, needed.
这导致多个SELECT子句，但是也没有像 1 + N 这样的SELECT子句膨胀.如果NHibernate会发现，则某些必需的属性已经在session中加载了......它将不再触发SELECT.
This leads to more than 1 SELECT clause, but also there is no inflation of SELECT clauses like 1 + N. If NHibernate will find out, that some of the required properties is already loaded in the session... it won't fire the SELECT any more.
摘要:两种方法都是正确的方法.尝试打个比方，找出在哪种情况下您可以从第一个或第二个中获得更多收益.但无论如何仍应使用lazy="true"和batch-size="25"的映射
Summary: both approaches are really the way. Try to play arround to find out in which situation you get more from the first or the second. BUT the mapping with lazy="true" and batch-size="25" should be used anyway
有关批量大小的更多链接:
Some more links about batch-size:
- How to Eager Load Associations without duplication in NHibernate?
- NHibernate QueryOver with Fetch resulting multiple sql queries and db hits
- Is this the right way to eager load child collections in NHibernate
这篇关于NHibernate Queryover-检索此对象加集合图时，是否有任何方法可以使nHibernate中的SQL更好?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
