performance issue with COVP

[shah_viraj]

Hi

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.

[FredericGirod]

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

[shah_viraj]

[shah_viraj]

[Former Member]

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.

[shah_viraj]

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.

[shah_viraj]

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

[RaymondGiuseppi]

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