Function Module Performance Tunning Issue

[Former Member]

I would like improve the performance for this below function module.

This FM is for BW extraction . Ztest is the view created based aufnr,afru and cdhdr table with 12 fields.

I have checked in the SE30 -transaction . It hit is 92 % in the database level and ABAP is only 7% . Please advise me how can improve the performace of this function module.

Advance thanks for your help.

FUNCTION ZTEST.

*"----

---

""Local interface:

*" IMPORTING

*" REFERENCE(I_REQUNR) TYPE SRSC_S_IF_SIMPLE-REQUNR

*" REFERENCE(I_DSOURCE) TYPE SRSC_S_IF_SIMPLE-DSOURCE OPTIONAL

*" REFERENCE(I_MAXSIZE) TYPE SRSC_S_IF_SIMPLE-MAXSIZE DEFAULT 1000

*" REFERENCE(I_INITFLAG) TYPE SRSC_S_IF_SIMPLE-INITFLAG OPTIONAL

*" REFERENCE(I_READ_ONLY) TYPE SRSC_S_IF_SIMPLE-READONLY OPTIONAL

*" TABLES

*" I_T_SELECT TYPE SRSC_S_IF_SIMPLE-T_SELECT OPTIONAL

*" I_T_FIELDS TYPE SRSC_S_IF_SIMPLE-T_FIELDS OPTIONAL

*" E_T_DATA STRUCTURE ZVIEW1 OPTIONAL

*" EXCEPTIONS

*" NO_MORE_DATA

*" ERROR_PASSED_TO_MESS_HANDLER

*"----

---

TABLES: ZVIEW1.

• Auxiliary Selection criteria structure

DATA: L_S_SELECT TYPE SRSC_S_SELECT.

• Initialization mode (first call by SAPI) or data transfer mode

• (following calls) ?

*----

---

• Data Structures for delta-mechanism

*----

---

DATA: L_V_LINES TYPE I.

*----

---

• Static structures

*----

---

STATICS: S_S_IF TYPE SRSC_S_IF_SIMPLE,

• counter

S_COUNTER_DATAPAKID LIKE SY-TABIX,

• cursor

S_CURSOR TYPE CURSOR.

• Ranges for data selection

RANGES: L_R_ERSDA FOR ZVIEW1-ERSDA,

L_R_AUFNR FOR ZVIEW1-AUFNR,

L_R_BUDAT FOR ZVIEW1-BUDAT,

L_R_RMZHL FOR ZVIEW1-RMZHL,

L_R_RUECK FOR ZVIEW1-RUECK,

L_R_VORNR FOR ZVIEW1-VORNR.

DATA: L_T_DATA TYPE STANDARD TABLE OF ZVIEW1 WITH HEADER LINE.

• Initialization mode (first call by SAPI) or data transfer mode

• (following calls) ?

IF I_INITFLAG = SBIWA_C_FLAG_ON.

************************************************************************

• Initialization: check input parameters

• buffer input parameters

• prepare data selection

************************************************************************

• Check DataSource validity

CASE I_DSOURCE.

WHEN 'ZTEST'.

WHEN OTHERS.

RAISE ERROR_PASSED_TO_MESS_HANDLER.

ENDCASE.

• Copy selection criteria for future extractor calls (fetch-calls)

APPEND LINES OF I_T_SELECT TO S_S_IF-T_SELECT.

• Fill parameter buffer for data extraction calls

S_S_IF-REQUNR = I_REQUNR.

S_S_IF-DSOURCE = I_DSOURCE.

S_S_IF-MAXSIZE = I_MAXSIZE.

S_S_IF-INITFLAG = I_INITFLAG.

• Fill field list table for an optimized select statement

• (in case that there is no 1:1 relation between InfoSource fields

• and database table fields this may be far from being trivial)

APPEND LINES OF I_T_FIELDS TO S_S_IF-T_FIELDS.

ELSE. "Initialization mode or data extraction ?

************************************************************************

