topic Re: performance problem in Application Development and Automation Discussions

performance problem

Former Member — Fri, 23 Sep 2005 13:51:41 GMT

Hi all,

Iam reading data from 5 tables mara,marc,mbew,mlgn,mlgt

using inner joins and left outer join.It taking alot of time to process.

So, I request you that is there any fm to retrive data

from these tables instead of select statement.

regards

chandu.

Re: performance problem

Former Member — Fri, 23 Sep 2005 13:56:29 GMT

You can use logical database .MM have one logical database

Re: performance problem

Former Member — Fri, 23 Sep 2005 13:59:26 GMT

also it can deoend on how you use your select statements. Can you post the code?

Re: performance problem

Former Member — Fri, 23 Sep 2005 14:02:46 GMT

This is my code please check.

select fields FROM mara JOIN marc ON mara_{matnr = marc}matnr JOIN makt ON makt_{matnr = marc}matnr

JOIN mbew ON mbew_{matnr = marc}matnr

AND mbew_{bwkey = marc}werks

JOIN mlgn ON mlgn_{matnr = mbew}matnr

LEFT OUTER JOIN mlgt ON mlgt_{matnr = mlgn}matnr

AND mlgt_{lgnum = mlgn}lgnum

AND mlgt~lgtyp = s_lgtyp

AND mlgt~lgpla = s_lgpla

INTO CORRESPONDING FIELDS OF TABLE gt_data1

WHERE

mara~matnr IN s_matnr

AND marc~werks IN s_werks

AND mbew~bwkey IN s_bwkey

AND mbew~bklas IN s_bklas

AND marc~dismm IN s_dismm

AND marc~dispo IN s_dispo

AND makt~spras IN s_spras

AND mlgn~lgnum IN s_lgnum

Re: performance problem

Former Member — Fri, 23 Sep 2005 14:24:53 GMT

Your code looks OK. Since the primary key for all of these tables is the material number, what does the contents of the select option s_matnr look like? Are there "NE"? Is it empty? If it is empty, that's likely your problem.

Rob

Re: performance problem

Former Member — Fri, 23 Sep 2005 14:29:06 GMT

Hi Rob,

You are correct my s_matnr is empty.

So is there any tip for that

thanks

chandu

Re: performance problem

Former Member — Fri, 23 Sep 2005 14:47:43 GMT

Mara has secondary indices, but I don't think you can use any of them. The other tables have secondary indices as well (MARC-WERKS for example, although this one probably wouldn't be selective enough to help).

I think you need to change the join around so that you are selecting from one of the other tables using a secondary index and then joining to the other tables.

But be careful that you get all of the results you expect.

Rob

Re: performance problem

Former Member — Fri, 23 Sep 2005 15:03:09 GMT

Hi Sai,

Don't try to do this in one select. I would join mara, marc, makt, mbew in one select, and then read mlgn and mlgt in a second select, using the results of the first select as the key. Then you combine the two tables in ABAP, rather than relying on the database to do it for you.

Working with internal tables is very fast.

SELECT fields

FROM mara

JOIN marc

ON mara_{matnr = marc}matnr

JOIN makt

ON makt_{matnr = marc}matnr

JOIN mbew

ON mbew_{matnr = marc}matnr AND

mbew_{bwkey = marc}werks

INTO I_MARA_DATA

WHERE mara~matnr IN s_matnr

AND marc~werks IN s_werks

AND mbew~bwkey IN s_bwkey

AND mbew~bklas IN s_bklas

AND marc~dismm IN s_dismm

AND marc~dispo IN s_dispo

AND makt~spras IN s_spras

SELECT fields FROM mlgn as mlgn

JOIN mlgt as mlgt

ON mlgt_{matnr = mlgn}matnr

mlgt_{lgnum = mlgn}lgnum

INTO CORRESPONDING FIELDS OF TABLE i_mlgt_data

FOR ALL ENTRIES IN i_mara_data

WHERE mlgt~matnr = i_mara_data-matnr

and mlgt~lgtyp = s_lgtyp

and mlgt~lgpla = s_lgpla.

This gives you two internal tables that can be logically 'joined' using MATNR as the common key.

I don't know what you're program is trying to do, but you can either create an itab with all of the combined data in it, OR, you can loop one of the itabs, and do a lookup on the other one.

For example:

sort i_gt_data1 by matnr.

loop at i_gt_data2 into st_gt_data2.

read table i_gt_data1 into st_gt_data1

with key matnr = st_gt_data2-matnr

binary search.

if sy-subrc ne 0.

clear st_gt_data2.

endif.

At this point, you have one logical row of data that
you would have had using one select

endloop.

I hope this makes sense. The basic premise is that you could have one itab for EACH table, and then just do a table lookup when necessary. This is SUBSTANTIALLY faster than most other techniques.

Good luck,

John

Message was edited by: John Welch

Re: performance problem

Former Member — Fri, 23 Sep 2005 15:38:35 GMT

Chandu - look at the following select:


SELECT marc~matnr
  FROM marc JOIN mara
            ON mara~matnr = marc~matnr JOIN
       makt ON makt~matnr = marc~matnr JOIN
       mbew ON mbew~matnr = marc~matnr AND
               mbew~bwkey = marc~werks JOIN
       mlgn ON mlgn~matnr = marc~matnr LEFT OUTER JOIN
       mlgt ON mlgt~matnr = mlgn~matnr AND
               mlgt~lgnum = mlgn~lgnum
    INTO CORRESPONDING FIELDS OF TABLE gt_data1
    WHERE marc~dispo IN s_dispo
      AND marc~werks IN s_werks
      AND mara~matnr IN s_matnr
      AND mbew~bwkey IN s_bwkey
      AND mbew~bklas IN s_bklas
      AND marc~dismm IN s_dismm
      AND makt~spras IN s_spras
      AND mlgn~lgnum IN s_lgnum.

The primary table is MARC and it has an index on DISPO and WERKS, so if these select options are not empty, it should run a more quickly. I'm not sure the select will return what you want, so please check it out very carefully.

Note that I took out


AND mlgt~lgtyp = s_lgtyp
AND mlgt~lgpla = s_lgpla

from the join condition. I don't think it worked.

Rob]

Message was edited by: Rob Burbank

Re: performance problem

Former Member — Fri, 23 Sep 2005 15:42:46 GMT

John - this may speed up the select somewhat, but I think that the main problem is that the s_matnr select-option is empty.

Rob

Re: performance problem

Former Member — Fri, 23 Sep 2005 15:51:50 GMT

Hello Sai,

Can you specify which fields you select in "Select Fields... ".

Join the tables only if you require data from all of them. Else if table is to just restrict entry of input on selection screen you can use sub query .

But this will depend on what fields you want to select.

Cheers

Re: performance problem

Former Member — Fri, 23 Sep 2005 16:09:36 GMT

Thanks alot for your replies.

I will check all the proposed solutions on monday

and i will let you know.

thanks

chandu