topic Re: REGARDING PERFORMANCE in Application Development and Automation Discussions

REGARDING PERFORMANCE

Former Member — Fri, 06 Jul 2007 08:40:28 GMT

Hello

I have an report i have to chk for the performance tuning i have checked for the index and secondary feilds evry thing is ok and the final table is sorted and binary search has been done.wht should i do to increse the performance of this report

points for sure

Re: REGARDING PERFORMANCE

Former Member — Fri, 06 Jul 2007 08:41:30 GMT

Hi,

There are many things to check like checking the select queries. If you can paste your code then we can suggest.

Regards,

Atish

Re: REGARDING PERFORMANCE

Former Member — Fri, 06 Jul 2007 08:50:36 GMT

SELECT-OPTIONS AUFNR FOR AFIH-AUFNR.

SELECT-OPTIONS EQUNR FOR AFIH-EQUNR.

SELECT-OPTIONS ILART FOR AFIH-ILART.

SELECT-OPTIONS INGPR FOR AFIH-INGPR MEMORY ID IHG.

SELECT-OPTIONS IWERK FOR AFIH-IWERK.

SELECT-OPTIONS IPHAS FOR AFIH-IPHAS DEFAULT '2' OPTION LE SIGN I

NO-DISPLAY.

SELECT-OPTIONS GSTRP FOR AFKO-GSTRP DEFAULT '19990101' TO SY-DATUM

NO-DISPLAY.

SELECT-OPTIONS AUART FOR AUFK-AUART.

SELECT-OPTIONS KTEXT FOR AUFK-KTEXT.

SELECT-OPTIONS SWERK FOR ILOA-SWERK OBLIGATORY MEMORY ID SWK.

SELECT-OPTIONS TPLNR FOR ILOA-TPLNR.

SELECT-OPTIONS STORT FOR ILOA-STORT.

PARAMETERS : P_ARBPL LIKE CRHD-ARBPL MATCHCODE OBJECT CRAM.

SELECTION-SCREEN: END OF BLOCK PROG.

SELECT AUFNR EQUNR ILART ILOAN INGPR IWERK GEWRK

FROM AFIH

INTO CORRESPONDING FIELDS OF TABLE ITAB

WHERE AUFNR IN AUFNR

AND EQUNR IN EQUNR

AND ILART IN ILART

AND IPHAS IN IPHAS

AND INGPR IN INGPR

AND IWERK IN IWERK.

IF SY-SUBRC NE 0.

MESSAGE I000(YW) WITH ' No Data Found For The Selection'.

STOP.

ENDIF.

SELECT AUFNR AUART KTEXT OBJNR

INTO TABLE I_AUFK

FROM AUFK

FOR ALL ENTRIES IN ITAB

WHERE AUFNR = ITAB-AUFNR

AND AUART IN AUART

AND KTEXT IN KTEXT.

IF SY-SUBRC EQ 0.

SORT I_AUFK BY AUFNR.

ENDIF.

SELECT ILOAN SWERK TPLNR STORT

FROM ILOA

INTO TABLE I_ILOA

FOR ALL ENTRIES IN ITAB

WHERE ILOAN = ITAB-ILOAN

AND SWERK IN SWERK

AND TPLNR IN TPLNR

AND STORT IN STORT.

IF SY-SUBRC EQ 0.

SORT I_ILOA BY ILOAN.

ENDIF.

SELECT AUFNR GSTRP

FROM AFKO

INTO TABLE I_AFKO

FOR ALL ENTRIES IN ITAB

WHERE AUFNR = ITAB-AUFNR

AND GSTRP IN GSTRP.

IF SY-SUBRC EQ 0.

SORT I_AFKO BY AUFNR.

ENDIF.

IF NOT P_ARBPL IS INITIAL.

SELECT OBJID WERKS ARBPL

FROM CRHD INTO TABLE IT_CRHD

WHERE WERKS IN SWERK

AND ARBPL = P_ARBPL.

LOOP AT IT_CRHD.

R_OBJID-SIGN = 'I'.

R_OBJID-OPTION = 'EQ'.

R_OBJID-LOW = IT_CRHD-OBJID.

APPEND R_OBJID.

ENDLOOP.

ENDIF.

DELETE ITAB WHERE NOT GEWRK IN R_OBJID.

LOOP AT ITAB.

CLEAR: I_AUFK, I_ILOA, I_AFKO.

READ TABLE I_AUFK

WITH KEY AUFNR = ITAB-AUFNR

BINARY SEARCH.

IF SY-SUBRC = 0.

ITAB-AUART = I_AUFK-AUART.

ITAB-KTEXT = I_AUFK-KTEXT.

ITAB-OBJNR = I_AUFK-OBJNR.

MODIFY ITAB.

ELSE.

DELETE ITAB .

CONTINUE.

ENDIF.

READ TABLE I_ILOA

WITH KEY ILOAN = ITAB-ILOAN

BINARY SEARCH.

IF SY-SUBRC = 0.

ITAB-SWERK = I_ILOA-SWERK.

ITAB-TPLNR = I_ILOA-TPLNR.

ITAB-STORT = I_ILOA-STORT.

MODIFY ITAB.

ELSE.

DELETE ITAB .

CONTINUE.

ENDIF.

READ TABLE I_AFKO

WITH KEY AUFNR = ITAB-AUFNR

BINARY SEARCH.

IF SY-SUBRC = 0.

ITAB-GSTRP = I_AFKO-GSTRP .

MODIFY ITAB.

ELSE.

DELETE ITAB .

CONTINUE.

ENDIF.

ENDLOOP.

LOOP AT ITAB.

W2 = SY-DATUM - ITAB-GSTRP .

CLEAR W7 .

IF W2 >= 61 AND W2 < 91 .

W7 = 1 .

ENDIF.

CLEAR W8 .

IF W2 >= 91 AND W2 < 181 .

W8 = 1 .

ENDIF.

CLEAR W9 .

IF W2 >= 181 AND W2 < 366 .

W9 = 1 .

ENDIF.

CLEAR W10 .

IF W2 >= 366 .

W10 = 1 .

ENDIF.

CLEAR W6 .

IF W2 >= 31 AND W2 < 61 .

W6 = 1 .

ENDIF.

CLEAR W5 .

IF W2 >= 15 AND W2 < 31 .

W5 = 1 .

ENDIF.

CLEAR W4 .

IF W2 >= 8 AND W2 < 15 .

W4 = 1 .

ENDIF.

CLEAR W3 .

IF W2 < 8 .

W3 = 1 .

ENDIF.

ITAB-W3 = W3.

ITAB-W4 = W4.

ITAB-W5 = W5.

ITAB-W6 = W6.

ITAB-W7 = W7.

ITAB-W8 = W8.

ITAB-W9 = W9.

ITAB-W10 = W10.

MODIFY ITAB.

LOOP AT T02 WHERE SWERK1 = ITAB-SWERK AND INGPR1 = ITAB-INGPR

AND STORT1 = ITAB-STORT.

T02-W3 = T02-W3 + W3.

T02-W4 = T02-W4 + W4.

T02-W5 = T02-W5 + W5.

T02-W6 = T02-W6 + W6.

T02-W7 = T02-W7 + W7.

T02-W8 = T02-W8 + W8.

T02-W9 = T02-W9 + W9.

T02-W10 = T02-W10 + W10.

MODIFY T02.

ENDLOOP.

IF SY-SUBRC NE 0.

T02-SWERK1 = ITAB-SWERK.

T02-INGPR1 = ITAB-INGPR.

T02-STORT1 = ITAB-STORT.

T02-W3 = W3.

T02-W4 = W4.

T02-W5 = W5.

T02-W6 = W6.

T02-W7 = W7.

T02-W8 = W8.

T02-W9 = W9.

T02-W10 = W10.

APPEND T02.

ENDIF.

ENDLOOP.

Re: REGARDING PERFORMANCE

Former Member — Fri, 06 Jul 2007 08:52:15 GMT

are there any other things to chk

Re: REGARDING PERFORMANCE

seshatalpasai_madala — Fri, 06 Jul 2007 08:55:19 GMT

Hi,

Since you have lot of MODIFY and DELETE statements on the Internal tables my suggestion would be to use FILED-SYMBOLS in the statements.

This will increase the performance since you will avoid the unwanted COPY operation involved in MODIFY(Moving from Headerline to table etc).

Regards,

Sesh

Re: REGARDING PERFORMANCE

Former Member — Fri, 06 Jul 2007 09:01:01 GMT

Hi,

No i don't think there is anything left to check but few suggestions

Make sure you are selecting all the key fields from all the table.

See if it is possible to avaoid two time LOOP AT ITAB.

Also when you use DELETE and MODIFY use INDEX it will improve the performance considerably.

Reward points if useful.

Regards,

Atish

Re: REGARDING PERFORMANCE

Former Member — Fri, 06 Jul 2007 09:03:12 GMT

For the performance tuning of any Report following are some useful points:-

1) The run-time analysis tool (Transaction SE30) can be used to evaluate the hit lists of the top CPU consumers, table accesses, and to get a general idea of the coding run during the program execution.

2) SELECT INTO preferred to SELECT APPEND ENDSELECT

When an internal table needs to be created directly from one database table, the SELECT INTO is used to fill the internal table. It is faster to use the INTO TABLE version of a SELECT statement than to use APPEND statements.

3) Avoid SELECT… ORDER BY. Put records into an internal table and SORT is preferred.

4) SELECT FOR ALL ENTRIES used outside of loops, with a READ used inside the loop.

5)The ordering of the WHERE statements match the arrangement of the keys in the table records.

6) Fields compared in the WHERE clause of SELECT statements have similar types.

7) Whenever possible, the full table key is specified and SELECT SINGLE is specified.

😎 SELECT only the required fields from a table. Avoid using SELECT *.

9) When a full key is unknown and only one record is needed, use UP TO 1 ROWS in the SELECT statement.

10) Always check that the internal table is not empty before using it in the SELECT …… FOR ALL ENTRIES IN … …

11) Avoid using nested SELECTs. Use data dictionary VIEW, Inner-Joins or Left Outer Joins if possible.

12) Avoid using SELECTs inside a LOOP. Select all required data into internal table(s) first and do the processing.

13) Specify the key fields when performing the READ operations.

14) When performing direct reads, the BINARY SEARCH is used only when the internal table contains more than 500 rows and must be sorted by the key fields.

15) SORT ITAB ORDER BY is preferred over SORT ITAB.

16) A number other than zero is only specified for the OCCURS parameter if the required storage is less than 8KB.

Reward points if useful.

regards,

Monica

Re: REGARDING PERFORMANCE

Former Member — Fri, 06 Jul 2007 10:28:58 GMT

Use the view CAUFV instead of the tables AFKO and AUFK.

Move the assignment of fixed values outside of your loops. Thus:

R_OBJID-SIGN = 'I'.
R_OBJID-OPTION = 'EQ'.
LOOP AT IT_CRHD.
  R_OBJID-LOW = IT_CRHD-OBJID.
  APPEND R_OBJID.
ENDLOOP.

Why have you got 2 loops on ITAB? 1 loop will allow the correct processing to be done.

Clear all the work fields W3 - W10 in one statement.

Reformat all the IFs on W2 into 1 IF - ELSEIF condition. Thus:

IF W2 >= 366 .
  W10 = 1 .
elseIF W2 >= 181.
  W9 = 1 .
elseIF W2 >= 91.
  W8 = 1 .
elseIF W2 >= 61.
  W7 = 1 .
elseIF W2 >= 31.
  W6 = 1 .
elseIF W2 >= 15.
  W5 = 1 .
elseIF W2 >= 8.
  W4 = 1 .
else.
  W3 = 1 .
ENDIF.

MattG.

Re: REGARDING PERFORMANCE

Former Member — Fri, 06 Jul 2007 11:21:12 GMT

Dear Kumar,

1.You can avoid into corresponding if your itab structure is accordingly.

2.Before the second selection statement you should check whether ITAB is initial or not. If ITAB is initial youe second selection will pull all the entries from AUFK.

3. Similarly for 3rd selection

4 Use this code

LOOP AT IT_CRHD.

R_OBJID-SIGN = 'I'.

R_OBJID-OPTION = 'EQ'.

R_OBJID-LOW = IT_CRHD-OBJID.

APPEND R_OBJID.

ENDLOOP.

ENDIF. before the first selcetion and add condition accordingly so that you can avoid statement DELETE ITAB WHERE NOT GEWRK IN R_OBJID

4.try to use one loop at itab only

Regards

Antony Thomas

reward poits if find helpful!

Re: REGARDING PERFORMANCE

Former Member — Fri, 06 Jul 2007 11:36:22 GMT

A few things u could probably do is:

SELECT AUFNR EQUNR ILART ILOAN INGPR IWERK GEWRK

FROM AFIH

INTO CORRESPONDING FIELDS OF TABLE ITAB

WHERE AUFNR IN AUFNR

AND EQUNR IN EQUNR

AND ILART IN ILART

AND IPHAS IN IPHAS

AND INGPR IN INGPR

AND IWERK IN IWERK.

Avoid using into corresponging fields of. Instead have an internal table with the same fields in the same order.

IF SY-SUBRC NE 0.

MESSAGE I000(YW) WITH ' No Data Found For The Selection'.

STOP.

ENDIF.

Before using a select query with for all entries delete duplicate recordsfrom table itab.

For eg. move contents of itab in a temporary table itab_temp.