• Data transfer: First Call OPEN CURSOR + FETCH

• Following Calls FETCH only

************************************************************************

• First data package -> OPEN CURSOR

IF S_COUNTER_DATAPAKID = 0.

• Fill range tables BW will only pass down simple selection criteria

• of the type SIGN = 'I' and OPTION = 'EQ' or OPTION = 'BT'.

LOOP AT S_S_IF-T_SELECT INTO L_S_SELECT

WHERE FIELDNM = 'ERSDA'.

MOVE-CORRESPONDING L_S_SELECT TO L_R_ERSDA.

APPEND L_R_ERSDA.

ENDLOOP.

LOOP AT S_S_IF-T_SELECT INTO L_S_SELECT

WHERE FIELDNM = 'AUFNR'.

MOVE-CORRESPONDING L_S_SELECT TO L_R_AUFNR.

APPEND L_R_AUFNR.

ENDLOOP.

LOOP AT S_S_IF-T_SELECT INTO L_S_SELECT

WHERE FIELDNM = 'BUDAT'.

MOVE-CORRESPONDING L_S_SELECT TO L_R_BUDAT.

APPEND L_R_BUDAT.

ENDLOOP.

LOOP AT S_S_IF-T_SELECT INTO L_S_SELECT

WHERE FIELDNM = 'RMZHL'.

MOVE-CORRESPONDING L_S_SELECT TO L_R_RMZHL.

APPEND L_R_RMZHL.

ENDLOOP.

LOOP AT S_S_IF-T_SELECT INTO L_S_SELECT

WHERE FIELDNM = 'RUECK'.

MOVE-CORRESPONDING L_S_SELECT TO L_R_RUECK.

APPEND L_R_RUECK.

ENDLOOP.

LOOP AT S_S_IF-T_SELECT INTO L_S_SELECT

WHERE FIELDNM = 'VORNR'.

MOVE-CORRESPONDING L_S_SELECT TO L_R_VORNR.

APPEND L_R_VORNR.

ENDLOOP.

• Determine number of database records to be read per FETCH statement

• from input parameter I_MAXSIZE. If there is a one to one relation

• between DataSource table lines and database entries, this is trivial.

• In other cases, it may be impossible and some estimated value has to

• be determined.

• Deltainit?

DESCRIBE TABLE L_R_ERSDA LINES L_V_LINES.

IF L_V_LINES = 1.

READ TABLE L_R_ERSDA INDEX 1.

IF L_R_ERSDA-SIGN = 'I'

AND L_R_ERSDA-OPTION = 'BT'

AND L_R_ERSDA-LOW = SPACE

AND L_R_ERSDA-HIGH = SY-DATUM.

CLEAR L_R_ERSDA.

REFRESH L_R_ERSDA.

ENDIF.

ENDIF.

IF L_R_ERSDA[] IS INITIAL.

• Deltainit or Full

OPEN CURSOR WITH HOLD S_CURSOR FOR

SELECT *

FROM ZVIEW1

WHERE AUFNR IN L_R_AUFNR

AND BUDAT IN L_R_BUDAT

AND RMZHL IN L_R_RMZHL

AND RUECK IN L_R_RUECK

AND VORNR IN L_R_VORNR.

ELSE.

• Delta

OPEN CURSOR WITH HOLD S_CURSOR FOR

SELECT *

FROM ZVIEW1

WHERE AUFNR IN L_R_AUFNR

AND BUDAT IN L_R_BUDAT

AND RMZHL IN L_R_RMZHL

AND RUECK IN L_R_RUECK

AND VORNR IN L_R_VORNR

AND ( ERSDA IN L_R_ERSDA

OR LAEDA IN L_R_ERSDA ).

ENDIF. "Full or Delta ?

ENDIF. "First data package ?

• Fetch records into e_t_data

FETCH NEXT CURSOR S_CURSOR

INTO CORRESPONDING FIELDS

OF TABLE L_T_DATA

PACKAGE SIZE S_S_IF-MAXSIZE.

