PERFORMANCE ANALYSE

[Former Member]

hi experts,

I am doing a task which need the following specification.

SELECTION SCREEN CONTAINS :

SETTLEMENT PERIOD : <PERIO>

FISCAL YEAR : <GJAHR>

PLANT : <WERKS>

selection of KOKRS BELNR BUZEI PERIO WTGBTR OBJNR WERKS KSTAR GJAHR FROM COEP(BASED ON SELECTION SCREEN ELEMENT) AND

AUFNR KDAUF OBJNR KOKRS FROM AUFK.

JOINING CONDITION : KOKRS AND OBJNR.

I USED INDIVIDULAL SELECT QUERY:

QUERY 1: FETECHING DATA FROM COEP BASED ON SELECTION SCREEN DATA.

QUERY 2 : FECTECH DATA FROM AUFK FOR ALL ENTRIES IN COEP (FROM QUERY 1).

BUT THE PERFORMANCE IS VERY POOR. PLS GIVE ME A IDEA TO IMPROVE THE PERFORMANCE.

IF I CHOOSE INNERJOIN QUERY IT'S ALSO VERY POOR. PLS GIVE ME IDEA.

Thanks ya.

[andreas_mann3]

try to fill fields of indices (1, 2 or 4, look se11) of table coep

A.

[Former Member]

Can you post the SELECTs you used?

Rob

[Former Member]

SELECT KOKRS BELNR BUZEI PERIO WTGBTR OBJNR WERKS KSTAR GJAHR

FROM COEP

INTO TABLE T_COEP

WHERE PERIO IN S_PERIO

AND GJAHR IN S_BUGJAH

AND WERKS IN S_WERKS

AND ( KSTAR EQ '0000530310' OR KSTAR EQ '0000530311' )

AND OBJNR LIKE 'OR_____9______'.

IF NOT T_COEP[] IS INITIAL.

SELECT AUFNR AUART KDAUF OBJNR KOKRS PHAS0

PHAS1 PHAS2 PHAS3 IDAT1 IDAT2 IDAT3

FROM AUFK

INTO TABLE T_AUFK

FOR ALL ENTRIES IN T_COEP

WHERE OBJNR EQ T_COEP-OBJNR

AND KOKRS EQ T_COEP-KOKRS.

IF NOT T_AUFK[] IS INITIAL.

SELECT AUFNR POSNR MATNR

FROM AFPO

INTO TABLE T_AFPO

FOR ALL ENTRIES IN T_AUFK

WHERE AUFNR EQ T_AUFK-AUFNR.

IF NOT T_AFPO[] IS INITIAL.

SELECT MATNR MAKTX

FROM MAKT

INTO TABLE T_MAKT

FOR ALL ENTRIES IN T_AFPO

WHERE MATNR EQ T_AFPO-MATNR.

ENDIF.

ENDIF.

ENDIF.

Performance Result :

Abap : 2%

DB : 97%

System : 1%.

I want to improve performance. Pls help me to do.

Thank u .

[Former Member]

You need to re-think how you're doing this. The first two SELECTs don't use any index.

Rob

[Clemenss]

Hi Murugan,

I think you should change the date selection sequence: COEP is for costing item data - you will have many more than AUFK order header data.

I'm sure you will find some selection criteria for AUFK(i.e. BUKRS, begin and end date) and then try view COVP to het relevant COEP data based on OBJNR.

Regards,

Clemens

[Former Member]

Hi!

You can analyse the performance in two ways.

1. Use SE30 transaction. Here you will be able to find out where exactly the performance degrades. i.e. at database level or program execution and accordingly you can make the changes.

2. Since you are using FOR ALL ENTRIES in second query and still its taking lot of time that means no. of records fetched into internal table (one used in for all entries) is very large. Hence it takes time. One solution to limit the number of records is to use where clause. For eg. You can avoid fetching old data values by placing constraints on the date fields if any.

3. Ensure that the first query selects the records based on the selection criteria and no extra records are selected.

[Former Member]

Hi,

To improve the database performance, follow these instruction after discussing it with ur lead.

1- Go to ST05 & run the SQL trace and scroll down and see which areas are in red color. These are the SQL statements which are taking much time.

2- I think so in ur case, the statement u have mentioned above taking more time.

3- Now see the tables, and note down the fields which u are using in where clause.

4- go to SE 11, and click on indexes and create secondary index for these tables and active it. It will ask you a change request.

Hope this will increase ur program performance.

Thanks,

Sal.

Message was edited by:

Sal Khan