TVF is much slower when using parameterized query

Question

I am trying to run an inline TVF as a raw parameterized SQL query.

When I run the following query in SSMS, it takes 2-3 seconds

select * from dbo.history('2/1/15','1/1/15','1/31/15',2,2021,default)

I was able to capture the following query through SQL profiler (parameterized, as generated by Entity framework) and run it in SSMS.

exec sp_executesql N'select * from dbo.history(@First,@DatedStart,@DatedEnd,@Number,@Year,default)',N'@First date,@DatedStart date,@DatedEnd date,@Maturity int,@Number decimal(10,5)',@First='2015-02-01',@DatedStart='2015-01-01',@DatedEnd='2015-01-31',@Year=2021,@Number=2

Running the above query in SSMS takes 1:08 which is around 30x longer than the non parameterized version.

I have tried adding option(recompile) to the end of the parameterized query, but it did absolutely nothing as far as performance. This is clearly an indexing issue to me, but I have no idea how to resolve it.

When looking at the execution plan, it appears that the parameterized version mostly gets mostly hung up on an Eager Spool (46%) and then a Clustered Index scan (30%) which are not present in the execution plan without parameters.

Perhaps there is something I am missing, can someone please point me in the right direction as to how I can get this parameterized query to work properly?

EDIT: Parameterized query execution plan, non-parameterized plan

Answer 1

Maybe it's a parameter sniffing problem.

Try modifying your function so that the parameters are set to local variables, and use the local vars in your SQL instead of the parameters.

So your function would have this structure

CREATE FUNCTION history(
       @First Date, 
       @DatedStart Date, 
       @DatedEnd Date, 
       @Maturity int, 
       @Number decimal(10,5))
RETURNS @table TABLE (
   --tabledef
) 
AS
BEGIN

   Declare @FirstVar Date = @First
   Declare @DatedStartVar Date = @DatedStart
   Declare @DatedEndVar Date = @DatedEnd
   Declare @MaturityVar int = @Maturity
   Declare @NumberVar decimal(10,5) = @Number

   --SQL Statement which uses the local 'Var' variables and not the parameters 

   RETURN;
END

;

I've had similar probs in the past where this has been the culprit, and mapping to local variables stops SQL Server from coming up with a dud execution plan.