topic Re: Inner Join How performance can be improved in Application Development and Automation Discussions

Inner Join How performance can be improved

Former Member — Tue, 28 Nov 2006 09:17:32 GMT

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

Re: Inner Join How performance can be improved

Former Member — Tue, 28 Nov 2006 09:24:11 GMT

Hi,

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

Then see, the performance.

Performance wise it will effective than innerjoin.

Regards,

Kumar

Re: Inner Join How performance can be improved

Former Member — Tue, 28 Nov 2006 09:30:00 GMT

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

Re: Inner Join How performance can be improved

Former Member — Tue, 28 Nov 2006 09:39:14 GMT

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.

Re: Inner Join How performance can be improved

seshatalpasai_madala — Tue, 28 Nov 2006 10:46:50 GMT

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

Re: Inner Join How performance can be improved

Bharathi_j — Tue, 28 Nov 2006 11:51:08 GMT

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.

Re: Inner Join How performance can be improved

Former Member — Tue, 28 Nov 2006 14:13:04 GMT

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

Re: Inner Join How performance can be improved

christian_wohlfahrt — Tue, 28 Nov 2006 16:22:49 GMT

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

Re: Inner Join How performance can be improved

Former Member — Tue, 28 Nov 2006 17:49:47 GMT

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' ) .

Re: Inner Join How performance can be improved

Former Member — Wed, 29 Nov 2006 06:12:19 GMT

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

Re: Inner Join How performance can be improved

Former Member — Wed, 29 Nov 2006 09:25:56 GMT

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

Regards,

kumar.