topic Re: Performnace issue in Application Development and Automation Discussions

Performnace issue

Former Member — Wed, 13 Feb 2008 12:44:09 GMT

Hi Gurus,

i m fetching data from A005 table and according to selection criteria there is 223,233 entries in the table because of that taking so much time.......and giving runtime error.......

this is code

SELECT MARA_{MATNR MAKT}MAKTX MARA_{MATKL MARC}WERKS

INTO TABLE IT_MARC

FROM MARC

INNER JOIN MARA ON MARC_{MATNR = MARA}MATNR

INNER JOIN MAKT ON MARC_{MATNR = MAKT}MATNR

WHERE MARC~MATNR IN S_MATNR AND

MARC~WERKS IN S_WERKS AND

MAKT~SPRAS = 'EN' AND

MARA~XCHPF = 'X' AND

MATKL IN S_MATKL.

IF IT_MARC[] IS NOT INITIAL.

SELECT KUNNR MATNR DATBI DATAB KNUMH

FROM A005

INTO TABLE IT_A005

FOR ALL ENTRIES IN IT_MARC

WHERE KSCHL = 'ZMRP' AND

VKORG = '1000' AND

VTWEG = '10' AND

KUNNR IN S_KUNNR AND

MATNR = IT_MARC-MATNR AND

datab le s_datbi-low and

DATBI ge s_datbi-low.

***

ENDIF.

during debugiing report taking time to fetch data from A005 table

IF IT_A005[] IS NOT INITIAL.

SELECT KNUMH KBETR

FROM KONP

INTO TABLE IT_KONP

FOR ALL ENTRIES IN IT_A005

WHERE KNUMH = IT_A005-KNUMH.

ENDIF.

give me some solution

Re: Performnace issue

ThomasZloch — Wed, 13 Feb 2008 12:47:31 GMT

you need to include primary key field A005-KAPPL for quicker access. It should always have the same value for your selected data, so once you find out which one it is, you can put that literal into your select statement.

Cheers

Thomas

Edit: possible values for KAPPL are stored in table T681A.

Re: Performnace issue

Former Member — Wed, 13 Feb 2008 12:48:47 GMT

Create the Indexes for respective database tables, then your problem solved

Re: Performnace issue

Former Member — Wed, 13 Feb 2008 12:50:30 GMT

Hi,

The number of records in table IT_A005 is too high to use the FOR ALL ENTRIES OPTION. One solution would ne to copy say 500 records of table IT_A005 and do the same. So loop over table IT_A005, and after each 500 records execute the selection.

Regards,

John.

Re: Performnace issue

ThomasZloch — Wed, 13 Feb 2008 12:51:17 GMT

> Create the Indexes for respective database tables, then your problem solved

Bollocks. Always check first whether you can use an existing, fully qualified primary or secondary key access path.

Re: Performnace issue

Former Member — Wed, 13 Feb 2008 12:55:52 GMT

Use KAPPL in where condition.

Field order should be the same in where condition as in the

table ..

field DATAB is after DATBI in the table ..

change the where condition accordingly ..

Re: Performnace issue

Former Member — Wed, 13 Feb 2008 17:45:04 GMT

Absolutely no guarantees, but you can try:

TABLES a005.

RANGES: r_kappl FOR a005-kappl.

* Get all values of KAPPL into a range table.

MOVE 'EQ' TO r_kappl-option.
MOVE 'I'  TO r_kappl-sign.
SELECT kappl FROM  t681a
  INTO r_kappl-low.
  APPEND r_kappl.
ENDSELECT.

SELECT kunnr matnr datbi datab knumh
  FROM a005
  INTO TABLE it_a005
  FOR ALL ENTRIES IN it_marc
  WHERE kappl IN r_kappl
    AND kschl = 'ZMRP'
    AND vkorg = '1000'
    AND vtweg = '10'
    AND kunnr IN s_kunnr
    AND matnr = it_marc-matnr
    AND datab LE s_datbi-low
    AND datbi GE s_datbi-low.

See if it can use the primary index.

Rob

Re: Performnace issue

Former Member — Fri, 15 Feb 2008 04:21:49 GMT

thnks to all of u i hv tried as all of u said......................

A005 not allowing me to cerate index.......................

i hv tried with all primary key and maintain sequence as in the table and removed for all entries* but still taking so much time