itab_temp[] = itab[].

sort itab_temp on the basis of the fields which are coming in the where clause.

sort itab_temp by aufnr.

delete all the adjacent duplicate records comparing aufnr

delete adjacent duplicates from itab_temp comparing aufnr .

now use itab_temp in the selevct query.

SELECT AUFNR AUART KTEXT OBJNR

INTO TABLE I_AUFK

FROM AUFK

FOR ALL ENTRIES IN ITAB_temp

WHERE AUFNR = ITAB_TEMP-AUFNR

AND AUART IN AUART

AND KTEXT IN KTEXT.

IF SY-SUBRC EQ 0.

SORT I_AUFK BY AUFNR.

ENDIF.

same thing applies here

SELECT ILOAN SWERK TPLNR STORT

FROM ILOA

INTO TABLE I_ILOA

FOR ALL ENTRIES IN ITAB

WHERE ILOAN = ITAB-ILOAN

AND SWERK IN SWERK

AND TPLNR IN TPLNR

AND STORT IN STORT.

IF SY-SUBRC EQ 0.

SORT I_ILOA BY ILOAN.

ENDIF.

same thing applies here

SELECT AUFNR GSTRP

FROM AFKO

INTO TABLE I_AFKO

FOR ALL ENTRIES IN ITAB

WHERE AUFNR = ITAB-AUFNR

AND GSTRP IN GSTRP.

IF SY-SUBRC EQ 0.

SORT I_AFKO BY AUFNR.

ENDIF.

IF NOT P_ARBPL IS INITIAL.

SELECT OBJID WERKS ARBPL

FROM CRHD INTO TABLE IT_CRHD

WHERE WERKS IN SWERK

AND ARBPL = P_ARBPL.

LOOP AT IT_CRHD.

R_OBJID-SIGN = 'I'.

R_OBJID-OPTION = 'EQ'.

R_OBJID-LOW = IT_CRHD-OBJID.

APPEND R_OBJID.

ENDLOOP.

ENDIF.

DELETE ITAB WHERE NOT GEWRK IN R_OBJID.

LOOP AT ITAB.

CLEAR: I_AUFK, I_ILOA, I_AFKO.

READ TABLE I_AUFK

WITH KEY AUFNR = ITAB-AUFNR

BINARY SEARCH.

IF SY-SUBRC = 0.

ITAB-AUART = I_AUFK-AUART.

ITAB-KTEXT = I_AUFK-KTEXT.

ITAB-OBJNR = I_AUFK-OBJNR.

MODIFY ITAB.

use modify along with the index addition to make the process faster.

ELSE.

DELETE ITAB .

never have a delete inside a loop it takes alot of time. Instead set a flag here and later on after the loop delete all those entries for which the flag is set

CONTINUE.

ENDIF.

READ TABLE I_ILOA

WITH KEY ILOAN = ITAB-ILOAN

BINARY SEARCH.

IF SY-SUBRC = 0.

ITAB-SWERK = I_ILOA-SWERK.

ITAB-TPLNR = I_ILOA-TPLNR.

ITAB-STORT = I_ILOA-STORT.

MODIFY ITAB.

ELSE.

DELETE ITAB .

CONTINUE.

ENDIF.

READ TABLE I_AFKO

WITH KEY AUFNR = ITAB-AUFNR

BINARY SEARCH.

IF SY-SUBRC = 0.

ITAB-GSTRP = I_AFKO-GSTRP .

MODIFY ITAB.

ELSE.

DELETE ITAB .

CONTINUE.

ENDIF.

ENDLOOP.

LOOP AT ITAB.

W2 = SY-DATUM - ITAB-GSTRP .

CLEAR W7 .

IF W2 >= 61 AND W2 < 91 .

W7 = 1 .

ENDIF.

CLEAR W8 .

IF W2 >= 91 AND W2 < 181 .

W8 = 1 .

ENDIF.

CLEAR W9 .

IF W2 >= 181 AND W2 < 366 .

W9 = 1 .

ENDIF.

CLEAR W10 .

IF W2 >= 366 .

W10 = 1 .

ENDIF.

CLEAR W6 .

IF W2 >= 31 AND W2 < 61 .

W6 = 1 .

ENDIF.

CLEAR W5 .

IF W2 >= 15 AND W2 < 31 .

W5 = 1 .

ENDIF.

CLEAR W4 .

IF W2 >= 8 AND W2 < 15 .

W4 = 1 .

ENDIF.

CLEAR W3 .

IF W2 < 8 .

W3 = 1 .

ENDIF.

ITAB-W3 = W3.

ITAB-W4 = W4.

