There _are_ some places where you can modify the SQL, but it's usually
hacky.
Your solution is probably the best compromise.
Diego
On Mon, Nov 15, 2010 at 12:25, Michael Smith <[email protected]> wrote:
> 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]<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.
