NHibernate：使用值表进行优化和动态连接

My situation is next: there are to entities with many-to-many relation, f.e. Products and Categories. Also, categories has hierachial structure, like a tree. There is need to select all products that depends to some concrete category with all its childs (branch). So, I use following sql statement to do that:

我的情况是下一个:有多对多关系的实体,例如。产品和类别。此外,类别具有层次结构,如树。需要选择所有依赖于某些具体类别及其所有子项(分支)的产品。所以,我使用以下sql语句来做到这一点:

SELECT * FROM Products p
WHERE p.ID IN (
    SELECT DISTINCT pc.ProductID FROM ProductsCategories pc
    INNER JOIN Categories c ON c.ID = pc.CategoryID
    WHERE c.TLeft >= 1 AND c.TRight <= 33378
)

But with big set of data this query executes very long and I found some solution to optimize it, look at here:

但是对于大量数据,这个查询执行的时间很长,我找到了一些优化它的解决方案,请看这里:

DECLARE @CatProducts TABLE (
    ProductID int NOT NULL
)

INSERT INTO @CatProducts
    SELECT DISTINCT pc.ProductID FROM ProductsCategories pc
    INNER JOIN Categories c ON c.ID = pc.CategoryID
    WHERE c.TLeft >= 1 AND c.TRight <= 33378

SELECT * FROM Products p
INNER JOIN @CatProducts cp ON cp.ProductID = p.ID

This query executes very fast but I don't know how to do that with NHIbernate. Note, that I need use only ICriteria because of dynamic filtering\ordering. If some one knows a solution for that, it will be fantastic. But I'll pleasure to any suggestions of course.

此查询执行速度非常快,但我不知道如何使用NHIbernate执行此操作。请注意,由于动态过滤\排序,我只需要使用ICriteria。如果有人知道解决方案,那就太棒了。但我很乐意接受任何建议。

Thank you ahead, Kostya

谢谢你,克斯特亚

1 个解决方案

#1

Answer is simple - adding index to ProductsCategories table for ProductId column fixed the problem.

答案很简单 - 为ProductId列的ProductsCategories表添加索引修复了问题。