improve performance

[Former Member]

how can i improve the below source code

SELECT ATINN ATNAM FROM CABN INTO CORRESPONDING FIELDS OF TABLE I_CABN.

LOOP AT I_CABN.

CASE I_CABN-ATNAM.

WHEN 'Z_BP_SOURCE'.

SOURCE = I_CABN-ATINN.

WHEN 'Z_BP_VERTICAL'.

VERTICAL = I_CABN-ATINN.

WHEN 'CLIENT_TYPE'.

CLIENT = I_CABN-ATINN.

WHEN 'Z_SIZE_OF_ORG'.

SIZE_OF_ORG = I_CABN-ATINN.

WHEN 'Z_LOB'.

ZLOB = I_CABN-ATINN.

WHEN 'Z_GEOGRAPHY'.

ZGEOGRAPHY = I_CABN-ATINN.

ENDCASE.

ENDLOOP.

• sort itab by partner1.

*************to get mkkting attributes*****************

loop at itab into itab.

SOURCE**

SELECT SINGLE atwrt FROM AUSP INTO SOURCETYPE WHERE

PARTNER_GUID = Itab-Partner_GUID. "AND ATINN = SOURCE.

IF SOURCETYPE <> ''.

IF SY-SUBRC = 0.

SELECT SINGLE atzhl FROM CAWN INTO ITAB-ATZHL2

WHERE ATINN = SOURCE AND Atwrt = SOURCETYPE.

IF SY-SUBRC = 0.

SELECT SINGLE ATWTB FROM CAWNT INTO (ITAB-SOURCE)

WHERE ATZHL = ITAB-ATZHL2 AND ATINN = SOURCE.

ENDIF.

ENDIF.

ENDIF.

*VERTICAL

SELECT SINGLE atwrt FROM AUSP INTO verticalTYPE WHERE

PARTNER_GUID = Itab-Partner_GUID AND ATINN = VERTICAL.

IF verticaltype <> ''.

IF SY-SUBRC = 0.

SELECT SINGLE atzhl FROM CAWN INTO ITAB-ATZHL1

WHERE ATINN = VERTICAL AND Atwrt = verticalTYPE.

ENDIF.

IF SY-SUBRC = 0.

SELECT SINGLE ATWTB FROM CAWNT INTO (ITAB-vertical)

WHERE ATZHL = ITAB-ATZHL1 AND ATINN = VERTICAL.

ENDIF.

ENDIF.

• client type

SELECT SINGLE atwrt FROM AUSP INTO clienttype WHERE

PARTNER_GUID = ITAB-PARTNER_GUID AND ATINN = CLIENT.

IF clienttype <> ''.

IF SY-SUBRC = 0.

SELECT SINGLE ATZHL FROM CAWN INTO (ITAB-ATZHL)

WHERE ATINN = CLIENT AND ATWRT = clienttype.

ENDIF.

IF SY-SUBRC = 0.

SELECT SINGLE ATWTB FROM CAWNT INTO ITAB-CLIENT

WHERE ATINN = CLIENT AND ATZHL = ITAB-ATZHL.

ENDIF.

endif.

• SELECT SINGLE ATVALUEDESCR FROM CAWNT INTO ITAB-CLIENT

• WHERE ATINN = '10858' AND ATZHL = clienttype.

• ENDIF.

**SIZE_OF_ORG**

SELECT SINGLE atwrt FROM AUSP INTO ORGSIZE WHERE

PARTNER_GUID = Itab-Partner_GUID AND ATINN = SIZE_OF_ORG.

IF ORGSIZE <> ''.

IF SY-SUBRC = 0.

SELECT SINGLE atzhl FROM CAWN INTO ITAB-ATZHL3

WHERE ATINN = SIZE_OF_ORG AND Atwrt = ORGSIZE.

ENDIF.

IF SY-SUBRC = 0.

SELECT SINGLE ATWTB FROM CAWNT INTO (ITAB-ORGSIZE)

WHERE ATZHL = ITAB-ATZHL3 AND ATINN = SIZE_OF_ORG.

ENDIF.

ENDIF.

*LOB*

SELECT SINGLE atwrt FROM AUSP INTO LOB WHERE

PARTNER_GUID = Itab-Partner_GUID AND ATINN = ZLOB.

IF LOB <> ''.

IF SY-SUBRC = 0.

SELECT SINGLE atzhl FROM CAWN INTO ITAB-ATZHL4

WHERE ATINN = ZLOB AND Atwrt = LOB.

ENDIF.

IF SY-SUBRC = 0.

SELECT SINGLE ATWTB FROM CAWNT INTO (ITAB-LOB)

WHERE ATZHL = ITAB-ATZHL4 AND ATINN = ZLOB.

