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