Question Progress Procedure Taking Too Long Time To Complete.

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

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.
 

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.
 

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