How to improve the performance of the program while using COVP database View?

[former_member569532]

Hi Experts,

I  am using the below statement to get the data from COVP  view  .But my program is taking more time for execution.Kindly advice  on how to improve the performance of the program while using COVP view.If we use COVP view in any program,that program is taking more time.

   SELECT KOKRS CPUDT BUDAT

                   SUM( MBGBTR ) SUM( WKGBTR ) SUM( WOGBTR )

            FROM   COVP

            INTO  (COVP-KOKRS, COVP-CPUDT, COVP-BUDAT,

                   COVP-MBGBTR, COVP-WKGBTR, COVP-WOGBTR)

            WHERE  LEDNR = '00'

            AND    OBJNR = PRPS-OBJNR

            AND    VERSN = '000'

            AND    WRTTP = '04'

            AND    VRGNG IN WF_VRGNG

            AND    GJAHR IN WF_GJAHR

            AND    CPUDT BETWEEN WF_DATE_FR AND WF_DATE_TO

            AND    ORGVG IN WF_ORGVG

            AND    BELTP = 1        "Debit type: Costs and credit-side

            AND    EBELN = EKKO-EBELN

            AND    EBELP = EKPO-EBELP

            AND    BLART NE 'ZE'

            AND    ( ZEKKN = 0 OR ZEKKN = EKKN-ZEKKN )

            AND    WKGBTR <> 0

            GROUP  BY KOKRS CPUDT BUDAT.

               CHECK COVP-CPUDT > WF_DATE_FR OR

                     ( COVP-CPUDT = WF_DATE_FR AND

                       COVP-BUDAT >= WF_BDATE ).

                   ADD COVP-MBGBTR TO WF_ACT_QTY.

                   IF COVP-KOKRS = '0001'.

                      ADD COVP-WKGBTR TO WF_ACT_AMT.

                   ELSE.

                      ADD COVP-WOGBTR TO WF_ACT_AMT.

                   ENDIF.

            ENDSELECT.

Thanks in advance,

Anusha.

[former_member186413]

don't use select loop! using SELECT .... FROM .... INTO CORRESPONDING FIELD OF TABLE .... WHERE....

[arindam_m]

Hi,

Do the aggregate functions of after selection. Thus avoiding the SELECT.. ENDSELECT construct as well as aggregate functions.

Cheers,

Arindam

[saranwin]

Hi Anu,

As said by Hai Wung and Arindam don't use SELECT and END SELECT. Collect all the values in internal table and loop the internal table to SUM up the values.

This will improve the select query performance.

If you still have performance issue use std FM AD15_COVP_READ_MULTI to select the values.

Regards,

Saravanan

[Former Member]

Hi,

   Dont use select...endselect instead use INTO TABLE. Do additions or manipulations after the select statement...Make use of an internal table to retreive data and do manipulations...

[Former Member]

Hello,

Please check if COSS/COSP tables give you enough detail.

These tables are a lot more faster because they summarize the quantity/value fields periodically.

Pedro Melo

[venkat_aileni]

Hi-

As advised by everyone, don't use select-endselect and avoid using aggregate functions in queries. This may solve your problem

-Venkat

[Former Member]

Hi,

Reading from DB id mainly affected by your where clause. If it does not hit any index available on table then it is going to take long time to read. Check your where clause and check available index in system first and manipulate accordingly. Creating secondary index is next option.

Also select - endselect should be avoided, do calculations after fetch. Do not put load on DB for doing calculations.

Regards,

Koustubh

[former_member569532]

Hi All,

Thank you for all replies,I have tried the inner join between coep  and cobk tables  and removed all select endselects ,but there is no significant improvement in performance.Is there any way to  get the coep document number form ekko table data.Kindly any one advise ,how to get covp-belnr by using ekko data.

Thanks in advance,

Anusha

[venkat_aileni]

Hi Anu,

Try this:

Based on EKKO entries retrieve values from EKBE table(EBELN, EBELP, ZEKKN, VGABE, GJAHR, BELNR, BUZEI) by giving PO number.

Once you get this details write a query on COVP where BELNR = EKBE-BELNR AND BUZEI = EKBE-BUZEI.

Thanks.

-Venkat

[Former Member]

Hi Anu,

Your where clause seems to be an issue. Actually in the DB , Optimizer which helps to pick the right index will not work for you as you have mentioned many negative statements like NE. ( Not equal to ).

You can have a simple select query from the table , Then you can do all the necessary operations like deletion of unwanted records etc etc.

Thanks,

AJ.