Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2005
    Posts
    71

    Unanswered: 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
    GO

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Post 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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2005
    Posts
    71
    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 ?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    May 2005
    Posts
    48

    Solution.

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •