Performance issue with mard table

[Former Member]

Hello All

I am using the following two query.

1.

SELECT mara~matnr werks xchar mtart matkl meins trame

umlmc mara~lvorm as lvorm_mara

marc~lvorm as lvorm_marc

INTO CORRESPONDING FIELDS OF TABLE t_mat

FROM mara INNER JOIN marc

ON mara_{matnr = marc}matnr

WHERE mara~matnr IN matnr

AND werks IN werks

AND mtart IN matart

AND matkl IN matkla

AND ekgrp IN ekgrup

and spart in spart.

if t_mat[] is not initial.

2.

SELECT matnr werks lgort

labst umlme insme einme speme retme lvorm

INTO (collector-matnr, collector-werks, collector-lgort,

collector-labst, collector-umlme, collector-insme,

collector-einme, collector-speme, collector-retme,

collector-lvorm)

FROM mard

FOR ALL ENTRIES IN t_mat

WHERE matnr = t_mat-matnr

AND werks = t_mat-werks

AND lgort = lgort-LOW

AND LABST <> 0

AND UMLME <> 0.

endif.

Now here in the Table t_mat abt 180000 record are there. when I am using the table t_mat for all entries with respect to t_mat and using all primery key itself.

the performance of program is dull. It is giving to time out Error

Can some One suggest some solution.

Regards

Swati namdev

[Former Member]

few suggessions:

1. First avoid "corresponding fields of " statement. Use "into table" instead.

2. Use open cursor to fetch data

DATA: s_cursor TYPE cursor.

OPEN CURSOR WITH HOLD s_cursor FOR

SELECT mara~matnr werks xchar mtart matkl meins trame

umlmc mara~lvorm as lvorm_mara

marc~lvorm as lvorm_marc

FROM mara INNER JOIN marc

ON mara_{matnr = marc}matnr

WHERE mara~matnr IN matnr

AND werks IN werks

AND mtart IN matart

AND matkl IN matkla

AND ekgrp IN ekgrup

and spart in spart.

if t_mat[] is not initial.

DO.

FETCH NEXT CURSOR s_cursor

APPENDING

TABLE t_mat

PACKAGE SIZE '2000'.

IF sy-subrc <> 0.

CLOSE CURSOR s_cursor.

EXIT.

ENDIF.

ENDDO.

Edited by: Ravi Kumar Singh on Apr 7, 2008 11:07 AM

[Former Member]

few suggessions:

1. First avoid "corresponding fields of " statement. Use "into table" instead.

2. Use open cursor to fetch data

DATA: s_cursor TYPE cursor.

OPEN CURSOR WITH HOLD s_cursor FOR

SELECT mara~matnr werks xchar mtart matkl meins trame

umlmc mara~lvorm as lvorm_mara

marc~lvorm as lvorm_marc

FROM mara INNER JOIN marc

ON mara_{matnr = marc}matnr

WHERE mara~matnr IN matnr

AND werks IN werks

AND mtart IN matart

AND matkl IN matkla

AND ekgrp IN ekgrup

and spart in spart.

if t_mat[] is not initial.

DO.

FETCH NEXT CURSOR s_cursor

APPENDING

TABLE t_mat

PACKAGE SIZE '2000'.

IF sy-subrc <> 0.

CLOSE CURSOR s_cursor.

EXIT.

ENDIF.

ENDDO.

Edited by: Ravi Kumar Singh on Apr 7, 2008 11:07 AM

[ThomasZloch]

try adding the join on MARD to the first selection, or if that's not an option declare t_mat as a sorted table with unique key matnr and werks.

Cheers

Thomas