• 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.

#1
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:
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.
 
Last edited by a moderator:

tamhas

ProgressTalk.com Sponsor
#2
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.
 
#3
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.
 

tamhas

ProgressTalk.com Sponsor
#5
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.