ENDIF.

ENDIF.

******GEOGRAPHY

SELECT SINGLE atwrt FROM AUSP INTO GEOGRAPHY WHERE

PARTNER_GUID = Itab-Partner_GUID AND ATINN = ZGEOGRAPHY.

IF GEOGRAPHY <> ''.

IF SY-SUBRC = 0.

SELECT SINGLE atzhl FROM CAWN INTO ITAB-ATZHL5

WHERE ATINN = ZGEOGRAPHY AND Atwrt = GEOGRAPHY.

ENDIF.

IF SY-SUBRC = 0.

SELECT SINGLE ATWTB FROM CAWNT INTO (ITAB-GEOGRAPHY)

WHERE ATZHL = ITAB-ATZHL5 AND ATINN = ZGEOGRAPHY.

ENDIF.

ENDIF.

modify itab.

endloop.

*************************end mkktg attr**************

LOOP AT ITAB.

IF ITAB-CLASSIFIC <> ''.

ITAB-ROLE = 'Sold-To-Party'.

ELSE.

ITAB-ROLE = 'Business Partner'.

ENDIF.

MODIFY ITAB.

ENDLOOP.

*

Message was edited by:

EKTA JAIN

[Former Member]

Hi Ekta,

You can improve the performance making the following changes in the above code:

1. Avoid using into corresponding fields in the select statement, desing the internal table in such a way that, the order in which u fetch fields from the database table is same as order of field in ur internal table.

2. Dont use so many selects, use inner join wherever possible for instance,

under source you can join : ausp, cawn, cawnt.

regards,

Navneeth K.

[Former Member]

Well, you're selecting every record from CABN and then looping through it doing more SELECTs. Add a WHERE condition to that SELECT to reduce the number of hits.

In some of your SELECT SINGLEs on CAWN, you are commenting out part of the primnary key (ATINN).

Rob

[Former Member]

Hi Ekta,

Use the following to improve the performance of your program.

Firstly remove all the select queries within the loop and endloop.

Select the header level data using select single if only one record is being fetched.

Select the item level data of the header using <b>for all entries</b> in header table's internal table contents.

Make the select query independent of the loop endloop for your performance to be much better.

This will help you improve the performance by leaps and bounds.

Reward Points.

Tej..

[Former Member]

Following are the performance standards need to be following in writing ABAP programs:

1. Unused/Dead code

Avoid leaving unused code in the program. Either comment out or delete the unused situation. Use program --> check --> extended program to check for the variables, which are not used statically.

2. Subroutine Usage

For good modularization, the decision of whether or not to execute a subroutine should be made before the subroutine is called. For example:

This is better:

IF f1 NE 0.

PERFORM sub1.

ENDIF.

FORM sub1.

...

ENDFORM.

Than this:

PERFORM sub1.

FORM sub1.

IF f1 NE 0.

...

ENDIF.

ENDFORM.

3. Usage of IF statements

When coding IF tests, nest the testing conditions so that the outer conditions are those which are most likely to fail. For logical expressions with AND , place the mostly likely false first and for the OR, place the mostly likely true first.

Example - nested IF's:

IF (least likely to be true).

IF (less likely to be true).

IF (most likely to be true).

ENDIF.

ENDIF.

ENDIF.

Example - IF...ELSEIF...ENDIF :

IF (most likely to be true).

ELSEIF (less likely to be true).

ELSEIF (least likely to be true).

ENDIF.

Example - AND:

IF (least likely to be true) AND

(most likely to be true).

ENDIF.

Example - OR:

IF (most likely to be true) OR

(least likely to be true).

4. CASE vs. nested Ifs

When testing fields "equal to" something, one can use either the nested IF or the CASE statement. The CASE is better for two reasons. It is easier to read and after about five nested IFs the performance of the CASE is more efficient.

5. MOVE statements

When records a and b have the exact same structure, it is more efficient to MOVE a TO b than to MOVE-CORRESPONDING a TO b.

MOVE BSEG TO *BSEG.

is better than

MOVE-CORRESPONDING BSEG TO *BSEG.

6. SELECT and SELECT SINGLE

When using the SELECT statement, study the key and always provide as much of the left-most part of the key as possible. If the entire key can be qualified, code a SELECT SINGLE not just a SELECT. If you are only interested in the first row or there is only one row to be returned, using SELECT SINGLE can increase performance by up to three times.

7. Small internal tables vs. complete internal tables

In general it is better to minimize the number of fields declared in an internal table. While it may be convenient to declare an internal table using the LIKE command, in most cases, programs will not use all fields in the SAP standard table.

For example:

Instead of this:

data: t_mara like mara occurs 0 with header line.

