Any ideas on how to speed up this sp?

it is working but takes about 3-4 seconds per exec.

CREATE PROCEDURE isp_ap_calc_apt_totals
@.p_comp char(2),
@.p_vend char(6),
@.p_asofdate char(8)
as

if (@.p_asofdate <= '00000000')
begin
set @.p_asofdate = '99999999'
end

delete from XAPAPTTOT
where xapt_comp = @.p_comp and xapt_vend = @.p_vend and xapt_asof_date = @.p_asofdate

insert into XAPAPTTOT
select apph_comp, apph_vend, apph_type, apph_id, @.p_asofdate,
sum(apph_paymnts),
sum(apph_discts),
sum(apph_adjts),
count(apph_paymnts),
sum(apph_paymnts)+ sum(apph_discts) + sum(apph_adjts) +
b.apt_gross,
0,
max(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0'))
from APPHISTF.a join APTRANF.b on b.apt_comp = a.apph_comp and b.apt_vend = a.apph_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id
where ((a.apph_comp = @.p_comp) and (a.apph_vend = @.p_vend) and (a.apph_unpost_dt = 0)
and (str(a.yy,4) + replace(str(a.mm,2),' ','0') + replace(str(a.dd,2),' ','0') <= @.p_asofdate))
or ((a.apph_unpost_dt > 0 and a.apph_unpost_dt <= @.p_asofdate and b.apt_unposted_fg = 1 and b.apt_comp = @.p_comp and b.apt_vend = @.p_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id))
or (((str(a.yy,4) + replace(str(a.mm,2),' ','0') + replace(str(a.dd,2),' ','0') <= @.p_asofdate) and a.apph_unpost_dt > @.p_asofdate and b.apt_comp = @.p_comp and b.apt_vend = @.p_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id))
group by apph_comp, apph_vend, apph_type, apph_id

update XAPAPTTOT
set xapt_last_payck =
(select max(apph_payck) from APPHISTF
where apph_comp = xapt_comp and apph_vend = xapt_vend and apph_type = xapt_type
and apph_id = xapt_id
and str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') = xapt_last_paydt )
where xapt_comp = @.p_comp and xapt_vend = @.p_vend and xapt_asof_date = @.p_asofdate
GOPost the DDL and the indexes for the tables. Read the hint sticky at the top of the forum. Sample data might help as well, but what kind of volume are we talking about?|||the volume varies(multiple customers)
1,000-10,000 APTRANF and APPHISTF records
the Relationship between the tables is for each APTRANF record
you can have 0 to 9999 APPHISTF records.(generally only 0 or 1)
in unusually instances the APPHISTF might have 2-5 records.
the APPHISTF is a payment history(detail) to the APTRANF(master)
we allow unposting of a payment (apph_unpost_dt) and reissueing a new payment.
not sure about DDL ?|||Read this link here

http://www.dbforums.com/t1196943.html|||Hi,
Since I don't have any idea of your table structures and indexes thereon, I would go with eliminating redundencies in your code to reduce time. Several of the conditions and calculations are repeated and have now been changed to occur once. The code is given below. Hope this helps:

CREATE PROCEDURE isp_ap_calc_apt_totals
@.p_comp char(2),
@.p_vend char(6),
@.p_asofdate char(8)
as

if (@.p_asofdate <= '00000000')
set @.p_asofdate = '99999999'

delete from XAPAPTTOT
where xapt_comp = @.p_comp
and xapt_vend = @.p_vend
and xapt_asof_date = @.p_asofdate

insert into XAPAPTTOT
select apph_comp
,apph_vend
,apph_type
,apph_id
,@.p_asofdate
,sum(apph_paymnts)
,sum(apph_discts)
,sum(apph_adjts)
,count(apph_paymnts)
,sum(apph_paymnts)+ sum(apph_discts) + sum(apph_adjts) + b.apt_gross
,0
,max(str_1)
from (select apph_comp
,apph_vend
,apph_type
,apph_id
,apph_paymnts
,apph_discts
,apph_adjts
,apph_paymnts
,apph_unpost_dt
,str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') str_1
from APPHISTF
where apph_comp = @.p_comp
and apph_vend = @.p_vend) a

inner join

(select apt_gross
,apt_type
,apt_id
,apt_unposted_fg
from APTRANF
where bapt_comp = @.p_comp
and apt_vend = @.p_vend) b

on ( b.apt_type = a.apph_type
and b.apt_id = a.apph_id)

where (a.apph_unpost_dt = 0
and a.str_1 <= @.p_asofdate)
or (a.apph_unpost_dt > 0
and a.apph_unpost_dt <= @.p_asofdate
and b.apt_unposted_fg = 1)
or (a.str_1 <= @.p_asofdate
and a.apph_unpost_dt > @.p_asofdate)

group by apph_comp, apph_vend, apph_type, apph_id

update XAPAPTTOT
set xapt_last_payck = (select max(apph_payck) from APPHISTF
where apph_comp = xapt_comp
and apph_vend = xapt_vend
and apph_type = xapt_type
and apph_id = xapt_id
and str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') = xapt_last_paydt )
where xapt_comp = @.p_comp and xapt_vend = @.p_vend and xapt_asof_date = @.p_asofdate
GO


--Scalability Experts.