UDF and SP what is difference

[Henn Sarv]

Hi SQLGurus, might someone explain me with simple words

I created on SQL UDF - quite complex - containing 3-level CTE inside - but single query (RETURNS TABLE AS RETURN(....)

and I created exact same as SP

comparision (I executed together both - select * from UDF and EXEC SP with same parameters goves me that SP runs 5 time quicker than UDF

What is tehnical difference of them and is there some way (SCHEMABINDING or Execute as or something else) to speed up the UDF

Create UDF with EXEC SP inside I've not yet tested :)

any comment are welcome
Henn Sarv

[#2]

One comment - both of them will be executed from EF over SQL - so on execute side both are equal. In EF both are treated as functions

[#3]

@Henn Sarv , with that less on informations no one can guess.

But I try: When you run first the UDF and then the SP, the SP benefits from the already cached data (buffer pool), so no big magic.

Compare the execution plan to see if there is a difference.

[#4]

I've tried in both direction and after several testings before - the data was cached for both

I can share the code - is this tells something

Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create proc GetNamesP(
@pref nvarchar(128),
@lcode nvarchar(10) = null, --, -- ''
@no_lcode nvarchar(10) = null, --, -- ''
@code0 nvarchar(5) = null, --, -- 'RA'
@code1 nvarchar(5) = null, --, -- 'MMM'
@no_code nvarchar(5) = null, --, -- ''
@year int = null , -- year(getdate()),
@date date = null -- getdate()
)
as

with LIN as (
select li.PersonId, p.eslid, p.fullname, li.FromDate, li.Departmentid, li.OrgId
, ROW_NUMBER() over (partition by p.id, Departmentid order by fromdate desc) nrin
from persons p
join DepartmentMembersIn LI on p.id = LI.PersonId
where 1=1
and DepartmentId not in (8,26)
and fullname like '%' + @pref + '%'
and FromDate <= coalesce(@date, datefromparts(@year,12,31), getdate())
), L as (
select LIN.*, lout.ToDate
, row_number() over (partition by lin.Departmentid, lin.personid order by todate) nrout
from LIN
left join DepartmentMembersOut lout
on LIN.PersonId = lout.PersonId
and LIN.Departmentid = coalesce(lout.DepartmentId, lin.Departmentid)
and LIN.OrgId = coalesce(lout.OrgId, lin.orgid)
where nrin = 1
), M as (
select * from L
where 1 = 1
and nrout = 1
), U as (
select M.PersonId, m.EslId, m.FullName, l.LCode, o.Code
from M
join Departments l on M.DepartmentId = l.id
join Organizations o on M.OrgId = o.id
where coalesce(ToDate, getdate()) >= coalesce(@date,datefromparts(@year,1,1), getdate())


), V as (
select * from U
where 1=1
and (coalesce(@lcode,'')='' or exists (select * from U U1 where u.personid = u1.PersonId and u1.LCode = @lcode))
and (coalesce(@no_lcode,'') ='' or not exists (select * from U U1 where u.personid = u1.PersonId and u1.LCode = @no_lcode))
and (coalesce(@code0,'') = '' or exists (select * from U U1 where u.personid = u1.PersonId and u1.Code = @code0))
and (coalesce(@code1,'') = '' or exists (select * from U U1 where u.personid = u1.PersonId and u1.Code = @code1))
and (coalesce(@no_code,'') = '' or not exists (select * from U U1 where u.personid = u1.PersonId and u1.Code = @no_code))

)
select * from V
pivot (min(LCode) for Code in (ESL, MMM, RAM, RA, RC, TR, CPR, ROS, CRY)) pvt