sql server - Why UDF call with declared variables as parameters is ways faster than call with hardcoded parameters -

sql server - Why UDF call with declared variables as parameters is ways faster than call with hardcoded parameters -

i'm having following, in opinion, unusual problem. when phone call udf next way:

declare @contact_id uniqueidentifier declare @group_id uniqueidentifier set @group_id = 'ee57e2ad-204b-4078-afa4-11fa8375c2fd' set @contact_id = 'e6efcc9f-9d1c-4c38-a950-c45372f2a6d2' select count( id ) [countall] [document] [document] ([document].[id] in (select id [fs_document_view_ee57e2ad_204b_4078_afa4_11fa8375c2fd](@contact_id, @group_id)))

it runs 4s , next execution plan: fast

when phone call udf hard coded parameters this:

select count( id ) [countall] [document] [document] ([document].[id] in (select id [fs_document_view_ee57e2ad_204b_4078_afa4_11fa8375c2fd]('e6efcc9f-9d1c-4c38-a950-c45372f2a6d2', 'ee57e2ad-204b-4078-afa4-11fa8375c2fd')))

i execution plan: slow , takes 91s.

can explain me why happening?

the function calls 4 other nested functions passing them same parameters. it's related items view permissions.

thanks help in advance.

update

i used alternative 2 this article ivan g. mentioned.

the problem parameter sniffing , alternative 2 solved problem.

another method of resolving parameter sniffing issue disable parameter sniffing >altogether. not done switch or database option, can done >within script of stored procedure code. here illustration of how created >stored procedure parameter sniffing disabled:

drop proc [dbo].[displaybillinginfo] go create proc [dbo].[displaybillinginfo] @begindate datetime, @enddate datetime recompile declare @startdate datetime; declare @stopdate datetime; set @startdate = @begindate; set @stopdate = @enddate; select billingdate, billingamt billinginfo billingdate between @startdate , @stopdate;

to disable parameter sniffing, did alter way parameter values used within stored procedure. creating 2 different local variables (@startdate , @enddate) within procedure, setting variables passed parameters, , using local variables in between condition, able disable parameter sniffing. parameter sniffing disabled because optimizer not able identify parameters’ values in actual select statement. because sql server cannot tell parameter values used phone call stored procedure, optimizer creates generic plan based on statistics.

when execute stored procedure using code above, using either narrow range of dates or years’ worth of dates, compiled execution plan “index scan” operation. can tell parameter sniff turned off because know short range of dates have created index seek operation.

i believe due parametrization. first version of query parametrized, , sec 1 isn't. "queries parametrized requires less recompilation , dynamically built queries needs compilations , recompilation frequently" (source)

for version of query built parameters, execution plan created , reused: "if sql query has parameters, sql server creates execution plan tailored them improve performance, via process called 'parameter sniffing'. plan stored , reused since best execution plan" (source).

sql-server performance tsql user-defined-functions