Problem with COEP table

[Former Member]

Hi All,

I was trying to create a markup report which basically is to fetch COEP table which has tons of records for a specific cost center group or cost values for a period and a fiscal year,but most of the time this report is pulled for all the costcenter group.

Initially we use to get ROLLBACK SEGMENT issue where basis people were aware of the problem and they said they did there best, then we changed the code using FETCH CURSOR by fetching 1/2 gb of records at a time which really worked for 2 months that means our report got run in less than 20 minutes for 2 times which earlier use to run for hours together.

Now this month the report is running for 24hrs and I am not able to get how to solve this issue. Please let me know what best alternate way we can code for the below code.

OPEN CURSOR WITH HOLD db_cursor FOR
  SELECT kokrs belnr buzei perio wrttp versn
         objnr gjahr wkgbtr kstar vrgng beknz
         parob1 bukrs pbukrs
   FROM  coep  BYPASSING BUFFER
   WHERE kokrs EQ p_carea
         AND   perio IN s_fprd
         AND   gjahr EQ p_fyear
         AND   wrttp EQ c_valuetype
         AND   versn EQ c_version.
  DO.
    FETCH NEXT CURSOR db_cursor
    INTO CORRESPONDING FIELDS OF TABLE it_coep_tmp
    PACKAGE SIZE g_package_size.
    IF sy-subrc NE 0.
      CLOSE CURSOR db_cursor.
      EXIT.
    ELSE.
      APPEND LINES OF it_coep_tmp TO it_coep.
      REFRESH it_coep_tmp.
    ENDIF.
  ENDDO.

Edited by: Thomas Zloch on Sep 7, 2010 9:29 PM

[Former Member]

You talk about large amounts of data for cost centers or cost center groups, but I don't see cost center (OBJNR) as part of the WHERE. You should do a little more work before posting here - like run a trace to see what index/access method is used.

Rob

[Former Member]

hey Rob,

I had removed this from the where clause as in the forum while searching on this was suggested that in the where clause if we have many values for a variable [like OBJNR had many values] its better not to include in SQL once the Selection is done we can delete whichever is not required.

If not the query with OBJNR in the where clause will take lot of time to search on database side.This was seen practically in my case so removed this and then used delete OBJNR..

Thanks and Regards

[Former Member]

I'd put it back in and use FOR ALL ENTRIES on OBJNR. Bur remember to include LEDNR in the WHERE as well so that the index (COEP~1) can be used.

Rob

[Former Member]

Check and see if a query like this gives you the correct functionality and speed.

SELECT c~kokrs
       c~belnr
       c~buzei
       c~perio
       c~wrttp
       c~versn
       c~objnr
       c~gjahr
       c~wkgbtr
       c~kstar
       c~vrgng
       c~beknz
       c~parob1
       c~bukrs
       c~pbukrs
    FROM       tka02 AS a
    INNER JOIN cosp  AS b
    ON a~bukrs EQ b~bukrs
    INNER JOIN coep AS c
    ON  b~lednr EQ c~lednr
    AND b~objnr EQ c~objnr
    AND b~gjahr EQ c~gjahr
    AND b~wrttp EQ c~wrttp
    AND b~versn EQ c~versn
    AND b~kstar EQ c~kstar
    AND b~hrkft EQ c~hrkft
    AND b~vrgng EQ c~vrgng
    AND b~vbund EQ c~vbund
    AND b~pargb EQ c~pargb
    AND b~beknz EQ c~beknz
    AND b~twaer EQ c~twaer
    AND b~perbl EQ c~perbl
    AND b~meinh EQ c~meinh
    INTO TABLE t_coep
    PACKAGE SIZE 1000
    WHERE a~kokrs EQ p_carea
    AND   b~gjahr EQ p_fyear
    AND   b~wrttp EQ c_valuetype
    AND   b~versn EQ c_version
    AND   c~perio IN s_fprd.
  
*  Process the records in table t_coep 
  
ENDSELECT.