Just because the joining columns are indexed doesn't mean the query plan will be good. you are filtering on non-indexed columns using one of the slowest operators 'Like'. I would check the query execution plans. chances are the Like operator are causing inefficient queries. here is a crash course in understanding execution plans. http://itknowledgeexchange.techtarget.com/sql-server/back-to-basics-reading-an-execution-plan/
On Oct 9, 3:32 am, EricP <[email protected]> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