IF SY-SUBRC <> 0.

CLOSE CURSOR S_CURSOR.

RAISE NO_MORE_DATA.

ENDIF.

• Next data package

S_COUNTER_DATAPAKID = S_COUNTER_DATAPAKID + 1.

ENDIF. "Initialization mode or data extraction ?

• delete records marked as cancelled.

DELETE L_T_DATA WHERE STZHL NE ''.

SORT L_T_DATA BY AUFNR RUECK RMZHL.

LOOP AT L_T_DATA.

IF SY-SUBRC = 0.

IF L_T_DATA-STOKZ = 'X'.

L_T_DATA-STZHL = L_T_DATA-RMZHL.

ENDIF.

ENDIF.

• Append into interface table e_t_data.

MOVE-CORRESPONDING L_T_DATA TO E_T_DATA.

APPEND E_T_DATA.

ENDLOOP.

ENDFUNCTION.

[Former Member]

Hi,

Avoid Select * statements.

Declare an internal table of particular fields & select those particulare fields in internal table from Database istead of providing select *.

Do one more thing give target area for selecting records. If you are selecting only one record the use SELECT SINGLE statement. Or if you are using multiple reocrds the use INTO TABLE addition.

eg.

DATA: begin of it_kan1 occurs 100,

kunnr type kunnr,

name1 type name1,

end if it_kna1.

SELECT kunnr name1

FROM kna1

INTO TABLE it_kna1

WHERE ..........

The performance of this function module will be increased drastically.

For example:

DATA: begin of

Reward points if helpful answer.

Asvhender

[Former Member]

> I would like improve the performance for this below

> function module.

> This FM is for BW extraction . Ztest is the view

> created based aufnr,afru and cdhdr table with 12

> fields.

>

> I have checked in the SE30 -transaction . It hit is

> 92 % in the database level and ABAP is only 7% .

> Please advise me how can improve the performace of

> this function module.

> Advance thanks for your help.

>

>

>

>

> FUNCTION ZTEST.

> *"----

---

> -

---

> ""Local interface:

> *" IMPORTING

> *" REFERENCE(I_REQUNR) TYPE

> SRSC_S_IF_SIMPLE-REQUNR

> " REFERENCE(I_DSOURCE) TYPE

> SRSC_S_IF_SIMPLE-DSOURCE OPTIONAL

> " REFERENCE(I_MAXSIZE) TYPE

> SRSC_S_IF_SIMPLE-MAXSIZE DEFAULT 1000

> " REFERENCE(I_INITFLAG) TYPE

> SRSC_S_IF_SIMPLE-INITFLAG OPTIONAL

> " REFERENCE(I_READ_ONLY) TYPE

> SRSC_S_IF_SIMPLE-READONLY OPTIONAL

> " TABLES

> *" I_T_SELECT TYPE SRSC_S_IF_SIMPLE-T_SELECT

> OPTIONAL

> *" I_T_FIELDS TYPE SRSC_S_IF_SIMPLE-T_FIELDS

> OPTIONAL

> *" E_T_DATA STRUCTURE ZVIEW1 OPTIONAL

> *" EXCEPTIONS

> *" NO_MORE_DATA

> *" ERROR_PASSED_TO_MESS_HANDLER

> *"----

---

> -

---

> TABLES: ZVIEW1.

> * Auxiliary Selection criteria structure

>

> DATA: L_S_SELECT TYPE SRSC_S_SELECT.

> * Initialization mode (first call by SAPI) or data

> transfer mode

> * (following calls) ?

>

> *----

---

> -

---

> * Data Structures for delta-mechanism

> *----

---

> -

---

> DATA: L_V_LINES TYPE I.

> -

---

> -

---

> * Static structures

> *----

---

> -

---

> STATICS: S_S_IF TYPE SRSC_S_IF_SIMPLE,

> counter

> S_COUNTER_DATAPAKID LIKE SY-TABIX,

> S_CURSOR TYPE CURSOR.