Use this:

data: begin of t_mara occurs 0,

matnr like mara-matnr,

...

end of t_mara.

8. Row-level processing and SELECT SINGLE

Similar to the processing of a SELECT-ENDSELECT loop, when calling multiple SELECT-SINGLE commands on a non-buffered table (check Data Dictionary -> Technical Info), you should do the following to improve performance:

o Use the SELECT into <itab> to buffer the necessary rows in an internal table, then

o sort the rows by the key fields, then

o use a READ TABLE WITH KEY ... BINARY SEARCH in place of the SELECT SINGLE command. Note that this only make sense when the table you are buffering is not too large (this decision must be made on a case by case basis).

9. READing single records of internal tables

When reading a single record in an internal table, the READ TABLE WITH KEY is not a direct READ. This means that if the data is not sorted according to the key, the system must sequentially read the table. Therefore, you should:

o SORT the table

o use READ TABLE WITH KEY BINARY SEARCH for better performance.

10. SORTing internal tables

When SORTing internal tables, specify the fields to SORTed.

SORT ITAB BY FLD1 FLD2.

is more efficient than

SORT ITAB.

11. Number of entries in an internal table

To find out how many entries are in an internal table use DESCRIBE.

DESCRIBE TABLE ITAB LINES CNTLNS.

is more efficient than

LOOP AT ITAB.

CNTLNS = CNTLNS + 1.

ENDLOOP.

12. Performance diagnosis

To diagnose performance problems, it is recommended to use the SAP transaction SE30, ABAP/4 Runtime Analysis. The utility allows statistical analysis of transactions and programs.

13. Nested SELECTs versus table views

Since releASE 4.0, OPEN SQL allows both inner and outer table joins. A nested SELECT loop may be used to accomplish the same concept. However, the performance of nested SELECT loops is very poor in comparison to a join. Hence, to improve performance by a factor of 25x and reduce network load, you should either create a view in the data dictionary then use this view to select data, or code the select using a join.

14. If nested SELECTs must be used

As mentioned previously, performance can be dramatically improved by using views instead of nested SELECTs, however, if this is not possible, then the following example of using an internal table in a nested SELECT can also improve performance by a factor of 5x:

Use this:

form select_good.

data: t_vbak like vbak occurs 0 with header line.

data: t_vbap like vbap occurs 0 with header line.

select * from vbak into table t_vbak up to 200 rows.

select * from vbap

for all entries in t_vbak

where vbeln = t_vbak-vbeln.

...

endselect.

endform.

Instead of this:

form select_bad.

select * from vbak up to 200 rows.

select * from vbap where vbeln = vbak-vbeln.

...

endselect.

endselect.

endform.

Although using "SELECT...FOR ALL ENTRIES IN..." is generally very fast, you should be aware of the three pitfalls of using it:

Firstly, SAP automatically removes any duplicates from the rest of the retrieved records. Therefore, if you wish to ensure that no qualifying records are discarded, the field list of the inner SELECT must be designed to ensure the retrieved records will contain no duplicates (normally, this would mean including in the list of retrieved fields all of those fields that comprise that table's primary key).

Secondly, if you were able to code "SELECT ... FROM <database table> FOR ALL ENTRIES IN TABLE <itab>" and the internal table <itab> is empty, then all rows from <database table> will be retrieved.

Thirdly, if the internal table supplying the selection criteria (i.e. internal table <itab> in the example "...FOR ALL ENTRIES IN TABLE <itab> ") contains a large number of entries, performance degradation may occur.

15. SELECT * versus SELECTing individual fields

In general, use a SELECT statement specifying a list of fields instead of a SELECT * to reduce network traffic and improve performance. For tables with only a few fields the improvements may be minor, but many SAP tables contain more than 50 fields when the program needs only a few. In the latter case, the performace gains can be substantial. For example:

Use:

select vbeln auart vbtyp from table vbak

into (vbak-vbeln, vbak-auart, vbak-vbtyp)

where ...

Instead of using:

select * from vbak where ...

16. Avoid unnecessary statements

There are a few cases where one command is better than two. For example:

Use:

append <tab_wa> to <tab>.

Instead of:

<tab> = <tab_wa>.

append <tab> (modify <tab>).

And also, use:

if not <tab>[] is initial.

Instead of:

describe table <tab> lines <line_counter>.

if <line_counter> > 0.

17. Copying or appending internal tables

Use this:

<tab2>[] = <tab1>[]. (if <tab2> is empty)

Instead of this:

loop at <tab1>.

append <tab1> to <tab2>.

endloop.

However, if <tab2> is not empty and should not be overwritten, then use:

append lines of <tab1> [from index1] [to index2] to <tab2>.