performance issue

[Former Member]

SELECT matnr mtart maktx FROM olr3marackt

INTO TABLE i_mara

WHERE spras EQ sy-langu

AND werks EQ pwerks

AND mtart IN s_mtart.

*Get MSEG data

IF i_mara[] IS NOT INITIAL.

SELECT mblnr

mjahr

zeile

bwart

matnr

werks

lgort

sobkz

bwtar

menge

wempf

bukrs

grund

INTO TABLE i_temp_mseg

FROM mseg

FOR ALL ENTRIES IN i_mara

WHERE bwart IN s_bwart AND

matnr EQ i_mara-matnr AND

werks EQ p_werks AND

lgort IN s_lgort .

IF sy-subrc EQ 0.

DELETE ADJACENT DUPLICATES FROM i_temp_mseg.

ENDIF.

ENDIF.

*Get MKPF data

IF i_temp_mseg[] IS NOT INITIAL.

SELECT mblnr

mjahr

budat

usnam

INTO TABLE i_temp_mkpf

FROM mkpf

FOR ALL ENTRIES IN i_temp_mseg

WHERE mblnr = i_temp_mseg-mblnr

AND mjahr = i_temp_mseg-mjahr

AND budat IN s_budat.

IF sy-subrc EQ 0.

DELETE ADJACENT DUPLICATES FROM i_temp_mkpf.

ENDIF.

ENDIF.

the data these tables are fetching consists of large amount of data so its taking lot of time , can any one help me out in this how to increase the performance for this two select statements.

[Former Member]

Hello,

First check whether you are using sequence of fields in where condition as per any of table index.If not corrct the field sequence.

If possible check in the system whether there is any view whcih already exists for mseg & mkpf,use that instead of table access.

Thanks.

Mark points if helpful.

[rui_nunes]

Hi,

You don't need to DELETE ADJACENT DUPLICATES. With SELECT... FOR ALL ENTRIES "Duplicates are discarded from the result set".

If you use view V_MKPF you can retrieve data from both tables at the same time. This would dramatically increase the performance. If this view doesn't have all the required fields, do a where used list, selecting views for both tables and compare the results. Check to see if any of the standard views is returning the data you need.

If you don't find any and this program is to be executed often, you can create a new view and use it instead.

If that's not an option you can still perform an INNER JOIN between both tables using the common primary keys.

Hope this helps,

Rui

[messier31]

Hi Chaya,

Also to add

instead of selecting records in first select and then deleting duplicates records..you can better go for SELECT DISTINCT..

This will definitely help...

Enjoy SAP.

Pankaj Singh

[Former Member]

Hi,

follow below logic

select from mkpf with budat since secondary index

is defined on this.

SELECT mblnr

mjahr

budat

usnam

INTO TABLE i_temp_mkpf

FROM mkpf

where budat IN s_budat.

IF sy-subrc EQ 0.

DELETE ADJACENT DUPLICATES FROM i_temp_mkpf.

ENDIF.

if not i_temp_mkpf[] is initial.

SELECT mblnr

mjahr

zeile

bwart

matnr

werks

lgort

sobkz

bwtar

menge

wempf

bukrs

grund

INTO TABLE i_temp_mseg

FROM mseg

FOR ALL ENTRIES IN i_temp_mkpf.

WHERE mblnr = i_temp_mkpf-mblnr

mjahr = i_temp_mkpf-mjahr

matnr EQ i_mara-matnr AND

werks EQ p_werks AND

lgort IN s_lgort and

bwart IN s_bwart .

IF sy-subrc EQ 0.

DELETE ADJACENT DUPLICATES FROM i_temp_mseg.

ENDIF.

if not i_temp_mseg[] is initial.

SELECT matnr mtart maktx FROM olr3marackt

INTO TABLE i_mara

for all entries in i_temp_mseg

WHERE matnr = i_temp_mseg-matnr

AND werks = i_temp_mseg-werks

AND mtart IN s_mtart

and spras EQ sy-langu .

endif.

besides above point see below steps

<b> reduce database time</b>

1) Remove corresponding from select satement

2) Remove * from select

3) Select field in sequence as defined in database

4) Avoid unnecessary selects

i.e check for internal table not initial

5) Use all entries and sort table by key fields

7) Try to use secondary index when you don't have

full key.

FORM SUB_SELECTION_AUFKTAB.

if not it_plant[] is initial.

it_plant1[] = it_plant[].

sort it_plant1 by werks.

delete adjacent duplicates from it_plant1 comparing werks

SELECT AUFNR KTEXT USER4 OBJNR INTO CORRESPONDING FIELDS OF TABLE I_AUFKTAB

FROM AUFK

FOR ALL ENTRIES IN it_plant1

WHERE AUFNR IN S_AUFNR AND

KTEXT IN S_KTEXT AND

• WERKS IN S_WERKS AND

AUART IN S_AUART AND

USER4 IN S_USER4 AND

werks eq it_plant1-werks.

free it_plant1.

Endif.

ENDFORM. "SUB_SELECTION_AUFKTAB

2) <b>reduce abap time</b>

1) Remove selects from loop and use binary search

2) Modify internal table use transporting option

3) Avoid nested loop . Use read table and loop at itab

from sy-tabix statement.

4) free intrenal table memory wnen table is not

required for further processing.

3)<b>Reduce sytsem time</b>Regards

1) perform give types of formal parameters

perform add using a b.

form add a type i b type i.

Regards

amole

[Clemenss]

Hi,

depending on your needs, consider selection of data first from

- VAPMA Sales index material

- CKMI1 Material index financial

- BSIM secondary material index

That might solve performance issues.

Regards,

Clemens