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

I can't post the function but it is an ITVF that joins another ITVF onto a range of dates. I have linked the plans for both queries in the main post. — phosplait, Jun 11 '15 at 23:42

DeanOC · Answer 1 · 2015-06-11T23:51:52.780

3

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.

edited Jun 11 '15 at 23:51

answered Jun 11 '15 at 22:49

DeanOC

7,142
6
42
56

I looked into this and have played around with changing the parameter types. Nothing I did worked. Also just tried what you said and declared all of the variables on a line above, then set each parameter to the local var. This didn't change the execution plan either. – phosplait Jun 11 '15 at 23:25
@phosplait Just checking; when you say you *declared all of the variables on a line above, then set each parameter to the local var.* did you mean you changed your function in a similar manner to my (new) example? – DeanOC Jun 11 '15 at 23:53
Sorry, I understood it as I should declare them before the parameter call. Correct me if I'm wrong, but since it is an inline function, I would have to change it to a multi statement function to do this, which would likely change the execution plan anyway. – phosplait Jun 12 '15 at 00:01
Oh OK. I didn't realise it was an inline function. Then this won't help you unless, as you say, you rewrite to be multi-line. – DeanOC Jun 12 '15 at 00:06
My fault for not making that clear initially. I may give converting it a shot and report back. – phosplait Jun 12 '15 at 00:12
1

I had one parameter "report type" that changed the execution of my function. Masking this one parameter like this stopped Sql Server from using the original execution plan for both report types. Thank you! – Eddie Fletcher Jun 25 '15 at 23:27

TVF is much slower when using parameterized query

1 Answers1