Performance issue

[Former Member]

Hi Guys,

Here are the two select statements one is sale order and other is purchase order.these two statements are taking lot of time to get execute.can any one suggest how to increase performance.

SELECT VBELN

AUDAT

AUART

VSBED

VDATU

BSTNK

KUNNR

INTO TABLE IT_VBAK

FROM VBAK

WHERE VBELN IN PVBELN

AND ERDAT IN PERDAT

AND AUART IN PLFART

AND VKORG IN PVKORG

AND KUNNR IN PKUNAG.

• AND AUART <> 'AG'.

IF SY-SUBRC = 0.

SORT IT_VBAK BY VBELN.

DELETE IT_VBAK WHERE AUART = 'AG'.

ENDIF.

CLEAR IT_EKKO.

REFRESH IT_EKKO.

SELECT EBELN

RESWK

BEDAT

INTO TABLE IT_EKKO

FROM EKKO

WHERE EBELN IN PVBELN

AND BSTYP = 'F'

AND BSART = 'NB'

AND BEDAT IN PERDAT

AND RESWK IN PVSTEL.

IF NOT IT_EKKO[] IS INITIAL.

PERFORM M_GET_EKPO_DATA.

ENDIF.

Thanks

Vikranth Khimavath

[Former Member]

Hi,

If p_vblen is initial try to use secindary index on

vbak .

If p_ebeln is initial try to use secondary index on

ekko .

Regards

Amole

[Former Member]

hi vikranth,

how did u declare it_vbak and it_ekko???

check the order in which u have declared the fields in the internal tables.

if order is not same give

select...

into corresponding fields of table it_vbak(or)it_ekko.

Are pvbeln,pauart...parameters or select-options

bcoz if they r parameters u need to give

where vbeln = p_vbeln...etc

Message was edited by: Priya

[Former Member]

Hi priya

i declared both internal tables in the correct order as it was in database tables.but still iam facing the same problem.

Thanks

Vikranth

[Former Member]

Hi,

Is p_vbeln,p_ebeln is mandatory field in your

select?

Regards

Amole

[Former Member]

Hi rob and amole,

It is not the mandatory field that is why iam facing the problem if it is a mandatory field then it could not have a problem.

Thanks

Vikranth Khimavath

[Former Member]

You can either tell the users (or functional person) that it should be mandatory or they should be prepared to wait. Let them decide.

Since there is a secondary index on ERDAT, you can tell them that either the document number or creation date must be entered (or they wait).

Rob

Message was edited by: Rob Burbank

[Former Member]

Make sure PVBELN is not empty.

Rob

[Former Member]

Hi Vikranth,

Try to write field names in SELECT as in the ORDER of occurence in database table. Internal table field names should also come as the order in SELECT statement. These two also effects peformance factor.

Thanks,

Vinay

[Former Member]

even i did that vinay but still iam facing the same problem.

Thanks

Vikranth Khimavath

[Former Member]

HI,

if you are not using primary or secondary

index your program performance will not

improve.

Regards

Amole

[Former Member]

Hi Khimavath Vikranth ,

<b>First option:</b>

Sales Order Select Query:

I'm assuming PVBELN, PERDAT, PLFART, PLFART, PVKORG, PKUNAG are PARAMETERS.

SELECT VBELN

AUDAT

AUART

VSBED

VDATU

BSTNK

KUNNR

INTO TABLE IT_VBAK

FROM VBAK

<u><b>Ur statement:</b></u>

WHERE VBELN IN PVBELN

AND ERDAT IN PERDAT

AND AUART IN PLFART

AND VKORG IN PVKORG

AND KUNNR IN PKUNAG.

• AND AUART <> 'AG'.

<u><b>Suggested Code:</b></u>

WHERE VBELN = PVBELN

AND ERDAT = PERDAT

AND AUART = PLFART

AND VKORG = PVKORG

AND KUNNR = PKUNAG.

• AND AUART <> 'AG'.

IF SY-SUBRC = 0.

SORT IT_VBAK BY VBELN.

DELETE IT_VBAK WHERE AUART = 'AG'.

ENDIF.

If all are SELECT-OPTION:

In where class maintain the same order what u give (PVBELN, PERDAT, PLFART, PLFART, PVKORG, PKUNAG) selection screen order.

Use the same logic for Second Query also.

<b>Second option:</b>

RANGES: RA_AUART for VBAK-AUART.

RA_AUART-sign = 'E'.

RA_AUART-option = 'EQ'.

RA_AUART-LOW = 'AG'.

APPEND RA_AUART.

SELECT VBELN

AUDAT

AUART

VSBED

VDATU

BSTNK

KUNNR

INTO TABLE IT_VBAK

FROM VBAK

WHERE VBELN IN PVBELN

AND ERDAT IN PERDAT

AND AUART IN PLFART

AND VKORG IN PVKORG

AND KUNNR IN PKUNAG.

<i><b>AND AUART IN RA_AUART</b></i>.

- Selvapandian Arunachalam