May 12, 2005 at 11:06 am
So I've got this batch of T-SQL that runs in Query Analyzer in about 3 seconds or so. When i put this exact same code into a UDF the time shoots up to over a minute. Has anyone ever heard of reduced performance when T-SQL is put into a UDF. By the way I also tried putting the code into a Stored Proc and having it insert into a temp table and I get the same reduction in performance. If anyone has any ideas I would appreciate it.
May 12, 2005 at 11:23 am
can you post the code you run from qa and the code form the udf please?
May 12, 2005 at 12:00 pm
Here's the function it's a little convoluted.
ALTER FUNCTION DDOverrideComp
(@From smalldatetime,
@To smalldatetime, @Region varchar(5))
RETURNS @table_var TABLE (tableid numeric(18,0) IDENTITY(1,1) primary key clustered,
[Personalid] varchar(40),
[Name] varchar(100),
[mga] varchar(40),
[Phone#] varchar(50),
[BizCode] varchar(5),
[policy] varchar(100),
[Premium] numeric(19,2),
[PBSOverride] numeric(19,2),
[DDOverride] numeric(19,2),
[AgentLevel] varchar(100),
[MGALevel] varchar(100),
[PolicyName] varchar(100),
[CompanyName] varchar(100),
[PolicyType] varchar(40))
AS
BEGIN
declare @StartDate as datetime
select @startdate = datestart from employees where dd = @region and department = 403 and isdd = 1
--Get c policy that the a statement date fall after the DD startdate
Declare @policies table (policy varchar(50) primary key)
insert @policies(policy) select distinct ppc.policy from (select * from paidproduction where life = 'c' and statementdate between @From and @To) ppc
inner join (SELECT * from paidproduction where life = 'a') ppa on ppc.policy = ppa.policy
group by ppc.policy,ppc.statementdate
having min(ppa.statementdate) >= @StartDate
--- Get DD personalid
--commented out by andye on 4/26/05
/*
declare @personalid varchar(50)
declare @emp varchar(50)
select @emp = [id] from employees where dd = @Region and department = 403
if @emp = '98139' begin
set @personalid = 'W59105'
end
if @emp = '98140' begin
set @personalid = '303733'
end
if @emp = '98141' begin
set @personalid = 'D80103'
end
if @emp = '98142' begin
set @personalid = '464467'
end
if @emp = '98159' begin
set @personalid = 'k178'
end
Declare @Org table (personalid varchar(50) primary key)
insert @Org(Personalid) select o1.personalid
from orglink o1
join orglink o2 on o2.orgid = o1.orgid
where o2.personalid = @personalid order by o1.display
*/
INSERT @table_var([Personalid],[Name],[mga], [Phone#],[BizCode],
[policy], [Premium],[PBSOverride],[DDOverride],[AgentLevel],
[MGALevel] ,[PolicyName],[CompanyName],[PolicyType])
select w.personalid, w.[Name], w.mga,w.phone1 as 'Phone#',p.life as 'BizCode',
p.policy, p.[premium],p.[premium] * (p.[override] / 100) as 'PBSOverride',
case when isnull(c.[level],'') like '%+%' or isnull(c2.[level],'') like '%+%' then .3
else .4 end * (p.[premium] * (p.[override] / 100)) as 'DDOverride',
c.[level] as 'AgentLevel',c2.[level] as 'MGAlevel',
pd.[planname] as 'PolicyName', n.[officialname] as 'CompanyName', pc.type as 'PolicyType'
from
(select * from workimp where region = @Region) w
inner join (select * from paidproduction where (life = 'a' or life = 'b' or policy in (select isnull(policy,'') from @policies)) and statementdate between @From and @To and renewal = 0) p on w.personalid = p.personalid
inner join product pd on pd.planname = p.[plan]
inner join productCodes pc on pc.[id] = pd.
inner join dbo.getnaictable() tpd on pd.naic = tpd.naic
left join (Select personalid,t.naic2 as NAIC,status,max([level]) as [level]
from contracts ce inner join dbo.getnaictable() t on ce.naic = t.naic group by personalid,t.naic2,status) c
on w.personalid = c.personalid and tpd.naic2 = c.naic and c.status = 'Active'
--table added by andye 2/21/05
left join (Select personalid,t.naic2 as NAIC,status,max([level]) as [level]
from contracts ce inner join dbo.getnaictable() t on ce.naic = t.naic group by personalid,t.naic2,status) c2
on isnull(w.mga,'') = c2.personalid and tpd.naic2 = c2.naic and c2.status = 'Active'
inner join company n on n.naic = pd.naic
where (isnull(c.[level],'') not like '%+ 2%' and isnull(c.[level],'') not like '%+ 4%' and isnull(c.[level],'') not like '%+ 3%' and isnull(c.[level],'') not like '%+ 5%' and isnull(c.[level],'') not like '%+ 6%')
and (isnull(c2.[level],'') not like '%+ 2%' and isnull(c2.[level],'') not like '%+ 4%' and isnull(c2.[level],'') not like '%+ 3%' and isnull(c2.[level],'') not like '%+ 5%' and isnull(c2.[level],'') not like '%+ 6%')
--Commented out by andye on 4/26/05
--and w.personalid not in (select personalid from @Org)
RETURN
END
And here's the batch
declare @From smalldatetime
declare @To smalldatetime
declare @Region varchar(5)
set @from = '4/1/05'
set @to = '4/30/05'
set @region = '11'
declare @table_var TABLE (tableid numeric(18,0) IDENTITY(1,1) primary key clustered,
[Personalid] varchar(40),
[Name] varchar(100),
[mga] varchar(40),
[Phone#] varchar(50),
[BizCode] varchar(5),
[policy] varchar(100),
[Premium] numeric(19,2),
[PBSOverride] numeric(19,2),
[DDOverride] numeric(19,2),
[AgentLevel] varchar(100),
[MGALevel] varchar(100),
[PolicyName] varchar(100),
[CompanyName] varchar(100),
[PolicyType] varchar(40))
declare @StartDate as datetime
select @startdate = datestart from ordnew.dbo.employees where dd = @region and department = 403 and isdd = 1
Declare @policies table (policy varchar(50) primary key)
insert @policies(policy) select distinct ppc.policy from (select * from ordnew.dbo.paidproduction where life = 'c' and statementdate between @From and @To) ppc
inner join (SELECT * from paidproduction where life = 'a') ppa on ppc.policy = ppa.policy
group by ppc.policy,ppc.statementdate
having min(ppa.statementdate) >= @StartDate
INSERT @table_var ([Personalid],[Name],[mga], [Phone#],[BizCode],
[policy], [Premium],[PBSOverride],[DDOverride],[AgentLevel],
[MGALevel] ,[PolicyName],[CompanyName],[PolicyType])
select w.personalid, w.[Name], w.mga,w.phone1 as 'Phone#',p.life as 'BizCode',
p.policy, p.[premium],p.[premium] * (p.[override] / 100) as 'PBSOverride',
case when isnull(c.[level],'') like '%+%' or isnull(c2.[level],'') like '%+%' then .3
else .4 end * (p.[premium] * (p.[override] / 100)) as 'DDOverride',
c.[level] as 'AgentLevel',c2.[level] as 'MGAlevel',
pd.[planname] as 'PolicyName', n.[officialname] as 'CompanyName', pc.type as 'PolicyType'
from
(select * from workimp where region = @Region) w
inner join (select * from paidproduction where (life = 'a' or life = 'b' or policy in (select isnull(policy,'') from @policies)) and statementdate between @From and @To and renewal = 0) p on w.personalid = p.personalid
inner join product pd on pd.planname = p.[plan]
inner join productCodes pc on pc.[id] = pd.
inner join dbo.getnaictable() tpd on pd.naic = tpd.naic
left join (Select personalid,t.naic2 as NAIC,status,max([level]) as [level]
from contracts ce inner join dbo.getnaictable() t on ce.naic = t.naic group by personalid,t.naic2,status) c
on w.personalid = c.personalid and tpd.naic2 = c.naic and c.status = 'Active'
left join (Select personalid,t.naic2 as NAIC,status,max([level]) as [level]
from contracts ce inner join dbo.getnaictable() t on ce.naic = t.naic group by personalid,t.naic2,status) c2
on isnull(w.mga,'') = c2.personalid and tpd.naic2 = c2.naic and c2.status = 'Active'
inner join company n on n.naic = pd.naic
where (isnull(c.[level],'') not like '%+ 2%' and isnull(c.[level],'') not like '%+ 4%' and isnull(c.[level],'') not like '%+ 3%' and isnull(c.[level],'') not like '%+ 5%' and isnull(c.[level],'') not like '%+ 6%')
and (isnull(c2.[level],'') not like '%+ 2%' and isnull(c2.[level],'') not like '%+ 4%' and isnull(c2.[level],'') not like '%+ 3%' and isnull(c2.[level],'') not like '%+ 5%' and isnull(c2.[level],'') not like '%+ 6%')
select * from @table_var
It's pretty scary but I don't see anything that would result in the behavior I described. In fact I know of nothing that could result in the behavior I described.
May 12, 2005 at 12:34 pm
Have you tried recompiling the udf b4 rerunning the code??
Might be a case of parameter sniffing...
May 12, 2005 at 12:49 pm
How do you recompile a function?
I've tried dropping and recreating it but still no change.
Also I've tried DBCC Freeproccache to remove the plan out of memory but still no improvement.
May 12, 2005 at 12:56 pm
can you compare both execution plan to find where it is different?
May 12, 2005 at 1:04 pm
I looked at that. The problem is when you look at the execution plan for a function it doesn't go into the internal details of the funtion. There are 2 steps. A clustered index scan and a select. When I look at the execution plan of the batch I see all the steps. I can't compare them.
May 12, 2005 at 1:08 pm
Can you convert the function to a stored proc while you debug this?
May 12, 2005 at 2:24 pm
Okay. I've got the execution plans and I'm going to go through them in detail. Something I'm noticing off the bat is that the batch uses Hash Joins where as the proc uses nested loops. The plans are very different. I'll start putting some hints in the proc to see if that gets me closer to the batch's plan.
May 12, 2005 at 2:45 pm
that's one way to go...
Try searching these forums for parameter sniffing... this might also be what's causing the headache here.
May 12, 2005 at 3:14 pm
Awesome. I created local variables to hold the values passed to the parameters. Then used the local variables inside the function rather the parameters. Now it runs just as fast as the batch.
ALTER FUNCTION DDOverrideComp
(@Froma smalldatetime,
@Toa smalldatetime, @Regiona varchar(5))
RETURNS @table_var ...
AS
BEGIN
declare @from smalldatetime
declare @to smalldatetime
declare @region varchar(5)
set @from = @froma
set @to = @toa
set @region = @regiona
Thanks for your help
May 12, 2005 at 3:18 pm
HTH
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply