topic Re: Performance issue in Application Development and Automation Discussions

Performance issue

Former Member — Mon, 02 Mar 2009 05:47:46 GMT

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.

Re: Performance issue

Former Member — Mon, 02 Mar 2009 05:53:32 GMT

Hi Soniya,

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

Regards,

Bhumika

Re: Performance issue

Former Member — Mon, 02 Mar 2009 05:56:13 GMT

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.

Re: Performance issue

suredarreddy_pulimamidi — Mon, 02 Mar 2009 05:57:05 GMT

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.

Re: Performance issue

former_member585060 — Mon, 02 Mar 2009 05:58:45 GMT

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

Re: Performance issue

Former Member — Mon, 02 Mar 2009 05:59:40 GMT

Hi Sonia,

use both the key fields,

mblnr and mjahr.

please try and reply.

Re: Performance issue

Former Member — Mon, 02 Mar 2009 06:00:26 GMT

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

Re: Performance issue

Former Member — Tue, 03 Mar 2009 14:30:31 GMT

Moved to correct forum

Re: Performance issue

Former Member — Tue, 03 Mar 2009 15:49:54 GMT


> 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

Re: Performance issue

Former Member — Tue, 03 Mar 2009 16:41:54 GMT

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

Re: Performance issue

Former Member — Tue, 03 Mar 2009 17:47:01 GMT

Try

WHERE  bktxt  GT  space

Re: Performance issue

Former Member — Tue, 03 Mar 2009 18:12:26 GMT


> Try WHERE  bktxt  GT  space

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

Rob

Re: Performance issue

Former Member — Wed, 04 Mar 2009 11:54:48 GMT

This message was moderated.

Re: Performance issue

Former Member — Thu, 05 Mar 2009 04:49:47 GMT

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 .

Re: Performance issue

Former Member — Thu, 05 Mar 2009 10:15:39 GMT

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

no it will not ....

Re: Performance issue

Former Member — Thu, 05 Mar 2009 11:59:56 GMT

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