Performance issue

[Former Member]

Hello,

I have performance problem in my program.

Can anybody suggest me how can i change following query for better performance?

SELECT a_{mblnr a}mjahr a~bktxt

b~matnr lgort charg erfmg

INTO TABLE i_mseg1

FROM ( mkpf AS a

INNER JOIN mseg AS b ON

a_{mblnr = b}mblnr )

WHERE a~mjahr = p_mjahr

AND vgart = 'WA'

AND bktxt NE space

AND lgort = 'SCRP'.

regards,

soni.

[Former Member]

Hi Soniya,

Instead of using joins try to use "FOR ALL ENTRIES" & see if this resolves your issue.

Regards,

Bhumika

[Former Member]

Hi Soniya,

Hope the below query will solve your problem.

DATA : lr_mblnr type range of mseg-mblnr.

SELECT a_{mblnr a}mjahr a~bktxt

b_{matnr b}lgort b_{charg b}erfmg

INTO TABLE i_mseg1

FROM ( mkpf AS a

INNER JOIN mseg AS b ON

a_{mblnr = b}mblnr )

WHERE a~mblnr in lr_mblnr

and a~mjahr = p_mjahr

AND a~vgart = 'WA'

AND a~bktxt NE space

AND b~lgort = 'SCRP'.

if this query did not solve your problem, retrieve the required from MKPF and use the FOR ALL ENTRIES and retreive the data from MSEG.

Regards,

Phani.

[suredarreddy_pulimamidi]

Hi,

Try to use all the key fields in the select statement and aviod the join,use for all entires,before using for all entires check entires are there or not and sort the internal tables.

Regards,

Surendar Reddy.

[former_member585060]

Hi,

Fetch the code without bktxt NE in where condition, the Delete all the records which r bktxt = ' '.

SELECT a~mblnr 
             a~mjahr 
             a~bktxt
             b~matnr 
             b~lgort 
             b~charg 
             b~erfmg
                          INTO TABLE i_mseg1
                          FROM  mkpf AS a INNER JOIN mseg AS b ON a~mblnr = b~mblnr 
                          WHERE a~mjahr = p_mjahr
                          AND vgart = 'WA'
                          AND lgort = 'SCRP'.

DELETE i_mseg1 WHERE bktxt = ' '.

Regards

Bala Krishna

[Former Member]

Hi Sonia,

use both the key fields,

mblnr and mjahr.

please try and reply.

[Former Member]

Soniya,

Remove this condition from WHERE clause 'a~bktxt NE space' and try deleting the entries from the internal table i_mseg1 for bktxt eq space after ur fetch.

delete i_mseg1 where bktxt = space.

Thanks,

Dhanalakshmi M S

[Former Member]

>

> Remove this condition from WHERE clause 'a~bktxt NE space' and try deleting the entries from the internal table i_mseg1 for bktxt eq space after ur fetch.
> 
> delete i_mseg1 where bktxt = space.

Since BKTXT is not used in any index, this will only increase execution time.

Rob

[Former Member]

Moved to correct forum

[Former Member]

sometimes I am really wondering what you expect ...

Assume you go to a bookshop and you want a book of autor, whos last name starts with 'D*' and

the first name is not 'John'. And there is tiger on red background on the front cover.

How fast will that work?

Do you really expect that by removing the 'first name is not 'John' it will become faster ????

If you understand that then you will understand that there is no fast solution for your task. Either you can add something to your WHERE condition, something which really restricts the result or check

... until you find the book with the tiger.

And please don't distribute points for recommendations which can never help, test first.

Siegfried

[Former Member]

Try

WHERE  bktxt  GT  space

[Former Member]

>

> Try 
WHERE  bktxt  GT  space

From a performance standpoint, I don't think there would be any difference.

Rob

[Former Member]

This message was moderated.

[Former Member]

Hi ,

for perofrmance u can remove the join

Also try to incorporate POSTING DATE in this query .

SELECT mblnr mjahr bktxt

INTO TABLE TABLE1

FROM mkpf

WHERE mjahr = p_mjahr

AND bktxt NE space

place loop on that or .

loop at TABLE1.

SELECT mblnr mjahr matnr lgort charg erfmg

appending TABLE i_mseg1

FROM mseg

WHERE mjahr = TABLE1-mjahr

and mblnr = TABLE1-mblnr

AND vgart = 'WA'

AND lgort = 'SCRP'.

if sy-subrc = 0 .

i_mseg1-bktxt = TABLE1-bktxt.

modify i_mesg1 transporting bktxt where mblnr = TABLE1-MBLNR and MJHAR = TABLE1-MJHAR.

endif.

endloop.

Please currect the code i have writen it in hurry but it will solve ur perforance problem .

[Former Member]

> Please currect the code i have writen it in hurry but it will solve ur perforance problem .

no it will not ....

[Former Member]

few points.1.What is the requirement for this selection?u require all the Material Documents in the storage location 'SCRP'.'?' inclusive of inward and outward movements? Is p_mjahr mandatory field?

To improve Performance

1.always use index fields in the WHERE condition,especially large tables like MKPF and MSEG

2.do u have index on budat..if so try to provide a~budat IN s_year(fill the date range for year p_mjahr)

3.Join condition should include max possible entries say.. AND a_{mjahr = b}mjahr

Cheers