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].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
