topic Re: Inner Join Performance in Application Development and Automation Discussions

Inner Join Performance

Former Member — Mon, 10 Jul 2006 07:58:56 GMT

Hi Folks,

I m using this particular inner join.

The performance is quite good in development system but in Prod. System performance become very low.

SELECT G_{MBLNR G}BWART G_{XAUTO G}MENGE G_{DMBTR F}BUDAT F~XBLNR

INTO TABLE IT_TEMP

FROM

MSEG AS G

INNER JOIN

MKPF AS F

ON G_{MBLNR = F}MBLNR AND

G_{MJAHR = F}MJAHR

WHERE G~BWART IN ('101','102','601','602','309','321','322') AND

G~MATNR EQ IT_MARA-MATNR

AND G~WERKS IN PLANT

AND G~LGORT IN S_LGORT

AND F~BUDAT GE VARDATELOW

AND F~BUDAT LE VARDATEHIGH.

What i can do to make the performance high.

Thanks

Sachin

Re: Inner Join Performance

Former Member — Mon, 10 Jul 2006 08:10:43 GMT

i think this code is ok. if it is possible add more key field other wise it looks very sound....

performance problem is due to another part of code...

ya in devlopment system it is work fine becoz in production there are mass of data.

Re: Inner Join Performance

Former Member — Mon, 10 Jul 2006 08:41:33 GMT

hi, i dunno the precedings of this code. but is there a select option for any of the key fields of these tables say MBLNR on selection screen? I think that's the only way to improve the performance. Also you have given a range of BWART so to say in ur code. it's very much identical to creating a range. Instead of that if u can create an internal table for the same and then use for all entries in ur slect statement, it'll still improve the performance.

Re: Inner Join Performance

Former Member — Mon, 10 Jul 2006 08:52:03 GMT

Hi Sachin,

1)I had doubt regarding the statement in WHERE conidition in your select query(G~MATNR EQ IT_MARA-MATNR).

2)If you are specifying this from internal table value then your SELECT will be in LOOP...ENDLOOP.

3)If this the case, then avoid SELECT statement between LOOP...ENDLOOP.This makes your performance very low.

4)If this the case, then write SELECT query with out G~MATNR EQ IT_MARA-MATNR in where condition and Have this condition on IT_TEMP after retriving the data.

Thanks,

Vinay

Re: Inner Join Performance

abdul_hakim — Mon, 10 Jul 2006 08:56:38 GMT

this is because the data load will be more on production system than the development system.

so split your joins and replace it with FOR ALL ENTRIES for better performance...

Cheers,

Abdul Hakim

Re: Inner Join Performance

Former Member — Mon, 10 Jul 2006 08:58:04 GMT

Hi sachin,

1. The sql is fine, but

i would personally suggest the following :

2. Since MSEG and MKPF are

HEAVY TRANSACTION TABLES,

3. We should AVOID Using joins, on such tables.

4. The ideal way is to,

a) fetch first data from any one table. eg MKPF

in an internal table

b) then fetch data from MSEG

using FOR ALL ENTRIES concept.

4. INTERNAL TABLE CONCEPT

-- Thats the BEAUTY of ABAP Lanaguage,

which SAP has introduced.

-- Instead of pulling data from database,

in ONE SHOT,

we should use INTERNAL TABLES,

as an intermediate,

and get the data in STEPS,

(instead of getting using EXTENSIVE JOINS,

and taxing the database)

regards,

amit m.

Re: Inner Join Performance

Former Member — Mon, 10 Jul 2006 09:05:04 GMT

Hi Folks,

Thanks for ur prompt replies.

I m using it like this

SELECT MBLNR BWART XAUTO MENGE DMBTR

INTO CORRESPONDING FIELDS OF IT_TEMP

FOR ALL ENTRIES IN IT_MARA FROM MSEG

WHERE

BWART IN ('101','102','601','602','309','321','322') AND

MATNR = IT_MARA-MATNR AND

WERKS IN PLANT AND

LGORT IN S_LGORT.

ENDSELECT.

It_mara contains the matnr.

it gives the error:

Unable to interpret FOR.

I m using 4.6B

Is it valid in 4.6 B.

Thanks

Sachin

Re: Inner Join Performance

