Performance optimisation for GLPCA-MARA joins

[Former Member]

Hi All,

I need to fetch data from multiples tables and the chunk of data is coming from GLPCA and MARA and display it in

an ALV.

The expected records is around 1 million.

I'm joining both these tables using the field MATNR as its the only key field in MARA, however I did not

get any significant improvement in the performance.

Can someone suggest me any  other performance optimisation technique.

Thanks,

Faiz

[Former Member]

Hi All,

I need to fetch data from multiples tables and the chunk of data is coming from GLPCA and MARA and display it in

an ALV.

The expected records is around 1 million.

I'm joining both these tables using the field MATNR as its the only key field in MARA, however I did not

get any significant improvement in the performance.

Can someone suggest me any  other performance optimisation technique.

Thanks,

Faiz

[ThomasZloch]

1 million records is too much for ALV display, you are likely going to blow the available memory allocation. I assume you are not planning to look at that much data, do you actually want to download it?

MATNR is key field of MARA, but not of GLPCA. I assume GLPCA is the leading table in your join, but what is your WHERE-conditions? Please paste your join-statement for further inspection.

Thomas

[Former Member]

Hi Thomas. Yes, I'll be displaying  around 1000 records in the ALV and the total records will be downloaded in a file.

My query looks something like this:-

  SELECT <field1..field2..>
  FROM        glpca
  INTO TABLE lt_glpca
  WHERE rbukrs IN s_rbukrs
  AND   racct  IN s_racct
  AND   ryear  IN s_ryear
  AND   poper  IN s_poper
  AND   blart  IN s_blart
  AND   werks  IN s_werks
  AND   matnr  IN s_matnr
  AND   vkbur  IN s_vkbur
  AND   kunnr  IN s_kunnr.
  IF sy-subrc = 0.

  ENDIF.
  IF lt_glpca[] IS NOT INITIAL.
    SELECT<field1...field2..>
    FROM mara
    INTO TABLE lt_mara
    FOR ALL ENTRIES IN lt_glpca
    WHERE matnr = lt_glpca-matnr
    AND   zzdivision IN s_zzdiv
    AND   zzbusunit  IN s_zzbunt.
    IF sy-subrc <> 0.

    ENDIF.
  ENDIF.

[deependra_shekhawat3]

Hi Rahman,

As thomas suggested, matnr is not a key field in GLPCA. There is one more approach that you can use before selecting data from MARA as below :

Data : lit_glpca Type standard table of glpca. "whateaver is type of lt_glpca

  SELECT <field1..field2..>

  FROM        glpca

  INTO TABLE lt_glpca

  WHERE rbukrs IN s_rbukrs

  AND   racct  IN s_racct

  AND   ryear  IN s_ryear

  AND   poper  IN s_poper

  AND   blart  IN s_blart

  AND   werks  IN s_werks

  AND   matnr  IN s_matnr

  AND   vkbur  IN s_vkbur

  AND   kunnr  IN s_kunnr.

  IF sy-subrc = 0.

  ENDIF.
  IF lt_glpca[] IS NOT INITIAL.

refresh lit_glpca.

lit_glpca [] = lt_glpca[].

sort lit_glpca BY matnr zzdivision zzbusunit.

delete adjcent duplicate from lit_glpca comparing matnr zzdivision zzbusunit.
    SELECT<field1...field2..>
    FROM mara
    INTO TABLE lt_mara
    FOR ALL ENTRIES IN lit_glpca
  WHERE matnr = lit_glpca-matnr        "Use limited matnr of new local table
    AND   zzdivision IN s_zzdiv
  AND zzbusunit IN s_zzbunt.
    IF sy-subrc <> 0.

    ENDIF.
  ENDIF.

Try with this. i think it could be helpful for you.

Thanks

Deependra

[ThomasZloch]

You can very well join GLPCA and MARA via MATNR, however then you need WHERE-conditions that effectively reduce the GLPCA entries.

You have a bunch of selection ranges that might contain anything/nothing at runtime, so the response time is quite unpredictable by just looking at your code.

You should study the available indexes for GLPCA and see if you can add WHERE-conditions that make effective use of one of these indexes. Also look into technically forcing the user to enter narrow selection criteria before executing the report.

Thomas

[Former Member]

Faizur,

To join MARA & GLPCA tables efficiently, you need to have atleast one common key field. if not, Create Secondary Index in GLPCA table with MATNR as field & then join both the tables with MATNR . It should work, i think.

Thanks,

Kishor.

[Former Member]

First check whether the scanning of index is full scan or not. if its full scan then probably create secondary index w.r.t relevant fields in GLPCA table.

Note : GLPCA is very very biggest table in r/3.

Regards,

Vadamalai