Possible way to reduce the time frame when selecting the data from COVP view .

[Former Member]

Hi,

In a custom report we are fetching the data from COVP view using the below query.

SELECT KOKRS

           BELNR

           BUZEI

           WOGBTR

           OBJNR

           GJAHR

           KSTAR

           BEKNZ

           OWAER

           EBELN

           EBELP

           PERNR

           OBJNR_N1

           BUKRS

           BLDAT

           BUDAT       FROM COVP INTO WA_COVP

        FOR ALL ENTRIES IN IST_CSKS

           WHERE BUKRS = P_BUKRS

             AND BUDAT IN S_DATE

             AND OBJNR = IST_CSKS-OBJNR

             AND KSTAR IN R_HKONT.

       INSERT WA_COVP INTO TABLE IST_COVP.

     ENDSELECT.

So comparing to INTO TABLE we are having better performance with SELECT & ENDSELECT . Is there any chance to reduce a little more for this select query. We are  using MSSQL server and there are 3 indexes on the table COEP. Can we utilize the indexes available as below for better performance.

So could you please help me on this.

Regards,

Praveen

[arthur_alvesteixeira]

Praveen,

your current where condition has 4 fields, maybe 3 came from selection screen, so it's necessary to check if these values always come from selection screen (if they are obligatory).

The other field is the object number, and you have a index with 2 fields:

TIMESTMP	Time created (Greenwich Meantime)
OBJNR	Object number

You can use this index (if the others 3 fields can be blank sometimes), but you need to add the TIMESTMP field.

The other index to check is the 02:

Object number

Cost Element

Fiscal Year

Period

Partner object (always filled)

The object number is the first field, and you need to fill the others values.

[Former Member]

Just in case: don't forget to check if IST_CSKS is empty and remove the duplicates from it.

So comparing to INTO TABLE we are having better performance with SELECT & ENDSELECT

That's weird. I've been told that SELECT ... INTO TABLE should perform better that SELECT  ... ENDSELECT loop: that's a memory mamagement thing. Can you please tell me how have you gained such a result and how big was the difference between the two approaches?

Thank you.

[Former Member]

Hi ,

Thank you alex and arthur . Now the execution time frame came from 5 mins to less than 1 minute.

Instead of date range I had restricted to enter same year range in the input and taking that year into local variable LV_YEAR.  After that I had modified the code as below .

** get year from the date ***

   DATA: LV_YEAR TYPE GJAHR.

   LV_YEAR = S_DATE-LOW+0(4) .

** GET THE DATA FOR THE WHOLE YEAR

SELECT KOKRS

            BELNR

            BUZEI

            WOGBTR

            OBJNR

            GJAHR

            KSTAR

            BEKNZ

            OWAER

            EBELN

            EBELP

            PERNR

            OBJNR_N1

            BUKRS

            BLDAT

            BUDAT  FROM COVP INTO TABLE IST_COVP

             WHERE GJAHR = LV_YEAR

             AND BUKRS = P_BUKRS

             AND KSTAR IN R_HKONT.

**** DELETE THE DATA WHICH ARE NOT IN THE GIVEN DATE RANGE I.E. IF USER ENTERED 01.01.2007 TO 31.03.2007  WE WILL DELETE

*** THE RECORDS WHICH ARE NOT IN THIS DATE RANGE.

     DELETE IST_COVP WHERE BUDAT NOT IN S_DATE.

     FIELD-SYMBOLS: <FS_COVP> TYPE TY_COVP.

** DELETE THE RECORDS WHICH OBJNR IS NOT IN IST_CSKS TABLE .

     LOOP AT IST_COVP ASSIGNING <FS_COVP>.

       READ TABLE IST_CSKS INTO WA_CSKS WITH KEY OBJNR = <FS_COVP>-OBJNR.

       IF SY-SUBRC NE 0.

         <FS_COVP>-BELNR = 'X'.

       ENDIF.

     ENDLOOP.

     DELETE IST_COVP WHERE BELNR = 'X'.

Regards,

Praveen chitturi