Passing a date parameter to an in-line Table-Valued-Function is sloooww

Question

I'm having a strange scenario with the performance of a table valued function. Basically, I have an inline table-valued-function that takes a DATETIME as a parameter.

It sort of looks like this (not exactly this):

  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'm trying to investigate an issue where this query is running for >1 minute. It turns out that if I call the query this way:

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

It runs for > 1 minute.

However, if I call the query this way:

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

It runs in under a second. SQL Server is using entirely different explain plans for both calls.

Obviously, I want it to do the second method all the time, unfortunately, I'm calling this Table Valued Function through LINQ 2 SQL, which won't declare an interim variable.

Is there a way I can use an interim variable in the in-line table valued function? I don't really want to convert this to a multi-line table valued function. Other ideas would be welcome as well. I'm a little stumped.

Answer 1

I tried this with a large ammount of records and both ways returned the values in 9 seconds, no diference...

this is a long shot but can test to see if the implicit cast is giving the function the same date value as the explicit cast? try with a date like '2011/1/30' so you would have month/day conversion problems