ITAB-W5 = W5.

ITAB-W6 = W6.

ITAB-W7 = W7.

ITAB-W8 = W8.

ITAB-W9 = W9.

ITAB-W10 = W10.

MODIFY ITAB.

Use the index addition in modify

LOOP AT T02 WHERE SWERK1 = ITAB-SWERK AND INGPR1 = ITAB-INGPR

AND STORT1 = ITAB-STORT.

T02-W3 = T02-W3 + W3.

T02-W4 = T02-W4 + W4.

T02-W5 = T02-W5 + W5.

T02-W6 = T02-W6 + W6.

T02-W7 = T02-W7 + W7.

T02-W8 = T02-W8 + W8.

T02-W9 = T02-W9 + W9.

T02-W10 = T02-W10 + W10.

MODIFY T02.

ENDLOOP.

IF SY-SUBRC NE 0.

T02-SWERK1 = ITAB-SWERK.

T02-INGPR1 = ITAB-INGPR.

T02-STORT1 = ITAB-STORT.

T02-W3 = W3.

T02-W4 = W4.

T02-W5 = W5.

T02-W6 = W6.

T02-W7 = W7.

T02-W8 = W8.

T02-W9 = W9.

T02-W10 = W10.

APPEND T02.

ENDIF.

ENDLOOP.

Hope this Helps...

Regards,

Geeta

Re: REGARDING PERFORMANCE

Former Member — Fri, 06 Jul 2007 13:11:08 GMT

In addition to the above:

Currently you may modify ITAB and then delete it so change this to

LOOP AT ITAB.
* CLEAR: I_AUFK, I_ILOA, I_AFKO.   "Not needed, all values are overwritten
  READ TABLE <b>I_CAUFV</b>
      WITH KEY AUFNR = ITAB-AUFNR
      BINARY SEARCH.
  IF SY-SUBRC  <b>NE</b>  0.
    DELETE ITAB .
*  never have a delete inside a loop it takes alot of time. Instead set a flag here
*  and later on after the loop delete all those entries for which the flag is set
    CONTINUE.
  ENDIF.

  READ TABLE I_ILOA
      WITH KEY ILOAN = ITAB-ILOAN
      BINARY SEARCH.
  IF SY-SUBRC  NE  0.
    DELETE ITAB .
    CONTINUE.
  ENDIF.

*   Only OK records get to this point 
  ITAB-SWERK = I_ILOA-SWERK.
  ITAB-TPLNR = I_ILOA-TPLNR.
  ITAB-STORT = I_ILOA-STORT.
  ITAB-GSTRP = I_AFKO-GSTRP .
  ITAB-AUART = I_AUFK-AUART.
  ITAB-KTEXT = I_AUFK-KTEXT.
  ITAB-OBJNR = I_AUFK-OBJNR.
  MODIFY ITAB.
* use modify along with the index addition to make the process faster.
ENDLOOP.

Your T02 process can be simplified by the COLLECT command. Thus:-

*LOOP AT T02 WHERE SWERK1 = ITAB-SWERK 
*  AND INGPR1 = ITAB-INGPR
*  AND STORT1 = ITAB-STORT.
*  T02-W3 = T02-W3 + W3.
*  T02-W4 = T02-W4 + W4.
*  T02-W5 = T02-W5 + W5.
*  T02-W6 = T02-W6 + W6.
*  T02-W7 = T02-W7 + W7.
*  T02-W8 = T02-W8 + W8.
*  T02-W9 = T02-W9 + W9.
*  T02-W10 = T02-W10 + W10.
*  MODIFY T02.
*  ENDLOOP.
*  IF SY-SUBRC NE 0.
T02-SWERK1 = ITAB-SWERK.
T02-INGPR1 = ITAB-INGPR.
T02-STORT1 = ITAB-STORT.

T02-W3 = W3.
T02-W4 = W4.
T02-W5 = W5.
T02-W6 = W6.
T02-W7 = W7.
T02-W8 = W8.
T02-W9 = W9.
T02-W10 = W10.
COLLECT T02.
*  APPEND T02.
*  ENDIF.

MattG.

Re: REGARDING PERFORMANCE

Former Member — Fri, 06 Jul 2007 13:24:46 GMT

You have anested loop:


LOOP AT itab.
...
  LOOP AT t02 WHERE swerk1 = itab-swerk AND ingpr1 = itab-ingpr
  AND stort1 = itab-stort.
  ...
  ENDLOOP.
ENDLOOP.

Depending on the number of entries in these tables, you can replace the inner loop with a binary read followed by index reads.

Rob