> for data selection

> RANGES: L_R_ERSDA FOR ZVIEW1-ERSDA,

> L_R_AUFNR FOR ZVIEW1-AUFNR,

> L_R_BUDAT FOR ZVIEW1-BUDAT,

> L_R_RMZHL FOR ZVIEW1-RMZHL,

> L_R_RUECK FOR ZVIEW1-RUECK,

> L_R_VORNR FOR ZVIEW1-VORNR.

> L_T_DATA TYPE STANDARD TABLE OF ZVIEW1 WITH HEADER

> LINE.

>

> * Initialization mode (first call by SAPI) or data

> transfer mode

> * (following calls) ?

>

> IF I_INITFLAG = SBIWA_C_FLAG_ON.

> ******************************************************

> ******************

>

> * Initialization: check input parameters

>

> * buffer input parameters

>

> * prepare data selection

>

> ******************************************************

> ******************

>

> * Check DataSource validity

>

> CASE I_DSOURCE.

> WHEN 'ZTEST'.

> WHEN OTHERS.

> RAISE ERROR_PASSED_TO_MESS_HANDLER.

> ENDCASE.

> * Copy selection criteria for future extractor calls

> (fetch-calls)

>

> APPEND LINES OF I_T_SELECT TO S_S_IF-T_SELECT.

> Fill parameter buffer for data extraction calls

>

> S_S_IF-REQUNR = I_REQUNR.

> S_S_IF-DSOURCE = I_DSOURCE.

> S_S_IF-MAXSIZE = I_MAXSIZE.

> S_S_IF-INITFLAG = I_INITFLAG.

>

> * Fill field list table for an optimized select

> statement

> * (in case that there is no 1:1 relation between

> InfoSource fields

> * and database table fields this may be far from

> being trivial)

> APPEND LINES OF I_T_FIELDS TO S_S_IF-T_FIELDS.

> ELSE. "Initialization mode or data

> extraction ?

>

> ******************************************************

> ******************

>

> * Data transfer: First Call OPEN CURSOR + FETCH

>

> * Following Calls FETCH only

>

> ******************************************************

> ******************

>

> * First data package -> OPEN CURSOR

>

> IF S_COUNTER_DATAPAKID = 0.

> ll range tables BW will only pass down simple

> selection criteria

> * of the type SIGN = 'I' and OPTION = 'EQ' or OPTION

> = 'BT'.

>

> LOOP AT S_S_IF-T_SELECT INTO L_S_SELECT

> WHERE FIELDNM = 'ERSDA'.

> NG L_S_SELECT TO L_R_ERSDA.

> APPEND L_R_ERSDA.

> DLOOP.

>

> LOOP AT S_S_IF-T_SELECT INTO L_S_SELECT

> WHERE FIELDNM = 'AUFNR'.

> NG L_S_SELECT TO L_R_AUFNR.

> APPEND L_R_AUFNR.

> DLOOP.

>

> LOOP AT S_S_IF-T_SELECT INTO L_S_SELECT

> WHERE FIELDNM = 'BUDAT'.

> NG L_S_SELECT TO L_R_BUDAT.

> APPEND L_R_BUDAT.

> DLOOP.

> LOOP AT S_S_IF-T_SELECT INTO L_S_SELECT

> WHERE FIELDNM = 'RMZHL'.

> NG L_S_SELECT TO L_R_RMZHL.

> APPEND L_R_RMZHL.

> DLOOP.

> LOOP AT S_S_IF-T_SELECT INTO L_S_SELECT

> WHERE FIELDNM = 'RUECK'.

> NG L_S_SELECT TO L_R_RUECK.

> APPEND L_R_RUECK.

> DLOOP.

> LOOP AT S_S_IF-T_SELECT INTO L_S_SELECT

> WHERE FIELDNM = 'VORNR'.

> NG L_S_SELECT TO L_R_VORNR.

> APPEND L_R_VORNR.

> DLOOP.

>

> * Determine number of database records to be read per

