Increase perfonmace and decrease run time

[Former Member]

Hi Expert,

I am working on SAP R/3 PACK on DataSage to extract data from SAP. One job taking too long time(2 hr) to complete in production. I want to decrease the run time by improving the performance. First I am looking into ABAP code.

I am posting the code also. Can anybody pls suggest me any modification in the code with which I can decrease the run time(improve the performance).

Thanks in advance...

• Step 1. Declare Table

TABLES:

ZDELTA_EDW.

• Step 2. Declare Structure

DATA: BEGIN OF ST_1 OCCURS 1,

F_1(3) TYPE C, " For ZDELTA_EDW~CLIENT,

F_2(30) TYPE C, " For ZDELTA_EDW~TABNAME,

F_3(70) TYPE C, " For ZDELTA_EDW~TABKEY,

END OF ST_1.

DATA: IT_1 LIKE ST_1 OCCURS 0 WITH HEADER LINE.

• Declare Structure for INTO clause

DATA: BEGIN OF ST_2 OCCURS 1,

F_1 LIKE ZDELTA_EDW-CLIENT, " For ZXDELTA_EDW~CLIENT,

F_2 LIKE ZDELTA_EDW-TABNAME, " For ZXDELTA_EDW~TABNAME,

F_3 LIKE ZDELTA_EDW-TABKEY, " For ZXDELTA_EDW~TABKEY,

END OF ST_2.

• Step 3. Additional variables for ftp

DATA: R_COUNT TYPE I. " Record Count

DATA: R_MAX TYPE I. " Internal table limit

• Step 4. Define Data File

DATA: DSN(9999) TYPE C. " Dataset name

***Custom*************

TABLES: tcdob, zparam.

• Types

TYPES : BEGIN OF ty_tcdob,

object TYPE tcdob-object,

tabname TYPE tcdob-tabname,

END OF ty_tcdob.

TYPES : BEGIN OF ty_tables,

tabname TYPE tcdob-tabname,

END OF ty_tables.

TYPES : BEGIN OF ty_cdhdr,

objectclas TYPE cdhdr-objectclas,

objectid TYPE cdhdr-objectid,

changenr TYPE cdhdr-changenr,

udate TYPE cdhdr-udate,

END OF ty_cdhdr.

TYPES : BEGIN OF ty_cdpos,

objectclas TYPE cdpos-objectclas,

objectid TYPE cdpos-objectid,

changenr TYPE cdpos-changenr,

tabname TYPE cdpos-tabname,

tabkey TYPE cdpos-tabkey,

END OF ty_cdpos.

TYPES : BEGIN OF ty_tabkey,

mandt TYPE eket-mandt,

ebeln TYPE eket-ebeln,

ebelp TYPE eket-ebelp,

END OF ty_tabkey.

***

TYPES : BEGIN OF ty_zdelta_edw,

client TYPE zdelta_edw-client,

tabname TYPE zdelta_edw-tabname,

tabkey TYPE zdelta_edw-tabkey,

END OF ty_zdelta_edw.

TYPES : BEGIN OF ty_eket,

mandt TYPE eket-mandt,

ebeln TYPE eket-ebeln,

ebelp TYPE eket-ebelp,

etenr TYPE eket-etenr,

END OF ty_eket.

TYPES : BEGIN OF ty_ekko,

mandt TYPE eket-mandt,

ebeln TYPE eket-ebeln,

END OF ty_ekko.

TYPES : BEGIN OF ty_ekbe,

ebeln TYPE ekbe-ebeln,

ebelp TYPE ekbe-ebelp,

END OF ty_ekbe.

DATA : gt_eket TYPE STANDARD TABLE OF ty_eket

WITH HEADER LINE.

DATA : gt_ekpo TYPE STANDARD TABLE OF ty_ekbe

WITH HEADER LINE.

DATA : gt_ekbe TYPE STANDARD TABLE OF ty_ekbe

WITH HEADER LINE.

DATA : gt_ekko TYPE STANDARD TABLE OF ty_ekko

WITH HEADER LINE.

DATA : wa_eket TYPE ty_eket.

