topic Re: Performance Select Query in Application Development and Automation Discussions

Performance Select Query

Former Member — Tue, 08 May 2007 03:20:12 GMT

Hi Experts,

Kindly let me know how can i improve performance of the below query...

SELECT DISTINCT c_{bukrs c}waers c_{prctr b}kostl

a_{kstar a}versn a~gjahr

a_{wkg001 a}wkg002 a_{wkg003 a}wkg004 a_{wkg005 a}wkg006

a_{wkg007 a}wkg008 a_{wkg009 a}wkg010 a_{wkg011 a}wkg012

a_{wkg013 a}wkg014 a_{wkg015 a}wkg016

APPENDING TABLE i_temp

FROM coss AS a

JOIN cssl AS b ON a_{objnr = b}objnr

JOIN csks AS c ON b_{kostl = c}kostl

WHERE ( a~lednr EQ '00'

AND a~versn EQ '000'

AND b~kostl IN r_ccde

AND a~wrttp EQ '04'

AND a~gjahr EQ p_fy

AND a~kstar GE '0000000000'

AND a~kstar LE '0000999999' )

Thanks in Advance.

Santosh

Re: Performance Select Query

Former Member — Tue, 08 May 2007 03:23:29 GMT

Hi,

Try to remove the inner join and use FOR ALL ENTRIES.

Regards,

Priyanka.

Re: Performance Select Query

Former Member — Tue, 08 May 2007 06:42:23 GMT

Hi,

I found your query is perfect, but one exception there u r giving appending clause, instead of this give into table option and then append these entries in your itab.

u try to remove Appending clause. Also, in where clause do not give non primary key field, instead remove these entries from resultset itab.

Jogdand M B

Message was edited by:

Jogdand M B

Re: Performance Select Query

Former Member — Tue, 08 May 2007 13:32:35 GMT

You must add KOKRS to both the JOIN and the WHERE:


SELECT DISTINCT c~bukrs c~waers c~prctr b~kostl
                a~kstar a~versn a~gjahr
                a~wkg001 a~wkg002 a~wkg003 a~wkg004 a~wkg005 a~wkg006
                a~wkg007 a~wkg008 a~wkg009 a~wkg010 a~wkg011 a~wkg012
                a~wkg013 a~wkg014 a~wkg015 a~wkg016
  APPENDING TABLE i_temp
  FROM coss AS a
  JOIN cssl AS b ON a~objnr = b~objnr
  JOIN csks AS c ON b~kokrs = c~kokrs AND
                    b~kostl = c~kostl
  WHERE ( a~lednr EQ '00'
    AND a~versn EQ '000'
    AND c~kokrs EQ p_kokrs
    AND b~kostl IN r_ccde
    AND a~wrttp EQ '04'
    AND a~gjahr EQ p_fy
    AND a~kstar GE '0000000000'
    AND a~kstar LE '0000999999' ).

Rob

Re: Performance Select Query

Former Member — Wed, 09 May 2007 04:57:30 GMT

Hi santosh,

If u use the inner joins the tables will be get linking in the database server..so that only ur performance is slower..For that u have to make the processes in the application server..U need to use the FOR ALL ENTRIES command..

That is give the respective fields in one internal table and by using for all entries we can inter link that with another internal table.. Always split the queries and then perform the actions..

Reward if it's helpful...

Re: Performance Select Query

Former Member — Wed, 09 May 2007 13:47:44 GMT

Hi Santosh,

Following instructions can be followed while writing SELECT query in ABAP for better performance.

1. Do not use APPENDING clause. Instead have an internal table exactly of the same size as of selection fields and should be there only for select query. So try using INTO TABLE <itab> clause.

2. Select the field in the same sequence in which they are present in the tables in database.

3. Also give the WHERE clause fields in the same sequence as of the database tables.

4. Try to give as many as key fields in the WHERE Clause.

5. Avoid using DISTINCT clause in SELECT query. Instead, have all the entries in an internal table. After SELECT query, sort the table and delete the duplicate entries.

Now for your query, following will result in better performance.

DATA RT_KSTART TYPE RANGE OF KSTAR.

RT_KSTAR-LOW = '0000000000'.

RT_KSTAR-HIGH = '0000999999'.

RT_KSTAR-OPTION = 'BT'.

RT_KSTAR-SIGN = 'I'.

APPEND RT_KSTAR.

SELECT a_{gjahr a}versn a_{kstar a}wkg001 a_{wkg002 a}wkg003 a_{wkg004 a}wkg005 a~wkg006

a_{wkg007 a}wkg008 a_{wkg009 a}wkg010 a_{wkg011 a}wkg012 a_{wkg013 a}wkg014 a_{wkg015 a}wkg016

b~kostl

c_{bukrs c}waers c~prctr

INTO TABLE i_temp

FROM coss AS a

INNER JOIN cssl AS b ON b_{objnr = a}objnr

INNER JOIN csks AS c ON c_{kostl = b}kostl

WHERE a~lednr EQ '00' AND

a~gjahr EQ p_fy AND

a~wrttp EQ '04' AND

a~versn EQ '000' AND

a~kstar IN RT_KSTAR AND

b~kostl IN r_ccde.

Hope this sort out your issue.

PS If the answer solves your query, plz close the thread by rewarding each reply.

Regards

Re: Performance Select Query

Former Member — Wed, 09 May 2007 14:12:50 GMT

Hi Santosh,

There is so much wrong with your query that I do not know where to begin.

1) Never use DISTINCT in your select because it bypasses the SAP buffer and forces a sort at the database level. Using this in conjunction to 2 joins is even worse.

2) In my personal openion joins are not the way to go about your queries. Very often they are slow. I would recommend that you make separate selects on each table and use FOR ALL ENTRIES.

3) There is no index (provided by SAP) on field OBJNR in table CSSL. Check your system and see if somebody has already created a Z index on this field. This is a biggy because you are doing a sequential search on this table for all the entries in found in table COSS. The performance of this query will be greatly improved if you can create a Z index on this table for fields OBJNR and GJAHR. However you need to remember that indexes will only increase your database size. You will have to choose between the performance and database size.

4) I also noticed that you are asking the user to only enter the year in the selection screen. I assume 'P_FY' is a parameter. You would help the performance of this query by asking the user to enter more information on the screen. Getting information like KOKRS, KOSTL and LSTAR if possible would be invaluable in constructing a more efficient query.

5) Use the KOKRS you get from table CSSL to get data from table CSKS.

Re: Performance Select Query

harishaginati — Thu, 10 May 2007 13:41:02 GMT

buddy...,

select distint is not good in effeciency. so best thing you can do is write normal SELECT and then sort and delete adjacent duplicates..,the reason why i am telling this is..., this entair process of comparisons will be done in database..., which will decrease performance of database..., sacrifying application server if better to sacrify data base server...,

reward me if useful...,

cheers,

Harish

Message was edited by:

Harish AGINATI

Message was edited by:

Harish AGINATI

Re: Performance Select Query

Former Member — Thu, 31 May 2007 05:48:11 GMT

The issue still persists, some more light on this would help me.

Cheers..

Santosh

Re: Performance Select Query

Former Member — Thu, 31 May 2007 05:54:42 GMT

Remove the inner join and try create views and make the select query from the same..