Forum Discussion
UDF and SP what is difference
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 exac...
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
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.
- 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