DATA : wa_ekpo TYPE ty_ekbe.

DATA : wa_ekbe TYPE ty_ekbe.

DATA : wa_ekko TYPE ty_ekko.

DATA : gv_fromdat TYPE budat.

DATA : gv_todat TYPE budat.

****

• Data

DATA: wa_tabkey type ty_tabkey.

• Table of object class for given SAP table

DATA : gt_tcdob TYPE STANDARD TABLE OF ty_tcdob INITIAL SIZE 0.

• Table containing SAP tables

DATA : gt_tables TYPE STANDARD TABLE OF ty_tables WITH HEADER LINE.

DATA : wa_tables TYPE ty_tables.

• Table containing CDHDR information

DATA : gt_cdhdr TYPE STANDARD TABLE OF ty_cdhdr WITH HEADER LINE.

DATA : wa_cdhdr TYPE ty_cdhdr.

• Table containing CDPOS information

DATA : gt_cdpos TYPE STANDARD TABLE OF ty_cdpos WITH HEADER LINE.

• Table containing zdelta_edw information

DATA : gt_zdelta_edw TYPE STANDARD TABLE OF ty_zdelta_edw

WITH HEADER LINE.

DATA : wa_cdpos TYPE ty_cdpos.

DATA : wa_zdelta_edw TYPE ty_zdelta_edw.

• Constants

CONSTANTS : gc_edw TYPE zparam-paramtype

VALUE '......',

gc_preserv TYPE zparam-subtype

VALUE '......',

gc_cdhdrd TYPE zparam-key1

VALUE '........'.

DSN = '/datafiles/DR3/DATASTAGE/ABAPExtract/....'.

OPEN DATASET DSN FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.

IF SY-SUBRC <> 0.

WRITE:/ 'Unable to open dataset: ', DSN.

EXIT.

ENDIF.

• Step 5. START-OF-SELECTION

START-OF-SELECTION.

• Step 6. Initialize variables

R_MAX = 1000.

R_COUNT = 0.

CLEAR IT_1.

REFRESH IT_1.

PERFORM get_zparam.

IF NOT gv_fromdat IS INITIAL.

PERFORM get_obj_class.

PERFORM get_cdhdr .

PERFORM get_cdpos .

PERFORM Populate_zdelta_edw.

ENDIF.

SELECT

ZDELTA_EDW~CLIENT

ZDELTA_EDW~TABNAME

ZDELTA_EDW~TABKEY

INTO (ST_2-F_1,

ST_2-F_2,

ST_2-F_3)

FROM

ZDELTA_EDW AS ZDELTA_EDW .

IT_1-F_1 = ST_2-F_1.

IT_1-F_2 = ST_2-F_2.

IT_1-F_3 = ST_2-F_3.

APPEND IT_1.

R_COUNT = R_COUNT + 1.

IF R_COUNT = R_MAX.

LOOP AT IT_1.

TRANSFER IT_1 TO DSN LENGTH 103.

ENDLOOP.

CLEAR IT_1.

REFRESH IT_1.

R_COUNT = 0.

ENDIF.

ENDSELECT.

LOOP AT IT_1.

TRANSFER IT_1 TO DSN LENGTH 103.

ENDLOOP.

CLOSE DATASET DSN.

&----

---

*& Form get_obj_class

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM get_obj_class .

SELECT object tabname FROM tcdob

INTO TABLE gt_tcdob

WHERE tabname

in ('EKKO','EKPO','EKET','EKPA').

ENDFORM. " get_obj_class

&----

---

*& Form get_CDHDR

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM get_cdhdr .

IF NOT gt_tcdob[] IS INITIAL.

SELECT objectclas

objectid

changenr

udate

INTO TABLE gt_cdhdr

FROM cdhdr

FOR ALL ENTRIES IN gt_tcdob

WHERE objectclas = gt_tcdob-object

AND udate between gv_fromdat and gv_todat.

ENDIF.

ENDFORM. " get_CDHDR

&----

---

*& Form get_CDPOS

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM get_cdpos .

IF NOT gt_cdhdr[] IS INITIAL.

