Performance

[Former Member]

Can any one suggest performance tuning for this program?

It is taking long time to display the results. This is a batch program we have to run every night and check whole database. Especially it's taking long time with PERFORM get_doc_num_of_billing_doc when quering bsad table.

REPORT zpxv0_w_005

NO STANDARD PAGE HEADING

MESSAGE-ID zw

LINE-SIZE 170

LINE-COUNT 58 .

TABLES:

• zdepmgmt, "WRM Dependency Management

qmel , "Quality Notification

vbak , "Sales Document: Header Data

vbfa , "Sales Document Flow

vbuk , "Sales Document: Header Status and Administrative Data

vbrk , "Billing Document: Header Data

bsad , "Accounting: Secondary Index for Customers (Cleared Items)

bseg . "Accounting Document Segment

DATA: BEGIN OF iords OCCURS 0,

aufnr LIKE zdepmgmt-aufnr,

phasenum LIKE zdepmgmt-aufnr,

phase LIKE zdepmgmt-aufnr,

priority LIKE zdepmgmt-aufnr,

ustat LIKE zdepmgmt-aufnr,

qmnum(20),

• qmnum LIKE qmel-qmnum, "Notification Number

vbeln LIKE qmel-vbeln,

qmart LIKE qmel-qmart, "Notification Type

ernam LIKE qmel-ernam, "Name of Person who Created the Object

END OF iords.

*DATA : BEGIN OF del_tab OCCURS 0,

• bstnk(20),

*END OF del_tab.

DATA: BEGIN OF idp_ords OCCURS 0,

vbeln LIKE vbak-vbeln,

bstnk LIKE vbak-bstnk,

auart LIKE vbak-auart,

netwr LIKE vbak-netwr,

aufnr LIKE qmel-aufnr,

qmnum LIKE vbak-qmnum, "Notification Number

qmart LIKE qmel-qmart, "Notification Type

ernam LIKE qmel-ernam, "Name of Person who Created the Object

END OF idp_ords.

DATA: BEGIN OF ibl_doc OCCURS 0,

vbeln LIKE vbfa-vbeln,

END OF ibl_doc.

DATA: BEGIN OF icl_doc OCCURS 0,

vbeln LIKE vbrk-vbeln,

END OF icl_doc.

DATA : BEGIN OF inum_cl_doc OCCURS 0,

augbl LIKE bsad-augbl,

zuonr LIKE bsad-zuonr,

vbeln LIKE bsad-vbeln,

END OF inum_cl_doc.

DATA : BEGIN OF iamt_cld OCCURS 0,

vbeln LIKE bseg-vbeln,

wrbtr LIKE bseg-wrbtr,

END OF iamt_cld.

DATA : BEGIN OF ilist OCCURS 0,

• aufnr LIKE zdepmgmt-aufnr,

• phasenum LIKE zdepmgmt-aufnr,

• phase LIKE zdepmgmt-aufnr,

• priority LIKE zdepmgmt-aufnr,

• ustat LIKE zdepmgmt-aufnr,

qmnum(20),

• qmnum LIKE qmel-qmnum, "Notification Number

vbeln LIKE qmel-vbeln,

qmart LIKE qmel-qmart, "Notification Type

ernam LIKE qmel-ernam, "Name of Person who Created the Object

• vbeln LIKE vbak-vbeln,

bstnk LIKE vbak-bstnk,

auart LIKE vbak-auart,

netwr LIKE vbak-netwr,

• aufnr LIKE qmel-aufnr,

• qmnum LIKE vbak-qmnum, "Notification Number

• qmart LIKE qmel-qmart, "Notification Type

• ernam LIKE qmel-ernam, "Name of Person who Created the Object

END OF ilist.

START-OF-SELECTION.

• PERFORM get_pm_orders.

PERFORM get_dp_orders.

PERFORM get_bl_doc.

PERFORM get_doc_num_of_billing_doc .

PERFORM amount_cleared.

END-OF-SELECTION.

PERFORM list_output.

&----

---

*& Form get_pm_orders

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM get_pm_orders .

SELECT a~aufnr

a~phasenum

a~phase

a~priority

a~ustat

b~qmnum

b~qmart

b~ernam

INTO TABLE iords

FROM zdepmgmt AS a INNER JOIN qmel AS b

ON ( a_{aufnr = b}aufnr )

WHERE a_{aufnr = b}aufnr.

SORT iords BY qmnum .

LOOP AT iords.

SHIFT iords-qmnum LEFT DELETING LEADING '0'. MODIFY iords.

ENDLOOP.

ENDFORM. " get_pm_orders

&----

---

*& Form get_dp_orders

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM get_dp_orders .

*Then look up the deposit order (vbak-vbeln) using the notification number (qmel-qmnum) where qmel-qmnum = vbak-bstnk (Customer Purchase Order).

SELECT

vbeln

bstnk

auart

netwr

qmnum

FROM vbak

INTO TABLE idp_ords .

LOOP AT idp_ords.

SHIFT idp_ords-bstnk LEFT DELETING LEADING '0'. MODIFY idp_ords.

ENDLOOP.

LOOP AT idp_ords.

READ TABLE iords WITH KEY qmnum = idp_ords-bstnk

BINARY SEARCH.

IF sy-subrc EQ 0.

• MOVE iords-aufnr TO ilist-aufnr.

• MOVE iords-phasenum TO ilist-phasenum.

• MOVE iords-phase TO ilist-phase.

• MOVE iords-priority TO ilist-priority.

