1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Question Progress Procedure Taking Too Long Time To Complete.

Discussion in 'Development' started by somashekar, Sep 22, 2017.

  1. somashekar

    somashekar New Member

    Below is the code snippet,

    Here is code is to get last invoice for the part, this code is inside for each pt_mstr loop, so this code is executed for each part.

    Can anyone help me out to reduce its completion time.
    Code (progress):
    1.  
    2. for each prh_hist where prh_domain = global_domain AND prh_part = pt_part and
    3.                 prh_site = v_site,
    4.         each pvo_mstr  where pvo_mstr.pvo_domain = global_domain and (
    5.       pvo_lc_charge         = "" and
    6.       pvo_internal_ref_type = {&TYPE_POReceiver} and
    7.       pvo_internal_ref      = prh_receiver and
    8.       pvo_line              = prh_line     and
    9.       pvo_order             = prh_nbr AND
    10.       pvo_last_voucher > ""
    11.       ) no-lock,
    12.    each pvod_det no-lock where
    13.         pvod_det.pvod_domain = global_domain and
    14.         pvod_id = pvo_id,
    15.    each vph_hist  where vph_hist.vph_domain = global_domain and (  vph_pvo_id =
    16.    pvo_id
    17.       and vph_pvod_id_line = pvod_id_line
    18.       and vph_nbr = prh_nbr
    19.       and   vph_inv_cost <> 0
    20.       and   vph_ref =  pvo_last_voucher
    21.       ) use-index vph_nbr no-lock,
    22.         each vo_mstr no-lock where vo_domain = global_domain AND vo_ref = vph_ref,
    23.         each ap_mstr no-lock where ap_domain = global_domain AND ap_type = "VO" and
    24.                       ap_ref = vo_ref
    25.         break by ap_effdate descending:
    26.        v_inv_price_curr = vph_curr_amt.
    27.             v_curr = caps(vo_curr).
    28.        v_last_inv = vo_invoice.
    29.        v_supplier = ap_vend.
    30.        v_local = (if v_curr = "AUS" then "LOCAL" else "OVERSEAS").
    31.        find first ad_mstr where ad_domain = global_domain AND ad_addr = v_supplier no-lock no-error .
    32.             if avail ad_mstr then v_supplier_name = ad_name.
    33.  
    34.        leave.
    35.     end.
     
    Last edited by a moderator: Sep 25, 2017
  2.  
  3. tamhas

    tamhas ProgressTalk.com Sponsor

    It would help a lot if you put code tags around that so that the indenting was maintained. A slow query is almost always a question of bad indexing. The file names look like MFG/Pro, but I don't have the dictionary for that. A COMPILE XREF would show you what actual indexes are used and you could compare that to the dictionary to see if it matches what you expect.
     
  4. somashekar

    somashekar New Member

    Hi Tamhas,

    Thanks for your help. I tried formatting the code and put it below. Yes, filenames are from MFG/Pro. I will compile with XREF to see the indexes used in the program.

    for each prh_hist where prh_domain = global_domain AND
    prh_part = pt_part and
    prh_site = v_site,
    each pvo_mstr where pvo_mstr.pvo_domain = global_domain and
    (pvo_lc_charge = "" and
    pvo_internal_ref_type = {&TYPE_POReceiver} and
    pvo_internal_ref = prh_receiver and
    pvo_line = prh_line and
    pvo_order = prh_nbr AND
    pvo_last_voucher > "") no-lock,
    each pvod_det no-lock where
    pvod_det.pvod_domain = global_domain and
    pvod_id = pvo_id,
    each vph_hist where vph_hist.vph_domain = global_domain
    and (vph_pvo_id = pvo_id
    and vph_pvod_id_line = pvod_id_line
    and vph_nbr = prh_nbr
    and vph_inv_cost <> 0
    and vph_ref = pvo_last_voucher)
    use-index vph_nbr no-lock,
    each vo_mstr no-lock where vo_domain = global_domain
    AND vo_ref = vph_ref,
    each ap_mstr no-lock where ap_domain = global_domain
    AND ap_type = "VO"
    and ap_ref = vo_ref
    break by ap_effdate descending:
    v_inv_price_curr = vph_curr_amt.
    v_curr = caps(vo_curr).
    v_last_inv = vo_invoice.
    v_supplier = ap_vend.
    v_local = (if v_curr = "AUS" then "LOCAL" else "OVERSEAS").
    find first ad_mstr where ad_domain = global_domain
    AND ad_addr = v_supplier no-lock no-error .
    if avail ad_mstr then v_supplier_name = ad_name.

    leave.
    end.
     
  5. somashekar

    somashekar New Member

    Hi Tamhas,

    Used the proper indexes comparing the xref output and indexes of the filenames.

    Program completes really fast now.

    Thanks a lot.
     
  6. tamhas

    tamhas ProgressTalk.com Sponsor

    And now you have a tool for the next time.

    For formatting code, use the icon between media and drafts, select code, and then paste your code in the inside. Helps a lot.
     

Share This Page