SELECT objectclas

objectid

changenr

tabname

tabkey

INTO TABLE gt_cdpos

FROM cdpos

FOR ALL ENTRIES IN gt_cdhdr

WHERE objectclas = gt_cdhdr-objectclas

AND objectid = gt_cdhdr-objectid

AND changenr = gt_cdhdr-changenr

AND tabname

IN ('EKKO','EKPO','EKET','EKPA')

AND chngind IN ('I','U').

ENDIF.

ENDFORM. " get_CDPOS

&----

---

*& Form get_ZDELTA_EDW

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM Populate_zdelta_edw.

DELETE FROM zdelta_edw.

COMMIT WORK.

***

LOOP AT gt_cdpos INTO wa_cdpos.

CLEAR wa_zdelta_edw.

wa_zdelta_edw-tabname = wa_cdpos-tabname.

wa_zdelta_edw-tabkey = wa_cdpos-tabkey.

APPEND wa_zdelta_edw TO gt_zdelta_edw.

ENDLOOP.

SORT gt_zdelta_edw.

DELETE ADJACENT DUPLICATES

FROM gt_zdelta_edw

COMPARING tabname

tabkey.

Modify zdelta_edw FROM TABLE gt_zdelta_edw.

IF sy-subrc = 0.

COMMIT WORK.

ELSE.

ROLLBACK WORK.

ENDIF.

CLEAR wa_zdelta_edw.

REFRESH gt_zdelta_edw.

*Process EKBE and EKPO-AEDAT changes

CLEAR gt_ekpo.

REFRESH gt_ekpo.

• Fetch changes from EKPO

SELECT EBELN EBELP FROM EKPO

INTO TABLE gt_ekpo

WHERE

AEDAT between gv_fromdat and gv_todat.

CLEAR gt_ekbe.

REFRESH gt_ekbe.

• Fetch changes from EKBE

SELECT EBELN EBELP FROM EKBE

INTO TABLE gt_ekbe

WHERE BWART in ('101','102','122','543',

'544','641','642') and

CPUDT between gv_fromdat and gv_todat.

Loop at gt_ekbe into wa_ekbe.

CLEAR wa_ekpo.

wa_ekpo-ebeln = wa_ekbe-ebeln.

wa_ekpo-ebelp = wa_ekbe-ebelp.

APPEND wa_ekpo TO gt_ekpo.

clear wa_ekbe.

endloop.

CLEAR gt_ekbe.

REFRESH gt_ekbe.

Sort gt_ekpo By ebeln ebelp.

DELETE ADJACENT DUPLICATES FROM gt_ekpo COMPARING ebeln ebelp.

loop at gt_ekpo into wa_ekpo.

CLEAR wa_tabkey.

wa_tabkey-mandt = sy-mandt .

wa_tabkey-ebeln = wa_ekpo-ebeln.

wa_tabkey-ebelp = wa_ekpo-ebelp.

wa_zdelta_edw-tabname ='EKPO'.

wa_zdelta_edw-tabkey = wa_tabkey.

APPEND wa_zdelta_edw TO gt_zdelta_edw.

endloop.

Modify zdelta_edw FROM TABLE gt_zdelta_edw.

IF sy-subrc = 0.

COMMIT WORK.

ELSE.

ROLLBACK WORK.

ENDIF.

CLEAR gt_ekpo.

REFRESH gt_ekpo.

CLEAR wa_zdelta_edw.

REFRESH gt_zdelta_edw.

ENDFORM. " get_ZDELTA_EDW

• --> p1 text

• <-- p2 text

*& Form get_ZPARAM

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM get_zparam .

CONSTANTS : lc_paramtype TYPE zparam-paramtype

VALUE 'EDW',

lc_subtype TYPE zparam-subtype

VALUE 'PRESERVE',

lc_key1 TYPE zparam-key1

VALUE '....'.

DATA : lv_value1 TYPE zparam-value1.

DATA : lv_value2 TYPE zparam-value1.

SELECT SINGLE value1 value2

INTO (lv_value1,lv_value2)

FROM zparam

