topic Re: performance issue with COVP in Application Development and Automation Discussions

performance issue with COVP

shah_viraj — Wed, 29 Oct 2014 11:41:02 GMT

We are facing performance issue with below query :

SELECT belnr

buzei

wogbtr

wkgbtr

objnr

gjahr

bukrs

budat

refbn

refbk

refgj

APPENDING TABLE t_covp

FROM covp

FOR ALL ENTRIES IN t_aufk

WHERE lednr = '0'

AND objnr = t_aufk-objnr

AND awtyp IN ('MKPF', 'RMRP', 'AFRU').

T_AUFK contains single record and it takes around 1-2 minute to execute this query.

Re: performance issue with COVP

FredericGirod — Wed, 29 Oct 2014 11:53:41 GMT

Hi,

could you put the explain tree ? I don't see in your screenshot the estimate time by index .. did you run Oracle ?

regards

Fred

Re: performance issue with COVP

shah_viraj — Wed, 29 Oct 2014 12:04:59 GMT

Re: performance issue with COVP

shah_viraj — Wed, 29 Oct 2014 12:05:58 GMT

Re: performance issue with COVP

Former Member — Wed, 29 Oct 2014 12:12:13 GMT

HI Viraj Shah,

Here COVP is a database view in your select query first it will check for all entries based on the data it will joins the coep and cobk.... if there are more records in t_aufk the performence will be degraded by joining and for all entries.

Try these code it will may help to you.

SELECT belnr

buzei

wogbtr

wkgbtr

objnr

gjahr

bukrs

APPENDING TABLE t_coep

FROM coep

FOR ALL ENTRIES IN t_aufk

WHERE lednr = '0'

AND objnr = t_aufk-objnr

AND awtyp IN ('MKPF', 'RMRP', 'AFRU').

SELECT kokrs

belnr

budat

refbn

refbk

refgj

APPENDING TABLE t_cobk

FROM cobk

FOR ALL ENTRIES IN t_coep

WHERE kokrs = t_coep-kokrs

belnr = t_coep-belnr.

and the loop the internal tables t_coep and t_cobk into Final Resultant internal table t_final.

Re: performance issue with COVP

shah_viraj — Wed, 29 Oct 2014 12:56:49 GMT

With this also it is taking too long time to fetch COEP and COBK. What I noticed is that when I pass data manually to COEP via SE16N, it is giving output quickly.

Re: performance issue with COVP

shah_viraj — Wed, 29 Oct 2014 13:06:29 GMT

sorry my bad. Now it is working very fine. Many many thanks.

Re: performance issue with COVP

RaymondGiuseppi — Wed, 29 Oct 2014 13:10:05 GMT

Use an explicit JOIN so optimizer condescends to use COEP~1 and COBK~0 for better performance.

SELECT coep~belnr coep~buzei coep~wogbtr coep~wkgbtr coep~objnr coep~gjahr coep~bukrs
        cobk~budat cobk~refbn cobk~refbk cobk~refgj
     APPENDING CORRESPONDING FIELDS OF TABLE t_covp
     FROM coep
     JOIN cobk
       ON  cobk~kokrs EQ coep~kokrs
       AND cobk~belnr EQ coep~belnr
     FOR ALL entries IN t_aufk
     WHERE lednr = '0'
       AND coep~objnr = t_aufk-objnr
       AND cobk~awtyp IN ('MKPF', 'RMRP', 'AFRU').

Regards,

Raymond