Inner Join Performance

[Former Member]

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

[Former Member]

Well,, a slight change can be done.

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 <b>BETWEEN VARDATELOW AND VARDATEHIGH</b>.

[dani_mn]

HI,

You can use secondary index to make performance better and also try to use 'FOR ALL-ENTRIES' instead of join.

Regards,

Wasim Ahmed

[Former Member]

Hi Wasim,

For which fields should i make secondery index and for which table.

Thanks

-

---

Sachin

[Former Member]

Hi,

Here are some tips..

Always Join from a ‘MASTER’ table to a ‘DETAIL’ table.

You cannot make an Inner Join good or bad by the order in which the

tables are specified in the FROM and JOIN clause.

The database optomizer ignores your order and ensures

that the data is accessed in the most efficient manner.

Regards,

Tanveer.

<b>Please mark helpful answers</b>