So, I guess the answer is: "It is not possible to join with a parameterized function or specify specific join hints without manually constructing the entire SQL using CreateSQLQuery". That's what I assumed, but I know there are lot of hooks into NHibernate that I have yet to learn, so I wanted to make sure.
We were able to re-write the function call into a single SQL query and add that into the criteria using criteria.Add(Expression.Sql(...)) and achieved the appropriate execution plan and performance. That saves us from needing to manually construct the rest of the SQL query. It's quite a bit messy for just the expression, but still considerably better than having to construct the whole query using CreateSQLQuery. Regards, Michael On Sun, Nov 14, 2010 at 8:26 AM, Diego Mijelshon <[email protected]>wrote: > If you need a specific SQL, the best way to use it is with CreateSQLQuery. > There's a whole chapter dedicated to that: > http://nhforge.org/doc/nh/en/index.html#querysql > > Diego > > > On Mon, Nov 8, 2010 at 19:22, Michael Smith <[email protected]>wrote: > >> I'm using the Criteria API to build up a highly variable query. This is >> working great for returning the proper results based on the inputs, but in >> some circumstances, performance is abysmal. Our DBA is recommending a >> specific query optimization that addresses these issues, but I am unable to >> determine how to translate this into NHibernate query code. >> >> The simplest query that demonstrates the problem is created using >> something like the following usage of the Criteria API: >> >> int inputX = ...; >> string inputY = ...; >> >> DetachedCriteria criteria = DetachedCriteria.For(typeof(MappedEntity)); >> >> SqlString sql = new SqlString >> ("{alias}.ID in (select ID FROM [dbo].[UserDefinedFunction](", >> Parameter.Placeholder, "))"); >> criteria.Add(Expression.Sql(sql, inputX, NHibernateUtil.Int32)); >> >> SqlString sql = new SqlString >> ("{alias}.ProjectId in (", >> "SELECT [Key] FROM CONTAINSTABLE(dbo.MappedEntity,*,", >> Parameter.Placeholder, >> "))"); >> criteria.Add(Expression.Sql(sql, inputY, NHibernateUtil.String)); >> >> >> >> This translates to the following SQL: >> >> SELECT ... >> FROM dbo.MappedEntity this_ >> WHERE this_.ID in ( >> SELECT ID >> FROM [dbo].[UserDefinedFunction](@p0) >> ) >> and this_.ID in ( >> SELECT [Key] >> FROM CONTAINSTABLE(dbo.MappedEntity,*,@p1) >> ) >> >> >> That SQL is exactly equivalent to the following with regards to the >> generated query plan and performance: >> >> SELECT ... >> FROM dbo.MappedEntity this_ >> INNER JOIN [dbo].[UserDefinedFunction](@p0) udf ON udf.ID = this_.ID >> WHERE this_.ID in ( >> SELECT [Key] >> FROM CONTAINSTABLE(dbo.MappedEntity,*,@p1) >> ) >> >> >> >> The performance issue is addressed by adding a "merge" join hint: >> >> SELECT ... >> FROM dbo.MappedEntity this_ >> INNER *MERGE *JOIN [dbo].[UserDefinedFunction](@p0) udf >> ON udf.ID = this_.ID >> WHERE this_.ID in ( >> SELECT [Key] >> FROM CONTAINSTABLE(dbo.MappedEntity,*,@p1) >> ) >> >> >> First off, is there any way to map a parameterized user-defined function >> such that we could even join with it (rather than having it in the where >> clause like we do now)? >> >> Secondly, if there is such a way, is there some method of providing join >> hints in the underlying sql? >> >> Any alternative suggestions are also welcome. >> >> Regards, >> Michael >> >> P.S. Note: the performance issues seem to be caused by SQL Server's >> query analyzer assuming that the user defined function returns a single row >> (since it can't maintain statistics on the temporary return table to know >> otherwise) and thus uses a nested loop to combine it with the additional >> restrictions. Since there are thousands of matches rather than one, a merge >> join is much better than the nested loop. We're simultaneously looking at >> ways to re-write the table-valued function to avoid the situation to begin >> with, but the odds of that are very small). >> >> -- >> 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]<nhusers%[email protected]> >> . >> For more options, visit this group at >> http://groups.google.com/group/nhusers?hl=en. >> > > -- > 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]<nhusers%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/nhusers?hl=en. > -- 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.