• MOVE iords-ustat TO ilist-ustat.

• MOVE iords-qmnum TO ilist-qmnum.

MOVE idp_ords-vbeln TO ilist-vbeln.

MOVE idp_ords-qmart TO ilist-qmart.

MOVE idp_ords-ernam TO ilist-ernam.

MOVE idp_ords-bstnk TO ilist-bstnk.

MOVE idp_ords-auart TO ilist-auart.

ENDIF.

IF ilist IS NOT INITIAL.

APPEND ilist.

CLEAR ilist.

ENDIF.

ENDLOOP.

SORT ilist BY vbeln .

ENDFORM. " get_dp_orders

&----

---

*& Form get_bl_doc

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM get_bl_doc .

SELECT vbeln FROM vbfa INTO TABLE ibl_doc .

SORT ibl_doc BY vbeln .

LOOP AT ilist.

READ TABLE ibl_doc WITH KEY vbeln = ilist-vbeln

BINARY SEARCH.

CHECK sy-subrc NE 0.

DELETE ilist.

ENDLOOP.

ENDFORM. " get_bl_orders

&----

---

*& Form get_doc_num_of_billing_doc

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM get_doc_num_of_billing_doc .

SELECT vbeln FROM vbrk INTO TABLE icl_doc.

SORT icl_doc BY vbeln .

LOOP AT ilist.

READ TABLE icl_doc WITH KEY vbeln = ilist-vbeln

BINARY SEARCH.

CHECK sy-subrc NE 0.

DELETE ilist.

ENDLOOP.

LOOP AT ilist.

SHIFT ilist-vbeln LEFT DELETING LEADING '0'. MODIFY ilist.

ENDLOOP.

SELECT augbl zuonr FROM bsad INTO TABLE inum_cl_doc.

• where vbeln ne ''.

DELETE inum_cl_doc WHERE vbeln = ''.

SORT inum_cl_doc BY zuonr.

LOOP AT inum_cl_doc.

SHIFT inum_cl_doc-zuonr LEFT DELETING LEADING '0'. MODIFY inum_cl_doc.

ENDLOOP.

LOOP AT ilist.

READ TABLE inum_cl_doc WITH KEY zuonr = ilist-vbeln

BINARY SEARCH.

IF sy-subrc EQ 0.

WRITE : 'BILLING DOCUMENT EXISTS IE BILLING DOCUMENT CLEARED'.

ENDIF.

ENDLOOP.

ENDFORM. " get_doc_num_of_billing_doc

&----

---

*& Form amount_cleared

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM amount_cleared .

SELECT vbeln wrbtr FROM bseg INTO TABLE iamt_cld .

SORT iamt_cld BY vbeln .

LOOP AT iamt_cld.

SHIFT iamt_cld-vbeln LEFT DELETING LEADING '0'. MODIFY iamt_cld.

ENDLOOP.

LOOP AT ilist.

READ TABLE iamt_cld WITH KEY vbeln = ilist-vbeln

BINARY SEARCH.

IF sy-subrc EQ 0 .

IF ilist-netwr EQ iamt_cld-wrbtr.

WRITE : 'Engineering Advance sucess'.

ELSE.

WRITE : 'Engineering Advance Error'.

ENDIF.

else.

write 😕 'nothing found'.

ENDIF.

ENDLOOP.

ENDFORM. " amount_cleared

&----

---

*& Form list_output

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM list_output .

LOOP AT iords.

WRITE 😕

iords-aufnr,

iords-phasenum,

iords-phase,

iords-priority,

iords-ustat,

iords-qmnum,

• iords-bstkd_m,

iords-qmart,

iords-ernam .

ENDLOOP.

SKIP. SKIP.

WRITE 😕 '<<<<<<<<<<<< idp_ords >>>>>>>>>>>>>'.

LOOP AT idp_ords.

IF idp_ords-bstnk = '9809'.

WRITE 😕 idp_ords-vbeln,

idp_ords-bstnk,

idp_ords-auart.

ENDIF.

ENDLOOP.

SKIP. SKIP.

WRITE 😕 '<<<<<<<<<<<< ilist >>>>>>>>>>>>>'.

LOOP AT ilist.

WRITE 😕 'ilist',

ilist-qmnum.

ENDLOOP.

ENDFORM. " list_output

[Former Member]

Instead of looping at ilist 3 times, u can loop only once:

SELECT vbeln FROM vbrk INTO TABLE icl_doc.

SORT icl_doc BY vbeln .

SELECT augbl zuonr FROM bsad INTO TABLE inum_cl_doc.

• where vbeln ne ''.

DELETE inum_cl_doc WHERE vbeln = ''.

SORT inum_cl_doc BY zuonr.

LOOP AT inum_cl_doc.

SHIFT inum_cl_doc-zuonr LEFT DELETING LEADING '0'. MODIFY inum_cl_doc.

ENDLOOP.

LOOP AT ilist.

READ TABLE icl_doc WITH KEY vbeln = ilist-vbeln

BINARY SEARCH.

<b>if sy-subrc = 0.

SHIFT ilist-vbeln LEFT DELETING LEADING '0'. MODIFY ilist.

READ TABLE inum_cl_doc WITH KEY zuonr = ilist-vbeln

BINARY SEARCH.

IF sy-subrc EQ 0.

WRITE : 'BILLING DOCUMENT EXISTS IE BILLING DOCUMENT CLEARED'.

ENDIF.

else.</b>

DELETE ilist.

endif.

ENDLOOP.

Regards,

Prakash.