Former Member — Mon, 10 Jul 2006 09:07:12 GMT

Hai Sachin

Check the following Code modification

if not IT_MARA[] is initial.

SELECT MBLNR

MJAHR

BWART

XAUTO

MENGE

DMBTR

INTO TABLE IT_MSEG

WHERE BWART IN ('101','102','601','602','309','321','322') AND

MATNR EQ IT_MARA-MATNR AND

WERKS IN PLANT AND

LGORT IN S_LGORT.

IF SY-SUBRC = 0.

SORT IT_MSEG BY MBLNR MJAHR.

ENDIF.

IF NOT IT_MSEG[] IS INITIAL.

SELECT BUDAT

XBLNR

INTO TABLE IT_mkpf

FROM MKPF

FOR ALL ENTRIES IN IT_MSEG

WHERE MBLNR = IT_MSEG-MBLNR AND

MJAHR = IT_MSEG-MJAHR AND

BUDAT BETWEEN VARDATELOW AND VARDATEHIGH.

ENDIF.

endif.

Thanks & regards

Sreenivasulu P

Message was edited by: Sreenivasulu Ponnadi

Re: Inner Join Performance

Former Member — Mon, 10 Jul 2006 09:08:29 GMT

Hi again,

1. it should be some thing like this.

SELECT MBLNR BWART XAUTO MENGE DMBTR

FROM MSEG

INTO CORRESPONDING FIELDS OF IT_TEMP

FOR ALL ENTRIES IN IT_MARA

WHERE

MATNR = IT_MARA-MATNR

AND BWART IN ('101','102','601','602','309','321','322') AND WERKS IN PLANT

AND LGORT IN S_LGORT.

regards,

amit m.

Re: Inner Join Performance

Former Member — Mon, 10 Jul 2006 09:09:19 GMT

Use it

SELECT MBLNR BWART XAUTO MENGE DMBTR

INTO CORRESPONDING FIELDS OF table IT_TEMP

FROM MSEG

FOR ALL ENTRIES IN IT_MARA

WHERE

BWART IN ('101','102','601','602','309','321','322') AND

MATNR = IT_MARA-MATNR AND

WERKS IN PLANT AND

LGORT IN S_LGORT.

Re: Inner Join Performance

vrkrishnaraju_mudunuri — Mon, 10 Jul 2006 10:36:12 GMT

Hi ,

Use the fields in the where condition in this order shown below

MATNR

WERKS

LGORT

BWART

and BUDAT

Now run this statement in PROD, and check in SE30 the perfomance of the statement.

Regards,

Krishnaraju

Re: Inner Join Performance

Former Member — Mon, 10 Jul 2006 10:54:32 GMT

Hi Sachin,

See that u select the fields in Select stmt in database table order.It has a maximum impact on performance.

Regards,

Kiran B

Re: Inner Join Performance

Former Member — Mon, 10 Jul 2006 11:07:19 GMT

Hi ,

<b>
SELECT G~MBLNR G~BWART G~XAUTO G~MENGE G~DMBTR F~BUDAT F~XBLNR
INTO TABLE IT_TEMP
FROM
MSEG AS G
INNER JOIN
MKPF AS F
ON G~MBLNR = F~MBLNR AND
G~MJAHR = F~MJAHR
WHERE G~BWART IN R_BWART AND
G~MATNR EQ IT_MARA-MATNR--> Dont Use For all Entries ( try to make use of Ranges ) like marnt in r_matnr.
AND G~WERKS IN PLANT
AND G~LGORT IN S_LGORT
AND F~BUDAT GE VARDATELOW
AND F~BUDAT LE VARDATEHIGH.</b>

and try to use / create secondary Indexess.

Re: Inner Join Performance

Former Member — Mon, 10 Jul 2006 11:35:23 GMT

Hi Folks,

Even i have break up join into select statement but still performance is slow.

Even i have created index on MSEG on the required fields.

Is there any thing that i have to mention in ABAP for indexes.

Thanks

Sachin

Re: Inner Join Performance

Former Member — Mon, 10 Jul 2006 13:42:05 GMT

Well, you are already using most of the fields in the secondary index on material, so I'd get rid of any index you created for this one program.

Check to see if PLANT or S_LGORT are empty. If either of them is, this could be your problem.

Rob