WHERE paramtype = lc_paramtype

AND subtype = lc_subtype

AND key1 = lc_key1.

IF sy-subrc EQ 0.

gv_fromdat = lv_value1.

gv_todat = lv_value2.

ENDIF.

ENDFORM. " read_parameters

[Former Member]

Hi,

use se30 to determine which part of your code takes the longest time to run.

kind regards, Rob Dielemans

[Former Member]

Anup,

Do a trace analysis using ST05 transaction

Procedure:

1. Activate trace in ST05

2. Run your program

3. Deactivate trace in ST05

4. View logs in ST05

5. analyze the log (see explain statements)

SQL Trace transaction ST05

The trace list has many lines that are not related to the SELECT statement in the ABAP program. This is because the execution of any ABAP program requires additional administrative SQL calls. To restrict the list output, use the filter introducing the trace list.

The trace list contains different SQL statements simultaneously related to the one SELECT statement in the ABAP program. This is because the R/3 Database Interface - a sophisticated component of the R/3 Application Server - maps every Open SQL statement to one or a series of physical database calls and brings it to execution. This mapping, crucial to R/3s performance, depends on the particular call and database system. For example, the SELECT-ENDSELECT loop on the SPFLI table in our test program is mapped to a sequence PREPARE-OPEN-FETCH of physical calls in an Oracle environment.

The WHERE clause in the trace list's SQL statement is different from the WHERE clause in the ABAP statement. This is because in an R/3 system, a client is a self-contained unit with separate master records and its own set of table data (in commercial, organizational, and technical terms). With ABAP, every Open SQL statement automatically executes within the correct client environment. For this reason, a condition with the actual client code is added to every WHERE clause if a client field is a component of the searched table.

To see a statement's execution plan, just position the cursor on the PREPARE statement and choose Explain SQL. A detailed explanation of the execution plan depends on the database system in use

[Former Member]

thanks Anirudha.....actually you have already cleared part of my doubt...like I have seen many sql trace result which is not related to ABAP program. Do I need to put any information in filter to restrict only ABAP Select Statement?

NB I have assign point.

[Former Member]

You can sort the SQL by time taken and concenterate on first five problamatic statement, in my experience it solves 95% of the problem.

[Former Member]

Ok Aniruddha I will try that. Thanks. And if in mean time you can give me any suggestion by looking my code..then it will be helpful....

Thanks

I assigned point.

[Former Member]

Hi ,

Can anybody give me some idea regarding the performance of IF-ELSEIF -ELSE statement. Suppose if we use some 60 ELSEIFs will it reduce the performance of my program. Is there any limitation on number of ELSEIFs. Please let me know its urgent.

Points will be rewarded.

Thanks & Regards,

S.Manjula

[Former Member]

> To restrict the list output, use the filter introducing the trace list.

No! What filter ???

Make it simpler use the SQL Trace Summary !!!!!!

<b>Go to 'Trace List -> Summarize Trace by SQL Statements' !!!!</b>

=> One line for ALL executions of a SQL statement, sorted by duration.

+ Slow statements have high values in MinTime/R (per record) => check explain

+ Identical executions should be avoided

Siegfried

<b></b>

[Former Member]

Hi Roy

chk these stmt's

SELECT ZDELTA_EDW_{CLIENT ZDELTA_EDW}TABNAME ZDELTA_EDW~TABKEY INTO (ST_2-F_1,ST_2-F_2,ST_2-F_3) FROM

ZDELTA_EDW AS ZDELTA_EDW .

IT_1-F_1 = ST_2-F_1.

IT_1-F_2 = ST_2-F_2.

IT_1-F_3 = ST_2-F_3.

APPEND IT_1.

use this one

SELECT ZDELTA_EDW_{CLIENT ZDELTA_EDW}TABNAME ZDELTA_EDW~TABKEY FROM ZDELTA_EDW into table it_1.

reward points to all helpful answers

kiran.M

[Former Member]

Hi Kiran,

Thank you so much for your reply....can you please go through once in my program...and give me more suggestion ....for example any "select", "loop" which I need to work on..

I assigned points....