> FETCH statement

> * from input parameter I_MAXSIZE. If there is a one

> to one relation

> * between DataSource table lines and database

> entries, this is trivial.

> * In other cases, it may be impossible and some

> estimated value has to

> * be determined.

>

> * Deltainit?

> DESCRIBE TABLE L_R_ERSDA LINES L_V_LINES.

> IF L_V_LINES = 1.

> READ TABLE L_R_ERSDA INDEX 1.

> IF L_R_ERSDA-SIGN = 'I'

> AND L_R_ERSDA-OPTION = 'BT'

> AND L_R_ERSDA-LOW = SPACE

> AND L_R_ERSDA-HIGH = SY-DATUM.

> CLEAR L_R_ERSDA.

> REFRESH L_R_ERSDA.

> ENDIF.

> ENDIF.

> IF L_R_ERSDA[] IS INITIAL.

> Deltainit or Full

>

<b>Why is OPEN CURSOR being used ??

Please define an internal table having the same number of fields in the same order that you would like to select and then get the entries into the internal table in one go. Your fetch cursor is causing an issue alongwith CORRESPONDING statement.

Also apart from this you can also remove the MOVE-CORRESPONDING that you used above to specifically define which fields to move into which .

This will performance tune your code further.</b>

> OPEN CURSOR WITH HOLD S_CURSOR FOR

> SELECT *

> FROM ZVIEW1

> WHERE AUFNR IN L_R_AUFNR

> AND BUDAT IN L_R_BUDAT

> AND RMZHL IN L_R_RMZHL

> AND RUECK IN L_R_RUECK

> AND VORNR IN L_R_VORNR.

> ELSE.

> a

> OPEN CURSOR WITH HOLD S_CURSOR FOR

> SELECT *

> FROM ZVIEW1

> WHERE AUFNR IN L_R_AUFNR

> AND BUDAT IN L_R_BUDAT

> AND RMZHL IN L_R_RMZHL

> AND RUECK IN L_R_RUECK

> AND VORNR IN L_R_VORNR

> AND ( ERSDA IN L_R_ERSDA

> OR LAEDA IN L_R_ERSDA ).

> ENDIF. "Full or Delta

> ?

>

> ENDIF. "First data

> package ?

>

>

> * Fetch records into e_t_data

>

> FETCH NEXT CURSOR S_CURSOR

> INTO CORRESPONDING FIELDS

> OF TABLE L_T_DATA

> PACKAGE SIZE S_S_IF-MAXSIZE.

> -SUBRC <> 0.

>

> CLOSE CURSOR S_CURSOR.

> RAISE NO_MORE_DATA.

> ENDIF.

> * Next data package

> S_COUNTER_DATAPAKID = S_COUNTER_DATAPAKID + 1.

> ENDIF. "Initialization mode or data extraction ?

> * delete records marked as cancelled.

>

> DELETE L_T_DATA WHERE STZHL NE ''.

>

> SORT L_T_DATA BY AUFNR RUECK RMZHL.

>

> LOOP AT L_T_DATA.

> IF SY-SUBRC = 0.

> IF L_T_DATA-STOKZ = 'X'.

> L_T_DATA-STZHL = L_T_DATA-RMZHL.

> ENDIF.

> ENDIF.

> end into interface table e_t_data.

> MOVE-CORRESPONDING L_T_DATA TO E_T_DATA.

> APPEND E_T_DATA.

>

>

> ENDLOOP.

> ENDFUNCTION.

<b>The above mentioned will surely help you.

Regards

Nishant

</b>

[christian_wohlfahrt]

Hi!

You are using a view with probably several tables. Check

- if all key fields are linked in the view definition

- if all tables are needed (maybe you aren't interested in all fields)

- if some existing indices can speed up the access

- if a new index can speed a up the access

- with SQL-trace, how the different tables are accessed (maybe the optimizer has troubles with best way to select the entries)

Regards,

Christian

[Former Member]

solved this problem by myself