topic Re: Performance Issue while fetching from COSP table in Application Development and Automation Discussions

Performance Issue while fetching from COSP table

Former Member — Wed, 08 May 2013 09:55:26 GMT

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.

Re: Performance Issue while fetching from COSP table

matt — Wed, 08 May 2013 11:22:17 GMT

What does p_objnr typically contain.

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

Re: Performance Issue while fetching from COSP table

Former Member — Wed, 08 May 2013 11:40:41 GMT

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.

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

Re: Performance Issue while fetching from COSP table

Former Member — Wed, 08 May 2013 11:49:02 GMT

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.

Re: Performance Issue while fetching from COSP table

matt — Wed, 08 May 2013 12:12:36 GMT

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.

Re: Performance Issue while fetching from COSP table

matt — Wed, 08 May 2013 12:15:23 GMT

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)