Performance Issue while fetching from COSP table

[Former Member]

Hi,

Please helip in improving the performance of the below SELECT Query.This currently uses the LEFT OUTER JOIN, which is time consuming. Please let me know if there are any alternatives for this.

SELECT gjahr p~objnr f~bukrs auart autyp versn

           SUM( wog001 )
           SUM( wog002 )
           SUM( wog003 )
           SUM( wog004 )
           SUM( wog005 )
           SUM( wog006 )
           SUM( wog007 )
           SUM( wog008 )
           SUM( wog009 )
           SUM( wog010 )
           SUM( wog011 )
           SUM( wog012 )
           SUM( wog013 )
           SUM( wog014 )
           SUM( wog015 )
           SUM( wog016 )
     APPENDING TABLE gt_coxx
     FROM  cosp AS p
     LEFT OUTER JOIN    aufk AS f
       ON  p~objnr =  f~objnr
     WHERE lednr   = '00'
     AND   p~objnr IN p_objnr
     AND   gjahr EQ  p_gjahr
     AND   p~versn EQ p_versn
     AND   p~wrttp EQ p_wrttp
     AND   p~vrgng NE 'COIN'
     GROUP BY gjahr p~objnr f~bukrs auart autyp versn.

[matt]

What does p_objnr typically contain.

Have got an ST05 trace on this select? What indexes is it using?

[Former Member]

Hi Matthew,

Thanks for quick response.

p_objnr contains the Object Numbers from OR000000000001 to ORZZZZZZZZZZZZ.

I have also tried by using the index: COSP 1 as shown in the below query, but still there is no improvement in performance.

*    SELECT gjahr p~objnr f~bukrs auart autyp versn "AUFNR WRTTP twaer

*         wog001

*         wog002

*         wog003

*         wog004

*         wog005

*         wog006

*         wog007

*         wog008

*         wog009

*         wog010

*         wog011

*         wog012

*         wog013

*         wog014

*         wog015

*         wog016

*         INTO TABLE gt_coxx

*         FROM  cosp AS p

*         LEFT OUTER JOIN    aufk AS f

*           ON  p~objnr =  f~objnr

*            CLIENT SPECIFIED

*         WHERE p~mandt = sy-mandt

*         AND  lednr   = '00'

*         AND   p~objnr IN p_objnr

*         AND   gjahr EQ  p_gjahr

*         AND   p~wrttp EQ p_wrttp

*         AND   p~versn EQ p_versn

*         AND   p~vrgng NE 'COIN'.

And after fetching the data from COSP table have done some manipulations in order to get the sum of the respective fields, but still there is no improvement in the performance.

Please help me out in this issue.

Thanks in advance.

[matt]

You should have the SUM in the select - taking it out will reduce performance.

Before you can go further you should run the select while a trace is on via ST05, to find out exactly how the database is being accessed. (EXPLAIN SQL)

[former_member946717]

Hi Roopa,

In a select statement, the order of the fields selected = order of the fields in the where condition = order of the fields in the internal table structure = order of fields in the table

Hence you can modify your select as below:

SELECT p~objnr p~gjahr p~versn  f~bukrs f~auart f~autyp

           SUM( wog001 )

           SUM( wog002 )

           SUM( wog003 )

           SUM( wog004 )

           SUM( wog005 )

           SUM( wog006 )

           SUM( wog007 )

           SUM( wog008 )

           SUM( wog009 )

           SUM( wog010 )

           SUM( wog011 )

           SUM( wog012 )

           SUM( wog013 )

           SUM( wog014 )

           SUM( wog015 )

           SUM( wog016 )

     APPENDING TABLE gt_coxx

     FROM  cosp AS p

     LEFT OUTER JOIN    aufk AS f

       ON  p~objnr =  f~objnr

     WHERE lednr   = '00'

     AND   p~objnr IN p_objnr

     AND   gjahr EQ  p_gjahr

     AND   p~versn EQ p_versn

     AND   p~wrttp EQ p_wrttp

     AND   p~vrgng NE 'COIN'

     GROUP BY p~objnr p~gjahr p~versn p~bukrs f~auart f~autyp .

The ideal way would be to use FOR ALL ENTRIES.

First select auart and autyp from AUFK table. Then if you get any entries, then for all entries in AUFK, select these entries. FOr example:

SELECT auart autyp objnr

FROM aufk

INTO table IT_AUFK

WHERE objnr IN p_objnr[].

IF sy-subrc EQ 0.

  SELECT objnr gjahr bukrs

           SUM( wog001 ) 

           SUM( wog002 )

           SUM( wog003 )

           SUM( wog004 )

           SUM( wog005 )

           SUM( wog006 )

           SUM( wog007 )

           SUM( wog008 )

           SUM( wog009 )

           SUM( wog010 )

           SUM( wog011 )

           SUM( wog012 )

           SUM( wog013 )

           SUM( wog014 )

           SUM( wog015 )

           SUM( wog016 )

  FROM cosp

  INTO TABLE IT_COSP

  FOR ALL ENTRIES IN IT_AUFK

  WHERE objnr = it_aufk-objnr

  AND   lednr = '00'

  AND   gjahr EQ  p_gjahr 

  AND   versn EQ p_versn

  AND   wrttp EQ p_wrttp

  AND   vrgng NE 'COIN'.

IF sy-subrc EQ 0.

ENDIF.

ENDIF.

Hope this helps. Let us know if you need any more help

[matt]

Contrary to popular myth FOR ALL ENTRIES is usually worse performing than INNER JOIN. As this is an OUTER JOIN, FAE won't be suitable anyway.