如何使用Nhibernate通过递归自联接SQL获得最佳性能 [英] How to get optimal performance with recursive self join SQL using nhibernate

问题描述

我有一个带有parentId的团队表/实体，parentID是一个自我加入到同一表中的ID.

I have a Team table /entity with a parentId which is an id that self joined into the same table.

有一个名为TopUnit的属性，它将递归地继续调用.Parent，直到在Team实体上找到一个名为"IsTopUnit"的属性

There is a property called TopUnit that will recursizely keep calling .Parent until it finds an attribute on the Team entity called "IsTopUnit"

使用nhibernate探查器现在会引起Select N + 1警报.无论如何，有什么方法可以优化本质上是递归大小的自联接查询，从而避免Select N +1行为.

using the nhibernate profiler this is now causing an Select N + 1 alert. is there anyway to optimize what is essentially a recursize self join query to avoid the Select N + 1 behavior.

batchsize似乎适用于子集合，但在这种情况下它似乎无济于事，因为它全部是同步的"，因为我无法执行recursize SQL语句.

batchsize seems to work for child collections but it doesn't seems to help in this case as its all "syncronous" as i can't do a recursize SQL statement.

我想这个问题在nhibernate之外也是有效的.在SQL中执行递归语句的最佳方法是什么.看来您必须将其分解为多个查询.

I guess this question is valid outside of nhibernate as well. What is the best way to do recursive statements in SQL. it seems like you would have to break it up into multiple queries.

推荐答案

您可以尝试以下 查看全文