Inner Join How performance can be improved

[Former Member]

Hi All,

There is a inner join for fetching data from mkpf and mseg. Now the query is like shown below.what all things can be done to improve the performance.

SELECT

b~mblnr

b~mjahr

b~zeile

b~bwart

b~matnr

b~werks

b~lgort

b~charg

b~sobkz

b~lifnr

b~shkzg

b~waers

b~dmbtr

b~menge

b~meins

b~ebeln

b~ebelp

b~kostl

b~aufnr

b~bukrs

a~budat

INTO TABLE imseg

FROM mkpf AS a INNER JOIN mseg AS b

ON a_{mblnr = b}mblnr AND a_{mjahr = b}mjahr

CLIENT SPECIFIED

WHERE a~mandt EQ sy-mandt

AND a~budat IN s_budat

AND a~vgart IN s_vgart

AND a~blart IN s_blart.

DELETE imseg WHERE NOT ( sobkz EQ 'K' ) .

DELETE imseg WHERE NOT ( bwart = '411' OR bwart = '412' ) .

IF NOT ( s_matnr IS INITIAL ) .

DELETE imseg WHERE NOT ( matnr IN s_matnr ) .

ENDIF.

IF NOT ( s_werks IS INITIAL ) .

DELETE imseg WHERE NOT ( werks IN s_werks ) .

ENDIF.

IF NOT ( s_lifnr IS INITIAL ) .

DELETE imseg WHERE NOT ( lifnr IN s_lifnr ) .

ENDIF.

IF NOT ( p_bukrs IS INITIAL ) .

DELETE imseg WHERE NOT ( bukrs EQ p_bukrs ) .

ENDIF.

Key fields are not available in the selection screen.

Thanks in advance,

Shyam

[Former Member]

Hi All,

There is a inner join for fetching data from mkpf and mseg. Now the query is like shown below.what all things can be done to improve the performance.

SELECT

b~mblnr

b~mjahr

b~zeile

b~bwart

b~matnr

b~werks

b~lgort

b~charg

b~sobkz

b~lifnr

b~shkzg

b~waers

b~dmbtr

b~menge

b~meins

b~ebeln

b~ebelp

b~kostl

b~aufnr

b~bukrs

a~budat

INTO TABLE imseg

FROM mkpf AS a INNER JOIN mseg AS b

ON a_{mblnr = b}mblnr AND a_{mjahr = b}mjahr

CLIENT SPECIFIED

WHERE a~mandt EQ sy-mandt

AND a~budat IN s_budat

AND a~vgart IN s_vgart

AND a~blart IN s_blart.

DELETE imseg WHERE NOT ( sobkz EQ 'K' ) .

DELETE imseg WHERE NOT ( bwart = '411' OR bwart = '412' ) .

IF NOT ( s_matnr IS INITIAL ) .

DELETE imseg WHERE NOT ( matnr IN s_matnr ) .

ENDIF.

IF NOT ( s_werks IS INITIAL ) .

DELETE imseg WHERE NOT ( werks IN s_werks ) .

ENDIF.

IF NOT ( s_lifnr IS INITIAL ) .

DELETE imseg WHERE NOT ( lifnr IN s_lifnr ) .

ENDIF.

IF NOT ( p_bukrs IS INITIAL ) .

DELETE imseg WHERE NOT ( bukrs EQ p_bukrs ) .

ENDIF.

Key fields are not available in the selection screen.

Thanks in advance,

Shyam

[Former Member]

Hi,

Instead of innerjoin, better u use 'for all entries'...

Then see, the performance.

Performance wise it will effective than innerjoin.

Regards,

Kumar

[Former Member]

Earlier in the program it was using FOR ALL ENTRIES but it was taking a lot of time so as these two table sare header and item level table i thought of putting a join b/w them.but problem is as key fields are not available i think it will take a lot of time.That i have to check after moving it to Quality system.Before that i want to know can any more can be done on this for improving the performance

[Former Member]

Hi Syam,

Have you checked whether they have used the statement to check internal table is empty or not?

Example : If <itab> is not initial.

As you know, if it is not there, FOR ALL ENTRIES will fetech entire data.

What you said is right, iF THEY ARE NOT PROPER KEY FIELD, OBVIOUS, THERE WILL NOT BE ANY ALTERNATIVE TO REDUSE THE TIME IN FETCHING THE DATA. So, hope, there is no alternative.

Because the tables you have mentioned are big db tables, Ask your functional guy for any alternative tables for the same fields.

Thanks

Manju.

If you think helpful, reward points.

[seshatalpasai_madala]

Hi,

One more thing you can do is to create a database view (Which always uses inner join) and then use the technical settings to buffer the data of this view.

Regards,

Sesh

[Bharathi_j]

Hi Shyam,

First thing check whether for all entries is coded properly i.e check wther the driver internal table is checked for not initial condition.

Next thing if u cant achive through for all entries it is better to approach DBA people for secondary index creation.

[Former Member]

There is an index on MKPF for BUDAT, so I think the only thing you can do is to make sure that s_budat is not empty and also contains a small enough range so that not too much data is retrieved.

Rob

[christian_wohlfahrt]

Hi Shyam!

I guess, you had once a select for MKPF and a 'for all entries' for MSEG. Depending on the usual selection (the way the potential selection criteria are used), you can try the other way: first select MSEG and then select MKPF 'for all entries'. This second select is fast, it's just a question, if MSEG can be accessed in a good way.

There is an index M for MSEG (with fields MANDT, MATNR, WERKS, LGORT, BWART, SOBKZ). Currently you give BWART and SOBKZ, the other fields are optional, LGORT you don't specify at all. I think it's quite easy to get all possible LGORT (out from table T001L).

When you report variant restricts MATNR and WERKS, you can fill the fields LGORT, BWART, SOBKZ and you have a 'full' index access for MSEG.

When MATNR (or WERKS) aren't specified, this will be slow, because the result is a full table access for MSEG.

In case MATNR, WERKS are sometimes specified, you will get best results with a join - then the DB should decide itself if an access for MKPF or an access for MSEG will be done first. At least, that's the case when there is a cost based optimizer working for the SQL translation.

Regards,

Christian

[Former Member]

I would change the following conditions, move them to where clause.

I would define range for bwart, fill in with your vlaues to EXCLUDE them and then use in where condiiton.

DELETE imseg WHERE NOT ( sobkz EQ 'K' ) .

DELETE imseg WHERE NOT ( bwart = '411' OR bwart = '412' ) .

[Former Member]

Hi,

As u have practising joins ,what ever code u have given wil work fine but this wil take lot of time to fetch from the Database.So,instead u can use <b>for all entries</b>. This makes the performance more efficient.

Regards,

Sharath

[Former Member]

Please use for all entries.That will increase the performance.

Regards,

kumar.