topic Re: Performance optimisation for GLPCA-MARA joins in Application Development and Automation Discussions

Performance optimisation for GLPCA-MARA joins

Former Member — Tue, 21 Jan 2014 12:20:31 GMT

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

Re: Performance optimisation for GLPCA-MARA joins

ThomasZloch — Tue, 21 Jan 2014 12:31:21 GMT

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

Re: Performance optimisation for GLPCA-MARA joins

Former Member — Tue, 21 Jan 2014 12:49:34 GMT

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.

Re: Performance optimisation for GLPCA-MARA joins

deependra_shekhawat3 — Tue, 21 Jan 2014 13:01:20 GMT

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

Re: Performance optimisation for GLPCA-MARA joins

ThomasZloch — Tue, 21 Jan 2014 13:46:45 GMT

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

Re: Performance optimisation for GLPCA-MARA joins

Former Member — Wed, 12 Feb 2014 22:46:27 GMT

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.

Re: Performance optimisation for GLPCA-MARA joins

Former Member — Tue, 18 Feb 2014 07:08:32 GMT

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