NHibernate: Using value tables for optimization AND dynamic join

Question

Hi all,

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:

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.

Thank you ahead, Kostya

Answer 1

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