Passing a date parameter to the table-value-function string is sloooww

Question

I have a strange scenario with executing a function related to a table. Basically, I have a built-in table-value function that takes DATETIME as a parameter.

It looks like this (not quite so):

CREATE FUNCTION fn_MyFunction(@StartDate DATETIME) RETURNS TABLE AS RETURN ( SELECT COUNT(*), CustomerID, SUM(PAID) FROM Orders WHERE OrderDate > @StartDate GROUP BY CustomerID ) 

Now I am trying to investigate a problem in which this request has been running for> 1 minute. It turns out if I call the request this way:

 SELECT * FROM fn_MyFunction('7/1/2011') 

It lasts> 1 minute.

However, if I call the request this way:

 DECLARE @startDate DATETIME = '7/1/2011' SELECT * FROM fn_MyFunction(@startDate) 

He passes in a second. SQL Server uses completely different explanation plans for both calls.

Obviously, I want it to execute the second method all the time, unfortunately, I call this function with a table using LINQ 2 SQL, which will not declare an intermediate variable.

Is there a way that I can use an intermediate variable in an inline table function? I really don't want to convert this to a multi-valued table function. Other ideas are also welcome. I am a little puzzled.

Answer 1

I tried this with a lot of records and in both directions returned values ​​in 9 seconds, there is no difference ...

This is a long snapshot, but it can check if an implicit cast gives a function with the same date value as an explicit cast? try with a date like '2011/1/30' so that you have problems converting the month / day.

Answer 2

Adding the OPTION (RECOMPILE) option will fix your problem. I have the same problem with embedded TVF as follows:

This statement is executed in less than a second:

 select PropertyID from msa_GetPropertlyListWithNoMessages_TVF(DATEADD(hh, -2, Getdate())) 

This statement never ended after 5 hours:

 declare @msg_age as Datetime SET @msg_age = DATEADD(hh, -2, Getdate()) select PropertyID from msa_GetPropertlyListWithNoMessages_TVF(@msg_age) 

When adding the OPTION (RECOMPILE) parameter to the second call, the problem is fixed.

From what I can say, using the datetime parameter, somehow I get a completely different execution plan. I would be interested to know why.