Performance Issue Problem

[Former Member]

SELECT PSPHI

A~PSPNR

B~POST1

A~POST1

INTO TABLE T_PRPS

FROM PRPS AS A

JOIN PROJ AS B

ON PSPHI = B~PSPNR

WHERE PSPHI IN S_PSPHI.

user wont enter the project number.so first select query vl fetch the entire table reocrds bcoz s_psphi is the select-option.

after getting the records from prps i m passing these records into AFPO table to fetch network numbers.

IF T_PRPS[] IS NOT INITIAL.

SELECT AUFNR INTO TABLE T_AFPO

FROM AFPO

FOR ALL ENTRIES IN T_PRPS

WHERE PROJN = T_PRPS-PSPNR

%_HINTS ORACLE 'INDEX("AFPO" "AFPO~002")'.

ENDIF.

after getting records from AFPO i passed these records to AUFK to fetch text and other data.

IF T_AFPO[] IS NOT INITIAL.

SELECT AUFNR

PSPEL

KTEXT

INTO TABLE T_AUFK

FROM AUFK

FOR ALL ENTRIES IN T_AFPO

WHERE AUFNR = T_AFPO-AUFNR

ENDIF.

The second select query is taking long time.

At first the 2nd and 3rd select queries are written by using JOIN,but performance issue came.so i split the JOIN condition.After that also i got same performance problem.Then i used Secondary index syntax.time reduced to 17mins,but user wants it less time.Please any body vl gIve solution for this as soon as possible.

My analysis is the field which is ther in where condition is not a primary key filed and records in the database table also hav large in amount.

Thanks&Regards,

R.P.Sastry

[ThomasZloch]

did you do a runtime analysis via SE30 to find out which statement actually takes so long to execute?

[Former Member]

Remove Inner Joins and use

FOR ALL ENTRIES

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Feb 20, 2008 9:28 AM

[Former Member]

Can you please check below FM may useful to read the data

2054_READ_WBS_STRUCTURE

AIAL_WBSELMS_READ

AIPK_TOP_WBS_ELEMENTS_READ

AIPK_WBS_ELEMENTS_DATES_READ

CJGC_READ_COMPONENTS_FOR_WBS

CJPN_WBS_ELEM_READ_DECO

CK_F_KEKOKEY_WBS_READ

CK_F_WBS_KALNR_READ

CK_F_WBS_KALNR_READ_BY_MAT

CM_PRST_READ_BY_WBS

PSBW_READ_INFO_FROM_WBS

[Former Member]

It looks like the problem is entirely due to the large amount of data. You are extracting a large number of entries from PRPS and then using all of the results to get orders from AFPO using an non-unique secondary index. this will take time and there's not much you can do about this except run it in the background.

Or enter the project number.

Rob

Edited by: Rob Burbank on Feb 20, 2008 9:27 AM

Edited by: Rob Burbank on Feb 20, 2008 9:29 AM