Here is my scenario.
I have objects:
Product (ID, Name, SubCategories)
ProductSubCategory (ID, Name, Category)
ProductCategory (ID, Name, SubCategories)
I need to write a query to get the count of all the Products where
Product.Name or ProductCategory.Name matches specified keyword. Here
is what I got.
==========================
ICriterion criterion = Restrictions.Like("Name", this.Keyword,
MatchMode.Anywhere);
DetachedCriteria postList = DetachedCriteria.For(typeof
(ProductCategory))
.CreateAlias("SubCategories", "sc")
.CreateAlias("sc.Products", "p")
.SetProjection(Projections.Property("p.ID"))
.Add(Restrictions.Like("Name", this.Keyword, MatchMode.Anywhere));
criterion = Restrictions.Or(criterion, Subqueries.PropertyIn("ID",
postList));
_query.Add(criterion);
=========================
This results in Sql query (using NHProf):
============================
SELECT count(* ) as y0_
FROM Product this_
WHERE this_.ID in (SELECT p2_.ID as y0_
FROM ProductCategory this_0_
inner join ProductSubCategory sc1_
on this_0_.ID = sc1_.CategoryID
inner join ProductSubCategories
products5_
on sc1_.ID =
products5_.SubCategoryID
inner join Product p2_
on products5_.ProductID = p2_.ID
WHERE this_0_.Name like '%ed%' /* @p3
*/)
or this_.Name like '%ed%' /* @p0 */
============================
This query takes 15 secs. Table Product has 600,000 records, while
table Category only has 15.
If I only keep product name condition and get rid of Category.Name
check - the query goes down to 3 secs.
============================
SELECT count(* ) as y0_
FROM Product this_
WHERE this_.Name like '%ed%' /* @p0 */
============================
I have indexes on all the joining columns.
Is there some way this can be made faster?
With direct SQL - if I replace OR condition with UNION - the query
time goes down to 3 secs, but I would rather keep this in NHibernate.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en
-~----------~----~----~----~------~----~------~--~---
